Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting values in one cell based on values in another
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting values in one cell based on values in another
=SUMPRODUCT(--(A1:A50=1),--(B1:B50="X"))
will give you what you what you want. Change 50 to the last row of your data... You can also try in E1 =SUMPRODUCT(--(A1:A50=C1),--(B1:B50=D1)) with 1 in C1, and X in D1 "S2" wrote: 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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting values in one cell based on values in another
=SUMPRODUCT(--(A2:A5&B2:B5="1X"))
"S2" wrote: 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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting values in one cell based on values in another
Thanks Sheeloo!
"Sheeloo" wrote: =SUMPRODUCT(--(A1:A50=1),--(B1:B50="X")) will give you what you what you want. Change 50 to the last row of your data... You can also try in E1 =SUMPRODUCT(--(A1:A50=C1),--(B1:B50=D1)) with 1 in C1, and X in D1 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting values in one cell based on values in another
Beautiful...& simple too! Thanks!
Quick q - what do the "--" mean/do? "Teethless mama" wrote: =SUMPRODUCT(--(A2:A5&B2:B5="1X")) "S2" wrote: 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) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting values in one cell based on values in another
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 he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html S2 wrote: Beautiful...& simple too! Thanks! Quick q - what do the "--" mean/do? "Teethless mama" wrote: =SUMPRODUCT(--(A2:A5&B2:B5="1X")) "S2" wrote: 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) -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting values in one cell based on values in another
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 "S2" wrote: Beautiful...& simple too! Thanks! Quick q - what do the "--" mean/do? "Teethless mama" wrote: =SUMPRODUCT(--(A2:A5&B2:B5="1X")) "S2" wrote: 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) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting values in one cell based on values in another
You're welcome!
"S2" wrote: Beautiful...& simple too! Thanks! Quick q - what do the "--" mean/do? "Teethless mama" wrote: =SUMPRODUCT(--(A2:A5&B2:B5="1X")) "S2" wrote: 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy values from a cell based on values of another cell | Excel Discussion (Misc queries) | |||
Counting values based on multiple conditions | Excel Worksheet Functions | |||
How to assign values to a cell based on values in another cell? | Excel Worksheet Functions | |||
Counting based on values in two separate columns | Excel Worksheet Functions | |||
Counting based on values in two columns? | Excel Worksheet Functions |