Counting values in one cell based on values in another

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)
 
D

Dave Peterson

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
 
S

Sheeloo

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding the right style field in VBA 0
Automatically sending multiple parts of text from one word doc to another - is possible. 0
Auto populate value from one task to another in MS PROJECT 0
Unique Records or Transpose Help 2
Summing cell values based on the value of another 1
How can count the number of cells that fulfill certain criteria in other cells 2
Checking Column Status on closing excel - Macro Needed 2
Finding the proper style field and the numbered paragraph value within the style 0

Top