SUMPRODUCT confusion

[JKwan]

I've found that SUMPRODUCT to be very useful and powerful, however, due to my recent discovery of this function.... things that I think should work, however, it does not work. Hoping people can point me in the right direction.

I have a list of items that I would like to count, but giving me a headache. So, If my list is say A, B, C, D, E in a column. Now, I would like to count all As and Bs. The formula would be:

=SUMPRODUCT(--($B$2:$B$544={"A","B"}))

The interesting part is that the above formula works by using the array. Where my problem is that I would like to exclude items from from list that I wanted to count. If I do this formula, Sumproduct fails, which I need help.

=SUMPRODUCT(--($B$2:$B$544<>{"A","B"}))

To me, it is the same logic, obviously, I am wrong

However, if I break it apart, into components, then it works.

=SUMPRODUCT(--($B$2:$B$544<>"A")*($B$2:$B$544<>"B"))


So, what I am missing? Why is it that if I do the "=", the count is correct, but if I do the "<>", it fails to work?

Thanks in advance.

[Bob Phillips]

The SP format you are using is ORing the conditions. SP is effectively looking at each cell and checking if it meets either of the conditions. As it is checking if OR not, every cell meets one or other of the conditions (or maybe even both).

Let's say there are 4 As and 11 Bs in the range $B$2:$B$544.

So it looks at $B$2:$B$544 and checks if <>"A", 539 are not A here. Then it checks the same range for <>"B", of which 532 are not B. S the result is 539+532, 1071. So, every cell that is not A or B gets counted twice, every one that is A or B gets counted once.

The formula that does work is ANDing the conditions, but it does not need both -- and *, one style or the other is sufficient.

[lynnnow]

XLD, can SP be made to look for text with wildcards? I tried using wildcards, but couldn't get the right results. For e.g. I'm validating "A" and "A/B" or "B/A", but SP doesn't allow the use of "*A*". Any help here?

[Bob Phillips]

Not in that way, but it can be done like this

=SUMPRODUCT(--(LEFT(B2:B20)="A"),--(MONTH(C2:C20)=9),A2:A20)

Of course, if you don't have any of the conditions that require the use of SUMPRODUCT, then SUMIF and SUMIFS both support wildcards.

[lynnnow]

XLD, my SP is something like this:

=IF(A14<>"",SUMPRODUCT(--(AF_Col_Rng=A14),--(AF_Rec_Rng="REC"),--(ISBLANK(AF_TDone_Rng)),--(ISBLANK(AF_PDone_Rng)),AF_Sum_Rng),0)

The "AF" ranges are derived from the AutoFilter set in Sheet1 (which change on a daily basis) but basically these are:

AF_Col_Rng = The person's name I'm looking for.
AF_Rec_Rng = Checking if it has "REC" in the cell
ISBLANK(AF_TDone_Rng) and ISBLANK(AF_PDone_Rng = self-explanatory
AF_Sum_Rng = The actual range being summed.

and these "REC" files can sometimes be shared between users...

Although my quest for getting a formula like this to work has been discontinued, I'd like to know if it was possible.

Also, SUMIF is limited to just a single condition, and 2003 does not support SUMIFS, so my formula out of elimination is SP.

[Bob Phillips]

XLD, my SP is something like this:

=IF(A14<>"",SUMPRODUCT(--(AF_Col_Rng=A14),--(AF_Rec_Rng="REC"),--(ISBLANK(AF_TDone_Rng)),--(ISBLANK(AF_PDone_Rng)),AF_Sum_Rng),0)

The "AF" ranges are derived from the AutoFilter set in Sheet1 (which change on a daily basis) but basically these are:

AF_Col_Rng = The person's name I'm looking for.
AF_Rec_Rng = Checking if it has "REC" in the cell
ISBLANK(AF_TDone_Rng) and ISBLANK(AF_PDone_Rng = self-explanatory
AF_Sum_Rng = The actual range being summed.

and these "REC" files can sometimes be shared between users...

Although my quest for getting a formula like this to work has been discontinued, I'd like to know if it was possible.

Of course it can be done, Excel can do anything :)

The key to contains is using the FIND function. This looks at a cell to see whether it contains a string and returns its position in the cell, or #N/A. In SP, we can use a range like so


FIND("REC",AF_Rec_Rng)

This won't count the RECs, and any cell doesn't have RED it will blow up our formula, so we do another test to see whether FIND gets a numeric position or #N/A


ISUMBER(FIND("REC",AF_Rec_Rng))

Put it all together, and we have


=IF(A14<>"",SUMPRODUCT(--(AF_Col_Rng=A14),--(ISNUMBER(FIND("REC",AF_Rec_Rng))),--(ISBLANK(AF_TDone_Rng)),--(ISBLANK(AF_PDone_Rng)),AF_Sum_Rng),0)


Also, SUMIF is limited to just a single condition, and 2003 does not support SUMIFS, so my formula out of elimination is SP.

Aah, still on 2003! Time to upgrade mate. I would never have suggested anyone upgrades to Excel 2007, it was truly bad, but 2010 is a good version.

[lynnnow]

That worked... Thank you... :bow: :beerchug:

Also, we will be migrating to 2010, hopefully sooner than I expect... :)