Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Double Count
I have the following data (sample):
w 1 w 1 w 2 w 1 w 1 c 3 w 2 c 1 c 1 w 3 c 1 I need a formula that calculates how many of the w's are 1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Double Count
=sumproduct(--(a1:a20="W"),--(b1:b20=1))
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 Byron720 wrote: I have the following data (sample): w 1 w 1 w 2 w 1 w 1 c 3 w 2 c 1 c 1 w 3 c 1 I need a formula that calculates how many of the w's are 1 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Double Count
Thanks, It worked !!!
"Dave Peterson" wrote: =sumproduct(--(a1:a20="W"),--(b1:b20=1)) 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 Byron720 wrote: I have the following data (sample): w 1 w 1 w 2 w 1 w 1 c 3 w 2 c 1 c 1 w 3 c 1 I need a formula that calculates how many of the w's are 1 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add excel horizontal & vertical ruler | Excel Worksheet Functions | |||
How to count occurrences where 2 columns are required to be true? | Excel Worksheet Functions | |||
Excel CSV file: How to preserve double quotation mark on Unix ftp? | Excel Discussion (Misc queries) | |||
retirning count values from calcualted fields | Excel Discussion (Misc queries) | |||
Double clicking in a Pivot Table, Please HELP | Excel Discussion (Misc queries) |