| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count multiple criteria
I want to get a count based on three columns of data. In column A I have a
department code, in column B I have plan IDs, and in column C I have a flag that indicates if this is a financial adjustment. I want to count all the plan IDs by department code where it is NOT a financial adjustment(or conversely, where it says "Current"). A B C 004 Plan1 Adj 004 Plan1 Current After reading many of the posts on this forum, I've been trying to get SUMPRODUCT to work. This is what I've written so far: =SUMPRODUCT(--(A$13:A$15000="004"),--(B$13:B$15000="PLAN1"),--(C$13:C$15000="Current")) I should get a count of 10, but it returns 0 not matter how I tweak it. What am I doing wrong? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count multiple criteria
=SUMPRODUCT(--(A$13:A$15000="004"),--(B$13:B$15000="PLAN1"),--(C$13:C$15000="Current")) It's probably due to data inconsistencies if the above is not returning properly Try this more robust version of the above, which should cover all possibilities: =SUMPRODUCT(--(TEXT(A$13:A$15000,"000")="004"),--(TRIM(B$13:B$15000)="PLAN1"),--(TRIM(C$13:C$15000)="Current")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "FinChase" wrote: I want to get a count based on three columns of data. In column A I have a department code, in column B I have plan IDs, and in column C I have a flag that indicates if this is a financial adjustment. I want to count all the plan IDs by department code where it is NOT a financial adjustment(or conversely, where it says "Current"). A B C 004 Plan1 Adj 004 Plan1 Current After reading many of the posts on this forum, I've been trying to get SUMPRODUCT to work. This is what I've written so far: =SUMPRODUCT(--(A$13:A$15000="004"),--(B$13:B$15000="PLAN1"),--(C$13:C$15000="Current")) I should get a count of 10, but it returns 0 not matter how I tweak it. What am I doing wrong? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count multiple criteria
The first thing I'd check is your dept numbers. Excel doesn't like numbers
with leading 0s. Are the dept numbers TEXT entries or are they numbers using a custom format so as to display the leading 0s? =SUMPRODUCT(--(A$13:A$15000="004") The way you have the formula written it's looking at the dept numbers as TEXT entries. Since it doesn't seem to recognize "004" perhaps they're custom formatted numbers. Are all dept numbers 3 digits? Try one of these: =SUMPRODUCT(--(A$13:A$15000=4),--(B$13:B$15000="PLAN1"),--(C$13:C$15000="Current")) =SUMPRODUCT(--(TEXT(A$13:A$15000,"000")="004"),--(B$13:B$15000="PLAN1"),--(C$13:C$15000="Current")) -- Biff Microsoft Excel MVP "FinChase" wrote in message ... I want to get a count based on three columns of data. In column A I have a department code, in column B I have plan IDs, and in column C I have a flag that indicates if this is a financial adjustment. I want to count all the plan IDs by department code where it is NOT a financial adjustment(or conversely, where it says "Current"). A B C 004 Plan1 Adj 004 Plan1 Current After reading many of the posts on this forum, I've been trying to get SUMPRODUCT to work. This is what I've written so far: =SUMPRODUCT(--(A$13:A$15000="004"),--(B$13:B$15000="PLAN1"),--(C$13:C$15000="Current")) I should get a count of 10, but it returns 0 not matter how I tweak it. What am I doing wrong? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count multiple criteria
Thanks to both Max and T. Valko. You're information helped. You were
correct that the company codes were not formatted as text. Also, it turned out that some of my plan ids also had trailing spaces, so once I cleaned the data up, the formulas worked perfectly. "T. Valko" wrote: The first thing I'd check is your dept numbers. Excel doesn't like numbers with leading 0s. Are the dept numbers TEXT entries or are they numbers using a custom format so as to display the leading 0s? =SUMPRODUCT(--(A$13:A$15000="004") The way you have the formula written it's looking at the dept numbers as TEXT entries. Since it doesn't seem to recognize "004" perhaps they're custom formatted numbers. Are all dept numbers 3 digits? Try one of these: =SUMPRODUCT(--(A$13:A$15000=4),--(B$13:B$15000="PLAN1"),--(C$13:C$15000="Current")) =SUMPRODUCT(--(TEXT(A$13:A$15000,"000")="004"),--(B$13:B$15000="PLAN1"),--(C$13:C$15000="Current")) -- Biff Microsoft Excel MVP "FinChase" wrote in message ... I want to get a count based on three columns of data. In column A I have a department code, in column B I have plan IDs, and in column C I have a flag that indicates if this is a financial adjustment. I want to count all the plan IDs by department code where it is NOT a financial adjustment(or conversely, where it says "Current"). A B C 004 Plan1 Adj 004 Plan1 Current After reading many of the posts on this forum, I've been trying to get SUMPRODUCT to work. This is what I've written so far: =SUMPRODUCT(--(A$13:A$15000="004"),--(B$13:B$15000="PLAN1"),--(C$13:C$15000="Current")) I should get a count of 10, but it returns 0 not matter how I tweak it. What am I doing wrong? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count multiple criteria
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "FinChase" wrote in message ... Thanks to both Max and T. Valko. You're information helped. You were correct that the company codes were not formatted as text. Also, it turned out that some of my plan ids also had trailing spaces, so once I cleaned the data up, the formulas worked perfectly. "T. Valko" wrote: The first thing I'd check is your dept numbers. Excel doesn't like numbers with leading 0s. Are the dept numbers TEXT entries or are they numbers using a custom format so as to display the leading 0s? =SUMPRODUCT(--(A$13:A$15000="004") The way you have the formula written it's looking at the dept numbers as TEXT entries. Since it doesn't seem to recognize "004" perhaps they're custom formatted numbers. Are all dept numbers 3 digits? Try one of these: =SUMPRODUCT(--(A$13:A$15000=4),--(B$13:B$15000="PLAN1"),--(C$13:C$15000="Current")) =SUMPRODUCT(--(TEXT(A$13:A$15000,"000")="004"),--(B$13:B$15000="PLAN1"),--(C$13:C$15000="Current")) -- Biff Microsoft Excel MVP "FinChase" wrote in message ... I want to get a count based on three columns of data. In column A I have a department code, in column B I have plan IDs, and in column C I have a flag that indicates if this is a financial adjustment. I want to count all the plan IDs by department code where it is NOT a financial adjustment(or conversely, where it says "Current"). A B C 004 Plan1 Adj 004 Plan1 Current After reading many of the posts on this forum, I've been trying to get SUMPRODUCT to work. This is what I've written so far: =SUMPRODUCT(--(A$13:A$15000="004"),--(B$13:B$15000="PLAN1"),--(C$13:C$15000="Current")) I should get a count of 10, but it returns 0 not matter how I tweak it. What am I doing wrong? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count multiple criteria
welcome ..
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "FinChase" wrote in message ... Thanks to both Max and T. Valko. You're information helped. You were correct that the company codes were not formatted as text. Also, it turned out that some of my plan ids also had trailing spaces, so once I cleaned the data up, the formulas worked perfectly. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNT WITH MULTIPLE CRITERIA | Excel Worksheet Functions | |||
| Count Multiple Criteria | Excel Worksheet Functions | |||
| COUNT or SUM with multiple criteria | Excel Worksheet Functions | |||
| Multiple Count Criteria | Excel Worksheet Functions | |||
| I need to count while using multiple criteria | Excel Worksheet Functions | |||
Linear Mode
