Two Criteria COUNTIF

[Mike]

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.

 

[Pete_UK]

COUNTIF is limited to a single condition. Use this instead:

=SUMPRODUCT((LEFT($A1:$A5)<>"7")*(B1:B5>0))

for the domestic count, and:

=SUMPRODUCT((LEFT($A1:$A5)="7")*(B1:B5>0))

for the international count.

Then copy these across for each month.

Hope this helps.

Pete

 

[Mike H]

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 H]

OOPs

That first formula is for domestic, ise the for international

=SUMPRODUCT(--($A$2:$A$5>=70000)*(B$2:B$5<>0))

Mike

 

[Gary''s Student]

First make sure the department numbers in column A are genuine numbers, then:

=SUMPRODUCT((A2:A5<70000)*(B2:B5>0)) Domestic in Jan.
=SUMPRODUCT((A2:A5>69999)*(B2:B5>0)) International in Jan


For Feb, use col C in place of B

 

[Jacob Skaria]

Try this in Col B and copy across as needed upto col M
=SUMPRODUCT((ISNUMBER(B$1:B$5)*(B$1:B$5>0)*(LEFT($A$1:$A$5,1)<>"7")))

=SUMPRODUCT((ISNUMBER(B$1:B$5)*(B$1:B$5>0)*(LEFT($A$1:$A$5,1)="7")))

If this post helps click Yes

 

[Mike H]

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]

Thank you all. I had not thought of SUMPRODUCT.

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