Counting values in one cell based on values in another

  • Thread starter Thread starter S2
  • Start date Start date
S

S2

Greetings all -
I have the following data:
A B
1 Pri State
2 1 X
3 2
4 1
5 3 X

For each value (Priority) in ColA, I need to know how many times "X" occurs
in State (ColB)
 
Be careful with this formula.

If you have 1X in A2 and B2 is empty or A2 is empty and B2 contains 1X, you may
not get the count you want.



Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
 
Comparisons return True or False... when you put two minus signs in front
they are converted into 1 or 0... which then sumproduct adds up after
multiplying the paired values... hence the name SumProduct
 
Back
Top