SumProduct Help

[Annie]

Please help me with the sumproduct formula. I'm using this formula to tell me
how many attendees, by department, attended a class at a particular time, and
this WORKS:
=sumproduct(--(D364=N1),--(F3:F64=N4),--(G3:G64=M1)

Now, I'm being asked to add the location, so I need to know how many
attendees, by department, attended a class in San Jose and how many attended
class in San Francisco, so I did added the location to the end:

=sumproduct(--(D364=N1),--(F3:F64=N4),--(G3:G64=M1),(--(J3:J64=O1)
and it's NOT working, I even typed in the location as (J3:j64="San Jose")
and it still doesn't work. The formula worked for one department but not the
others, for some reason it will not count the other departments -- it gives
me 0 instead of the actual count. I checked to make sure the correct cells
were typed in for each department and they are

Can anyone tell me what I'm doing wrong?

 

[Per Jessen]

Hi Annie

Nothing seems to be wrong with your formula, which is also confirmed as it
works for one department.

I think you have to look for leading or trailing spaces in the location
column (and maybe in the others too).

Hopes this helps.
....
Per

 

[Annie]

Hi Jessen,
It's not working. When I remove the location it works on the other
departments, for some reason it doesn't like the location. Is there
alternate formula I can use?

 

[JoeU2004]

Can anyone tell me what I'm doing wrong?

To begin with, you are not copy-and-pasting the formulas exactly as they
appear in the worksheet (i.e. the Formula Bar).

What you have typed in your posting has obvious typos. Perhaps even your
actual formula has obvious typos. If you want to help us help you, you need
to show us the actual formula.

Second, assuming your formula is entered correctly as implied by what you
wrote here, we might need to see exactly what is in O1 and at least some of
J3:J64 that you believe should match.

That is more difficult since copy-and-pasting text into a web page for
posting does not always faithfully reproduce the text exactly. So some
additional information might give credibility to the posted text. For
example, =LEN(O1) and =LEN(x), where "x" are some matching J3:J64.

Finally, we need some evidence that there are indeed some rows where all of
the conditions are met.

Provide an example, say row 32, where
SUMPRODUCT(--(D32=N1),-(F32=N4),--(G32=M1)) is 1, but
SUMPRODUCT(--(D32=N1),-(F32=N4),--(G32=M1),--(J32=O1)) is zero, but should
also be 1. Provide LEN(J32) and LEN(O1), and copy-and-paste their cell
contents from the Formula Bar.


----- original message -----

 

[Annie]

JoeU2004 & Per Jessen,

THANK YOU VERY MUCH!! I had to step away and come back to it. You guys
were right. My formula is correct. While double checking my numbers, I
counted incorrectly. Sorry for the bother. I spent to many hours looking at
the data in one setting. I appreciate your help!!