Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count with two criteria
I am working in Excel 2003, I am trying to count with two criteria and
multiple sheets. I am using this formula: =SUMPRODUCT(('Sum 1Q10'!A2:A4="BKD")*('Sum 1Q10'!F2:F4="Open")) my result is 0. It should be 1. Is there another way to do this? I really need help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count with two criteria
Lisak- There are a couple of approaches to do this; I'll give you the one I
use out of habit. The sumproduct conditions evaluate to true or false. You have to force them back to a numeric format. Some folks do that with a leading double negative on each condition. I tend to multiply each condition (the ones that have an evaluation step, not just raw numbers) by 1. Also, sumproduct separates conditions with a comma. Example: =SUMPRODUCT(('Sum 1Q10'!A2:A4="BKD")*1,('Sum 1Q10'!F2:F4="Open")*1) Hope that helps, Keith "LisaK" wrote: I am working in Excel 2003, I am trying to count with two criteria and multiple sheets. I am using this formula: =SUMPRODUCT(('Sum 1Q10'!A2:A4="BKD")*('Sum 1Q10'!F2:F4="Open")) my result is 0. It should be 1. Is there another way to do this? I really need help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count with two criteria
Just check that you do have "BKD" and "Open" in those cells, and that
there are no extra spaces (or non-breaking space characters) in there - use =LEN(A2) to find out how many characters you have in A2, and apply this to the other cells. Hope this helps. Pete On Mar 18, 4:39*pm, LisaK wrote: I am working in Excel 2003, I am trying to count with two criteria and multiple sheets. I am using this formula: *=SUMPRODUCT(('Sum 1Q10'!A2:A4="BKD")*('Sum 1Q10'!F2:F4="Open")) my result is 0. It should be 1. Is there another way to do this? *I really need help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count if 2 criteria are met | Excel Worksheet Functions | |||
Count how many criteria in a column match criteria in another colu | Excel Discussion (Misc queries) | |||
Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
Criteria count | Excel Discussion (Misc queries) | |||
Count If 3 Criteria Met | Excel Worksheet Functions |