Count multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to get a count based on three columns of data. In column A I have a
department code, in column B I have plan IDs, and in column C I have a flag
that indicates if this is a financial adjustment. I want to count all the
plan IDs by department code where it is NOT a financial adjustment(or
conversely, where it says "Current").

A B C
004 Plan1 Adj
004 Plan1 Current

After reading many of the posts on this forum, I've been trying to get
SUMPRODUCT to work. This is what I've written so far:

=SUMPRODUCT(--(A$13:A$15000="004"),--(B$13:B$15000="PLAN1"),--(C$13:C$15000="Current"))

I should get a count of 10, but it returns 0 not matter how I tweak it.
What am I doing wrong?
 
=SUMPRODUCT(--(A$13:A$15000="004"),--(B$13:B$15000="PLAN1"),--(C$13:C$15000="Current"))

It's probably due to data inconsistencies if the above is not returning
properly

Try this more robust version of the above, which should cover all
possibilities:
=SUMPRODUCT(--(TEXT(A$13:A$15000,"000")="004"),--(TRIM(B$13:B$15000)="PLAN1"),--(TRIM(C$13:C$15000)="Current"))
 
The first thing I'd check is your dept numbers. Excel doesn't like numbers
with leading 0s.

Are the dept numbers TEXT entries or are they numbers using a custom format
so as to display the leading 0s?
=SUMPRODUCT(--(A$13:A$15000="004")
Click to expand...

The way you have the formula written it's looking at the dept numbers as
TEXT entries. Since it doesn't seem to recognize "004" perhaps they're
custom formatted numbers. Are all dept numbers 3 digits?

Try one of these:

=SUMPRODUCT(--(A$13:A$15000=4),--(B$13:B$15000="PLAN1"),--(C$13:C$15000="Current"))

=SUMPRODUCT(--(TEXT(A$13:A$15000,"000")="004"),--(B$13:B$15000="PLAN1"),--(C$13:C$15000="Current"))
 
Thanks to both Max and T. Valko. You're information helped. You were
correct that the company codes were not formatted as text. Also, it turned
out that some of my plan ids also had trailing spaces, so once I cleaned the
data up, the formulas worked perfectly.
 
Back
Top