Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Criteria COUNTIF
I am trying to count the number cells with a value greater than zero based
upon two conditions. In column A will be Department numbers. In Columns B through M will be monthly salary amounts. The work sheet will look like this: A B C D Dept Jan Feb Mar 11450 1000 1000 0 25000 0 2500 2500 70000 0 1000 1000 76500 1000 1000 1000 Domestic 1 2 1 International 1 2 2 Department numbers that begin with a number other than a 7 are Domestic Departments and Department numbers that begin with a 7 are International Departments. I am trying to use the COUNTIF for the Domestic line by saying count the cell for the month if the cell value is greater than zero and if the Department number does not begin with a 7. For the International line I would want the monthly cell counted if the cell value is greater than zero and if the Department number does begin with a 7. The Department numbers that are listed are formulas that are pulling values from another Sheet. I have been trying to use a combination of COUNTIF and MID with no luck. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Criteria COUNTIF
COUNTIF is limited to a single condition. Use this instead:
=SUMPRODUCT((LEFT($A1:$A5)<"7")*(B1:B50)) for the domestic count, and: =SUMPRODUCT((LEFT($A1:$A5)="7")*(B1:B50)) for the international count. Then copy these across for each month. Hope this helps. Pete On Sep 30, 6:56*pm, Mike wrote: I am trying to count the number cells with a value greater than zero based upon two conditions. In column A will be Department numbers. In Columns B through M will be monthly salary amounts. The work sheet will look like this: A * * * B * * * C * * * D Dept * *Jan * * Feb * * Mar 11450 * 1000 * *1000 * *0 25000 * 0 * * * 2500 * *2500 70000 * 0 * * * 1000 * *1000 76500 * 1000 * *1000 * *1000 Domestic * * * * * 1 * *2 * * * 1 International 1 2 * * * 2 Department numbers that begin with a number other than a 7 are Domestic Departments and Department numbers that begin with a 7 are International Departments. I am trying to use the COUNTIF for the Domestic line by saying count the cell for the month if the cell value is greater than zero and if the Department number does not begin with a 7. For the International line I would want the monthly cell counted if the cell value is greater than zero and if the Department number does begin with a 7. The Department numbers that are listed are formulas that are pulling values from another Sheet. I have been trying to use a combination of COUNTIF and MID with no luck. Any help would be appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Criteria COUNTIF
Hi,
If all dep't numbers are 5 digits then put this under column B =SUMPRODUCT(--($A$2:$A$5<70000)*(B$2:B$5<0)) Drag down and then right as required Mike "Mike" wrote: I am trying to count the number cells with a value greater than zero based upon two conditions. In column A will be Department numbers. In Columns B through M will be monthly salary amounts. The work sheet will look like this: A B C D Dept Jan Feb Mar 11450 1000 1000 0 25000 0 2500 2500 70000 0 1000 1000 76500 1000 1000 1000 Domestic 1 2 1 International 1 2 2 Department numbers that begin with a number other than a 7 are Domestic Departments and Department numbers that begin with a 7 are International Departments. I am trying to use the COUNTIF for the Domestic line by saying count the cell for the month if the cell value is greater than zero and if the Department number does not begin with a 7. For the International line I would want the monthly cell counted if the cell value is greater than zero and if the Department number does begin with a 7. The Department numbers that are listed are formulas that are pulling values from another Sheet. I have been trying to use a combination of COUNTIF and MID with no luck. Any help would be appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Criteria COUNTIF
OOPs
That first formula is for domestic, ise the for international =SUMPRODUCT(--($A$2:$A$5=70000)*(B$2:B$5<0)) Mike "Mike H" wrote: Hi, If all dep't numbers are 5 digits then put this under column B =SUMPRODUCT(--($A$2:$A$5<70000)*(B$2:B$5<0)) Drag down and then right as required Mike "Mike" wrote: I am trying to count the number cells with a value greater than zero based upon two conditions. In column A will be Department numbers. In Columns B through M will be monthly salary amounts. The work sheet will look like this: A B C D Dept Jan Feb Mar 11450 1000 1000 0 25000 0 2500 2500 70000 0 1000 1000 76500 1000 1000 1000 Domestic 1 2 1 International 1 2 2 Department numbers that begin with a number other than a 7 are Domestic Departments and Department numbers that begin with a 7 are International Departments. I am trying to use the COUNTIF for the Domestic line by saying count the cell for the month if the cell value is greater than zero and if the Department number does not begin with a 7. For the International line I would want the monthly cell counted if the cell value is greater than zero and if the Department number does begin with a 7. The Department numbers that are listed are formulas that are pulling values from another Sheet. I have been trying to use a combination of COUNTIF and MID with no luck. Any help would be appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Criteria COUNTIF
First make sure the department numbers in column A are genuine numbers, then:
=SUMPRODUCT((A2:A5<70000)*(B2:B50)) Domestic in Jan. =SUMPRODUCT((A2:A569999)*(B2:B50)) International in Jan For Feb, use col C in place of B -- Gary''s Student - gsnu200905 "Mike" wrote: I am trying to count the number cells with a value greater than zero based upon two conditions. In column A will be Department numbers. In Columns B through M will be monthly salary amounts. The work sheet will look like this: A B C D Dept Jan Feb Mar 11450 1000 1000 0 25000 0 2500 2500 70000 0 1000 1000 76500 1000 1000 1000 Domestic 1 2 1 International 1 2 2 Department numbers that begin with a number other than a 7 are Domestic Departments and Department numbers that begin with a 7 are International Departments. I am trying to use the COUNTIF for the Domestic line by saying count the cell for the month if the cell value is greater than zero and if the Department number does not begin with a 7. For the International line I would want the monthly cell counted if the cell value is greater than zero and if the Department number does begin with a 7. The Department numbers that are listed are formulas that are pulling values from another Sheet. I have been trying to use a combination of COUNTIF and MID with no luck. Any help would be appreciated. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Criteria COUNTIF
Try this in Col B and copy across as needed upto col M
=SUMPRODUCT((ISNUMBER(B$1:B$5)*(B$1:B$50)*(LEFT($ A$1:$A$5,1)<"7"))) =SUMPRODUCT((ISNUMBER(B$1:B$5)*(B$1:B$50)*(LEFT($ A$1:$A$5,1)="7"))) If this post helps click Yes --------------- Jacob Skaria "Mike" wrote: I am trying to count the number cells with a value greater than zero based upon two conditions. In column A will be Department numbers. In Columns B through M will be monthly salary amounts. The work sheet will look like this: A B C D Dept Jan Feb Mar 11450 1000 1000 0 25000 0 2500 2500 70000 0 1000 1000 76500 1000 1000 1000 Domestic 1 2 1 International 1 2 2 Department numbers that begin with a number other than a 7 are Domestic Departments and Department numbers that begin with a 7 are International Departments. I am trying to use the COUNTIF for the Domestic line by saying count the cell for the month if the cell value is greater than zero and if the Department number does not begin with a 7. For the International line I would want the monthly cell counted if the cell value is greater than zero and if the Department number does begin with a 7. The Department numbers that are listed are formulas that are pulling values from another Sheet. I have been trying to use a combination of COUNTIF and MID with no luck. Any help would be appreciated. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Criteria COUNTIF
Or if department numbers are different lengths use these 2
=SUMPRODUCT((LEFT($A$2:$A$5,1)<"7")*(B2:B5<0)) =SUMPRODUCT((LEFT($A$2:$A$5,1)="7")*(B2:B5<0)) Mike "Mike" wrote: I am trying to count the number cells with a value greater than zero based upon two conditions. In column A will be Department numbers. In Columns B through M will be monthly salary amounts. The work sheet will look like this: A B C D Dept Jan Feb Mar 11450 1000 1000 0 25000 0 2500 2500 70000 0 1000 1000 76500 1000 1000 1000 Domestic 1 2 1 International 1 2 2 Department numbers that begin with a number other than a 7 are Domestic Departments and Department numbers that begin with a 7 are International Departments. I am trying to use the COUNTIF for the Domestic line by saying count the cell for the month if the cell value is greater than zero and if the Department number does not begin with a 7. For the International line I would want the monthly cell counted if the cell value is greater than zero and if the Department number does begin with a 7. The Department numbers that are listed are formulas that are pulling values from another Sheet. I have been trying to use a combination of COUNTIF and MID with no luck. Any help would be appreciated. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two Criteria COUNTIF
Thank you all. I had not thought of SUMPRODUCT.
"Mike H" wrote: Or if department numbers are different lengths use these 2 =SUMPRODUCT((LEFT($A$2:$A$5,1)<"7")*(B2:B5<0)) =SUMPRODUCT((LEFT($A$2:$A$5,1)="7")*(B2:B5<0)) Mike "Mike" wrote: I am trying to count the number cells with a value greater than zero based upon two conditions. In column A will be Department numbers. In Columns B through M will be monthly salary amounts. The work sheet will look like this: A B C D Dept Jan Feb Mar 11450 1000 1000 0 25000 0 2500 2500 70000 0 1000 1000 76500 1000 1000 1000 Domestic 1 2 1 International 1 2 2 Department numbers that begin with a number other than a 7 are Domestic Departments and Department numbers that begin with a 7 are International Departments. I am trying to use the COUNTIF for the Domestic line by saying count the cell for the month if the cell value is greater than zero and if the Department number does not begin with a 7. For the International line I would want the monthly cell counted if the cell value is greater than zero and if the Department number does begin with a 7. The Department numbers that are listed are formulas that are pulling values from another Sheet. I have been trying to use a combination of COUNTIF and MID with no luck. Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif with 2 criteria | Excel Worksheet Functions | |||
countif criteria sg | Excel Worksheet Functions | |||
CountIf with 2 criteria possible? | Excel Discussion (Misc queries) | |||
Countif using format criteria not number criteria? | Excel Worksheet Functions | |||
Countif using format criteria....not number criteria? | Excel Worksheet Functions |