Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting rows that meet conditions in multiple columns
I want to count how many people who attended an event hold a certain status.
I've tried a number of formulas, including: =SUMPRODUCT((C2:C493="x")*(A2:A493="renewed")) and =SUMPRODUCT(--(C2:C493="x"),--(A2:A493="renewed")) (the "x" marks whether someone attended the event) =SUMPRODUCT(IF(ISNUMBER(SEARCH("Asia Trail", B1:B493)), IF(A2:A493="renewed",1))) where "Asia Trail" is one of several text values in a cell and I continue to get incorrect numbers. In other similar cells, I get zero when just looking at the sheet I know the number has to be in the 100's. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting rows that meet conditions in multiple columns
Odd, your second formula works just fine for me, which would make me think
that may be something is going on with the inputs. Are there extra spaces in the "x" or in the "renewed"? -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "belleinhell" wrote: I want to count how many people who attended an event hold a certain status. I've tried a number of formulas, including: =SUMPRODUCT((C2:C493="x")*(A2:A493="renewed")) and =SUMPRODUCT(--(C2:C493="x"),--(A2:A493="renewed")) (the "x" marks whether someone attended the event) =SUMPRODUCT(IF(ISNUMBER(SEARCH("Asia Trail", B1:B493)), IF(A2:A493="renewed",1))) where "Asia Trail" is one of several text values in a cell and I continue to get incorrect numbers. In other similar cells, I get zero when just looking at the sheet I know the number has to be in the 100's. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting rows that meet conditions in multiple columns
The SUMPRODUCT with the IF formula won't work, you would use
=SUMPRODUCT(--(ISNUMBER(SEARCH("Asia Trail", B2:B493))), --(A2:A493="renewed")) also the ranges need to be of equal size you are using B1:B493 and A2:A493 The other formulas should work so you must have leading trailing spaces/hidden characters in your data -- Regards, Peo Sjoblom "belleinhell" wrote in message ... I want to count how many people who attended an event hold a certain status. I've tried a number of formulas, including: =SUMPRODUCT((C2:C493="x")*(A2:A493="renewed")) and =SUMPRODUCT(--(C2:C493="x"),--(A2:A493="renewed")) (the "x" marks whether someone attended the event) =SUMPRODUCT(IF(ISNUMBER(SEARCH("Asia Trail", B1:B493)), IF(A2:A493="renewed",1))) where "Asia Trail" is one of several text values in a cell and I continue to get incorrect numbers. In other similar cells, I get zero when just looking at the sheet I know the number has to be in the 100's. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting rows that meet conditions in multiple columns
I tried your formula and still no success. I also tried:
=SUMPRODUCT((ISNUMBER(SEARCH("Asia Trail", B2:B493)))*(A2:A493="renewed")) =SUMPRODUCT(--(ISNUMBER(SEARCH("Asia Trail",B2:B493))),--(ISNUMBER(SEARCH(A2:A493,"renewed")))) =SUMPRODUCT((ISNUMBER(SEARCH("Asia",B2:B493)))*(IS NUMBER(SEARCH(A2:A493,"renewed")))) and none of those worked either. I'm going to try redoing the worksheet and see if that does anything. "Peo Sjoblom" wrote: The SUMPRODUCT with the IF formula won't work, you would use =SUMPRODUCT(--(ISNUMBER(SEARCH("Asia Trail", B2:B493))), --(A2:A493="renewed")) also the ranges need to be of equal size you are using B1:B493 and A2:A493 The other formulas should work so you must have leading trailing spaces/hidden characters in your data -- Regards, Peo Sjoblom "belleinhell" wrote in message ... I want to count how many people who attended an event hold a certain status. I've tried a number of formulas, including: =SUMPRODUCT((C2:C493="x")*(A2:A493="renewed")) and =SUMPRODUCT(--(C2:C493="x"),--(A2:A493="renewed")) (the "x" marks whether someone attended the event) =SUMPRODUCT(IF(ISNUMBER(SEARCH("Asia Trail", B1:B493)), IF(A2:A493="renewed",1))) where "Asia Trail" is one of several text values in a cell and I continue to get incorrect numbers. In other similar cells, I get zero when just looking at the sheet I know the number has to be in the 100's. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting characters in multiple rows when rows meet specific criteria | Excel Worksheet Functions | |||
Counting # of cells with that meet criteria in two columns | Excel Worksheet Functions | |||
count pieces of records meet conditions in different columns | Excel Worksheet Functions | |||
Hiding rows that meet multiple conditions in excel | Excel Discussion (Misc queries) | |||
counting cells in a data range that meet 3 specific conditions | Excel Discussion (Misc queries) |