Complex or Conditional Countif(s)

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.



"Booweezie" wrote:

> 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

pdberger - You're a peach!
That's exactly what I needed and it works perfectly!
Check's in the mail!

~~Booweezie

=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


"Booweezie" <Booweezie@discussions.microsoft.com> wrote in message
news:08C80D9C-2A0C-4147-B929-7F6AE5662F6B@microsoft.com...
> 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