Complex or Conditional Countif(s)

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

Guest

I'm needing to count stuff in a column based on a condition in another
column. Here's my example:

Reasons
Plant A B C D E

102 X
999 X
102 X
102 X

I want to set up a matrix where I count each reason by Plant.........so.....
For Reason #A - My count for 102 would be 2 and for 999 would be 1
For Reason #C - My count for 102 would be 1 and for 999 would be 0

Does anyone know of a formula to use; I'm stumped.

Thanks.

Booweezie
 
Boo -- Here's one approach. Tried it -- seems to work
A B C D E
1 Reasons
2 A B C
3 102 X
4 999 X
5 102 X
6 102 X
7
8 102 Formula here
9 999

In cell C8, put the following formula:
=SUMPRODUCT(--($C$3:$C$6="X"),(--($A$3:$A$6=A8)))

You can copy it down to C9.

There's lots more powerful tools -- Filtering, PivotTables, etc. But this
is an easy way.
 
=SUMPRODUCT(--(A2:A50=102),--(B2:B50<>""))

where A2:A50 is the plant range and B2:B50 reason A range

if you are using X you can change it to

=SUMPRODUCT(--(A2:A50=102),--(B2:B50="X"))

replace 102 with

=SUMPRODUCT(--(A2:A50=H1),--(B2:B50<>""))

where you put the plant number in H1



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
pdberger - You're a peach!
That's exactly what I needed and it works perfectly!
Check's in the mail!

~~Booweezie
 
Back
Top