Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a count
A B
1 Ford June 2 Ford May 3 BMW June 4 Ford June 5 Ford June I need to COUNT how many "Fords" have the value of June - the answer should be 3 in this sample. I tried sumif, countif, sumproduct, maybe I need a combination? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a count
You can use a support column C with this formula: =IF(AND(A1="Ford",B1="June"),1,0) Apply this all the rows and get the total of column C. -- renegan ------------------------------------------------------------------------ renegan's Profile: http://www.excelforum.com/member.php...o&userid=10450 View this thread: http://www.excelforum.com/showthread...hreadid=535124 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a count
Hi!
Try this: =SUMPRODUCT(--(A1:A5="Ford"),--(B1:B5="June")) Biff "serve" wrote in message ... A B 1 Ford June 2 Ford May 3 BMW June 4 Ford June 5 Ford June I need to COUNT how many "Fords" have the value of June - the answer should be 3 in this sample. I tried sumif, countif, sumproduct, maybe I need a combination? Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a count
sum product should work
= sumproduct(--(A1:A5="Ford"),--(B1:B5="June")) if there is a chance there are leading or trailing spaces try = sumproduct(--(trim(A1:A5)="Ford"),--(trim(B1:B5)="June")) "serve" wrote: A B 1 Ford June 2 Ford May 3 BMW June 4 Ford June 5 Ford June I need to COUNT how many "Fords" have the value of June - the answer should be 3 in this sample. I tried sumif, countif, sumproduct, maybe I need a combination? Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a count
Hi,
You may also want to try using an array formula although sumproduct is definitely a better solution =sum(if(($A$1:$A$5="Ford")*($B$1:$B$5="June"),1,0) ) confirmed by a Ctrl+Shift+Enter Regards, Ashish Mathur "serve" wrote: A B 1 Ford June 2 Ford May 3 BMW June 4 Ford June 5 Ford June I need to COUNT how many "Fords" have the value of June - the answer should be 3 in this sample. I tried sumif, countif, sumproduct, maybe I need a combination? Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a count
=sum(if(($A$1:$A$5="Ford")*($B$1:$B$5="June"),1,0) )
confirmed by a Ctrl+Shift+Enter No need for the IF function: =SUM(($A$1:$A$5="Ford")*($B$1:$B$5="June")) Biff "Ashish Mathur" wrote in message ... Hi, You may also want to try using an array formula although sumproduct is definitely a better solution =sum(if(($A$1:$A$5="Ford")*($B$1:$B$5="June"),1,0) ) confirmed by a Ctrl+Shift+Enter Regards, Ashish Mathur "serve" wrote: A B 1 Ford June 2 Ford May 3 BMW June 4 Ford June 5 Ford June I need to COUNT how many "Fords" have the value of June - the answer should be 3 in this sample. I tried sumif, countif, sumproduct, maybe I need a combination? Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a count
Thank you everybody. They all work.
"Biff" wrote: =sum(if(($A$1:$A$5="Ford")*($B$1:$B$5="June"),1,0) ) confirmed by a Ctrl+Shift+Enter No need for the IF function: =SUM(($A$1:$A$5="Ford")*($B$1:$B$5="June")) Biff "Ashish Mathur" wrote in message ... Hi, You may also want to try using an array formula although sumproduct is definitely a better solution =sum(if(($A$1:$A$5="Ford")*($B$1:$B$5="June"),1,0) ) confirmed by a Ctrl+Shift+Enter Regards, Ashish Mathur "serve" wrote: A B 1 Ford June 2 Ford May 3 BMW June 4 Ford June 5 Ford June I need to COUNT how many "Fords" have the value of June - the answer should be 3 in this sample. I tried sumif, countif, sumproduct, maybe I need a combination? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |