Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count with criteria
Hi,
I have a column B which can have input as 1, 0,a or sh. Another Column C can have input as I,D,X,M. I want to count the number of "I"s in column C which have "1" in column B. In other words if column B has o ans column c has I , I do not want to count it. Column B Column C 1 I 0 I sh D Thanks for your help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count with criteria
A1: 1
A2: I =SUMPRODUCT(--($B$2:$B$100=A1),--($C$2:C$100=A2)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "NM" wrote: Hi, I have a column B which can have input as 1, 0,a or sh. Another Column C can have input as I,D,X,M. I want to count the number of "I"s in column C which have "1" in column B. In other words if column B has o ans column c has I , I do not want to count it. Column B Column C 1 I 0 I sh D Thanks for your help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count with criteria
Try this in A1 (or anywhere other than B1:C100)
=SUMPRODUCT(--(B1:B100=1),--(C1:C100="I")) Adjust 100 to the last row in your data set. "NM" wrote: Hi, I have a column B which can have input as 1, 0,a or sh. Another Column C can have input as I,D,X,M. I want to count the number of "I"s in column C which have "1" in column B. In other words if column B has o ans column c has I , I do not want to count it. Column B Column C 1 I 0 I sh D Thanks for your help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count with criteria
Try this
=SUMPRODUCT((B1:B30=1)*(C1:C30="I")) Mike "NM" wrote: Hi, I have a column B which can have input as 1, 0,a or sh. Another Column C can have input as I,D,X,M. I want to count the number of "I"s in column C which have "1" in column B. In other words if column B has o ans column c has I , I do not want to count it. Column B Column C 1 I 0 I sh D Thanks for your help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count with criteria
Try this:
=SUMIF(C:C,"I",B:B) Hope this helps. Pete On Nov 12, 4:21*pm, NM wrote: Hi, I have a column B which can have input as 1, 0,a or sh. Another Column C can have input as I,D,X,M. I want to count the number of "I"s in column C which have "1" in column B. In other words if column B has o ans column c has I , I do not want to count it. Column B * * Column C 1 * * * * * * * * I * * 0 * * * * * * * * I * sh * * * * * * * D Thanks for your help. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count with criteria
Thanks much! It works!
"Sheeloo" wrote: Try this in A1 (or anywhere other than B1:C100) =SUMPRODUCT(--(B1:B100=1),--(C1:C100="I")) Adjust 100 to the last row in your data set. "NM" wrote: Hi, I have a column B which can have input as 1, 0,a or sh. Another Column C can have input as I,D,X,M. I want to count the number of "I"s in column C which have "1" in column B. In other words if column B has o ans column c has I , I do not want to count it. Column B Column C 1 I 0 I sh D Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
count using criteria | Excel Discussion (Misc queries) | |||
How to count nos. on 3 criteria | Excel Worksheet Functions | |||
Count If 3 Criteria Met | Excel Worksheet Functions | |||
Sum Count of Criteria Every 3rd Row | Excel Worksheet Functions |