| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count the number of cells that meet multiple criteria?
I want to count cells from 3 worksheets that meet multiple criteria.
I've tried with a result of 0 (it should be 3): =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)*(Mark!F9:F258=Mark!Y10)*(Craig!X9:X258=In fo!B15)*(Craig!F9:F258=Craig!Y9)*(Craig!F9:F258=Cr aig!Y10)*(Dan!X9:X258=Info!B15)*(Dan!F9:F258=Dan!Y 9)*(Dan!F9:F258=Dan!Y10) I've also tried with a result of 0: =SUMPRODUCT(--(Mark!X9:X258=Info!B15),--(Mark!F9:F258=Mark!Y9),--(Mark!F9:F258=Mark!Y10),--(Craig!X9:X258=Info!B15),--(Craig!F9:F258=Craig!Y9),--(Craig!F9:F258=Craig!Y10),--(Dan!X9:X258=Info!B15),--(Dan!F9:F258=Dan!Y9),--(Dan!F9:F258=Dan!Y10) Please help! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I count the number of cells that meet multiple criteria?
Hi,
First you are missing a parenthesis at the end. Second this would only return a value if All conditions were true on the same row at the same time, is that what you are checking? So for example if Mark!X9:X258=Info!B15 is true for row 9 but Mark!F9:F258=Mark!Y9 is true for row 10 then 0 will be your answer. If this helps, please click the Yes button Cheers, Shane DEvenshire "Dianna_P" wrote: I want to count cells from 3 worksheets that meet multiple criteria. I've tried with a result of 0 (it should be 3): =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)*(Mark!F9:F258=Mark!Y10)*(Craig!X9:X258=In fo!B15)*(Craig!F9:F258=Craig!Y9)*(Craig!F9:F258=Cr aig!Y10)*(Dan!X9:X258=Info!B15)*(Dan!F9:F258=Dan!Y 9)*(Dan!F9:F258=Dan!Y10) I've also tried with a result of 0: =SUMPRODUCT(--(Mark!X9:X258=Info!B15),--(Mark!F9:F258=Mark!Y9),--(Mark!F9:F258=Mark!Y10),--(Craig!X9:X258=Info!B15),--(Craig!F9:F258=Craig!Y9),--(Craig!F9:F258=Craig!Y10),--(Dan!X9:X258=Info!B15),--(Dan!F9:F258=Dan!Y9),--(Dan!F9:F258=Dan!Y10) Please help! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I count the number of cells that meet multiple criteria?
The syntax looks find, so the problem must be with the data.
I would test each part separately to find where the error is. for example does this =SUMPRODUCT((Mark!X9:X258=Info!B15)*1) give you the correct answer? Next does this =SUMPRODUCT((Mark!F9:F258=Mark!Y9)*1) and this =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)) Are you working with number, dates or text? and so on -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Dianna_P" wrote in message ... I want to count cells from 3 worksheets that meet multiple criteria. I've tried with a result of 0 (it should be 3): =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)*(Mark!F9:F258=Mark!Y10)*(Craig!X9:X258=In fo!B15)*(Craig!F9:F258=Craig!Y9)*(Craig!F9:F258=Cr aig!Y10)*(Dan!X9:X258=Info!B15)*(Dan!F9:F258=Dan!Y 9)*(Dan!F9:F258=Dan!Y10) I've also tried with a result of 0: =SUMPRODUCT(--(Mark!X9:X258=Info!B15),--(Mark!F9:F258=Mark!Y9),--(Mark!F9:F258=Mark!Y10),--(Craig!X9:X258=Info!B15),--(Craig!F9:F258=Craig!Y9),--(Craig!F9:F258=Craig!Y10),--(Dan!X9:X258=Info!B15),--(Dan!F9:F258=Dan!Y9),--(Dan!F9:F258=Dan!Y10) Please help! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I count the number of cells that meet multiple criteria?
Hi,
One other thing you might consider - if the entries are numeric or dates are some of them text and other numeric? The data types must be the same in your formula. Cheers, Shane Devenshire "Dianna_P" wrote: I want to count cells from 3 worksheets that meet multiple criteria. I've tried with a result of 0 (it should be 3): =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)*(Mark!F9:F258=Mark!Y10)*(Craig!X9:X258=In fo!B15)*(Craig!F9:F258=Craig!Y9)*(Craig!F9:F258=Cr aig!Y10)*(Dan!X9:X258=Info!B15)*(Dan!F9:F258=Dan!Y 9)*(Dan!F9:F258=Dan!Y10) I've also tried with a result of 0: =SUMPRODUCT(--(Mark!X9:X258=Info!B15),--(Mark!F9:F258=Mark!Y9),--(Mark!F9:F258=Mark!Y10),--(Craig!X9:X258=Info!B15),--(Craig!F9:F258=Craig!Y9),--(Craig!F9:F258=Craig!Y10),--(Dan!X9:X258=Info!B15),--(Dan!F9:F258=Dan!Y9),--(Dan!F9:F258=Dan!Y10) Please help! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I count the number of cells that meet multiple criteria?
Are you sure that it shouldn't be separate functions
=SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)*(Mark!F9:F258=Mark!Y10)) +SUMPRODUCT(Craig!X9:X258=Info!B15)*(Craig!F9:F258 =Craig!Y9)*(Craig!F9:F258=Craig!Y10)) +SUMPRODUCT(Dan!X9:X258=Info!B15)*(Dan!F9:F258=Dan !Y9)*(Dan!F9:F258=Dan!Y10)) -- __________________________________ HTH Bob "Dianna_P" wrote in message ... I want to count cells from 3 worksheets that meet multiple criteria. I've tried with a result of 0 (it should be 3): =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)*(Mark!F9:F258=Mark!Y10)*(Craig!X9:X258=In fo!B15)*(Craig!F9:F258=Craig!Y9)*(Craig!F9:F258=Cr aig!Y10)*(Dan!X9:X258=Info!B15)*(Dan!F9:F258=Dan!Y 9)*(Dan!F9:F258=Dan!Y10) I've also tried with a result of 0: =SUMPRODUCT(--(Mark!X9:X258=Info!B15),--(Mark!F9:F258=Mark!Y9),--(Mark!F9:F258=Mark!Y10),--(Craig!X9:X258=Info!B15),--(Craig!F9:F258=Craig!Y9),--(Craig!F9:F258=Craig!Y10),--(Dan!X9:X258=Info!B15),--(Dan!F9:F258=Dan!Y9),--(Dan!F9:F258=Dan!Y10) Please help! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I count the number of cells that meet multiple criteria
I am working with text.
I tried each part separately and I got correct answers. Then I started combining parts and I got correct answers: =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)) and =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y10)) However, when I combine everything, I get 0 and it should be 3: =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)*(Mark!F9:F258=Mark!Y10)) I guess I could just add the separate sections and get the same result. Add the cell with the formula =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)) to the cell with formula =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y10)). I just thought it could be combined. "Bernard Liengme" wrote: The syntax looks find, so the problem must be with the data. I would test each part separately to find where the error is. for example does this =SUMPRODUCT((Mark!X9:X258=Info!B15)*1) give you the correct answer? Next does this =SUMPRODUCT((Mark!F9:F258=Mark!Y9)*1) and this =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)) Are you working with number, dates or text? and so on -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Dianna_P" wrote in message ... I want to count cells from 3 worksheets that meet multiple criteria. I've tried with a result of 0 (it should be 3): =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)*(Mark!F9:F258=Mark!Y10)*(Craig!X9:X258=In fo!B15)*(Craig!F9:F258=Craig!Y9)*(Craig!F9:F258=Cr aig!Y10)*(Dan!X9:X258=Info!B15)*(Dan!F9:F258=Dan!Y 9)*(Dan!F9:F258=Dan!Y10) I've also tried with a result of 0: =SUMPRODUCT(--(Mark!X9:X258=Info!B15),--(Mark!F9:F258=Mark!Y9),--(Mark!F9:F258=Mark!Y10),--(Craig!X9:X258=Info!B15),--(Craig!F9:F258=Craig!Y9),--(Craig!F9:F258=Craig!Y10),--(Dan!X9:X258=Info!B15),--(Dan!F9:F258=Dan!Y9),--(Dan!F9:F258=Dan!Y10) Please help! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I count the number of cells that meet multiple criteria
I tried and it didn't work. I also tried an additional paranthesis after the
2nd and 3rd SUMPRODUCT. "Bob Phillips" wrote: Are you sure that it shouldn't be separate functions =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)*(Mark!F9:F258=Mark!Y10)) +SUMPRODUCT(Craig!X9:X258=Info!B15)*(Craig!F9:F258 =Craig!Y9)*(Craig!F9:F258=Craig!Y10)) +SUMPRODUCT(Dan!X9:X258=Info!B15)*(Dan!F9:F258=Dan !Y9)*(Dan!F9:F258=Dan!Y10)) -- __________________________________ HTH Bob "Dianna_P" wrote in message ... I want to count cells from 3 worksheets that meet multiple criteria. I've tried with a result of 0 (it should be 3): =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)*(Mark!F9:F258=Mark!Y10)*(Craig!X9:X258=In fo!B15)*(Craig!F9:F258=Craig!Y9)*(Craig!F9:F258=Cr aig!Y10)*(Dan!X9:X258=Info!B15)*(Dan!F9:F258=Dan!Y 9)*(Dan!F9:F258=Dan!Y10) I've also tried with a result of 0: =SUMPRODUCT(--(Mark!X9:X258=Info!B15),--(Mark!F9:F258=Mark!Y9),--(Mark!F9:F258=Mark!Y10),--(Craig!X9:X258=Info!B15),--(Craig!F9:F258=Craig!Y9),--(Craig!F9:F258=Craig!Y10),--(Dan!X9:X258=Info!B15),--(Dan!F9:F258=Dan!Y9),--(Dan!F9:F258=Dan!Y10) Please help! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I count the number of cells that meet multiple criteria
Thought further and came up with the following with the correct answer:
=SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9))+SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark !F9:F258=Mark!Y10)) Thank you for all the help! "Dianna_P" wrote: I am working with text. I tried each part separately and I got correct answers. Then I started combining parts and I got correct answers: =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)) and =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y10)) However, when I combine everything, I get 0 and it should be 3: =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)*(Mark!F9:F258=Mark!Y10)) I guess I could just add the separate sections and get the same result. Add the cell with the formula =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)) to the cell with formula =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y10)). I just thought it could be combined. "Bernard Liengme" wrote: The syntax looks find, so the problem must be with the data. I would test each part separately to find where the error is. for example does this =SUMPRODUCT((Mark!X9:X258=Info!B15)*1) give you the correct answer? Next does this =SUMPRODUCT((Mark!F9:F258=Mark!Y9)*1) and this =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)) Are you working with number, dates or text? and so on -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Dianna_P" wrote in message ... I want to count cells from 3 worksheets that meet multiple criteria. I've tried with a result of 0 (it should be 3): =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258= Mark!Y9)*(Mark!F9:F258=Mark!Y10)*(Craig!X9:X258=In fo!B15)*(Craig!F9:F258=Craig!Y9)*(Craig!F9:F258=Cr aig!Y10)*(Dan!X9:X258=Info!B15)*(Dan!F9:F258=Dan!Y 9)*(Dan!F9:F258=Dan!Y10) I've also tried with a result of 0: =SUMPRODUCT(--(Mark!X9:X258=Info!B15),--(Mark!F9:F258=Mark!Y9),--(Mark!F9:F258=Mark!Y10),--(Craig!X9:X258=Info!B15),--(Craig!F9:F258=Craig!Y9),--(Craig!F9:F258=Craig!Y10),--(Dan!X9:X258=Info!B15),--(Dan!F9:F258=Dan!Y9),--(Dan!F9:F258=Dan!Y10) Please help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count cells that meet 2 criteria | Excel Worksheet Functions | |||
| Count the number of values in a list that meet certain criteria | Excel Worksheet Functions | |||
| Count Cells that meet Criteria | Excel Worksheet Functions | |||
| count non blank cells which meet criteria in another column | Excel Worksheet Functions | |||
| How can I count cells that meet two criteria within a filtered co. | Excel Worksheet Functions | |||
Linear Mode
