Countif with 2+ Criteria

  • Thread starter Thread starter muziq2
  • Start date Start date
M

muziq2

HI:

I'm looking for a way to use the countif function to look at 2 criteri
before counting only the data that matches both.

For example, say I have a table with column headings of department an
costs. In another sheet I want to chart the cost by department broke
out by the amount of the cost.

i.e. =countif(L1:L1000,"<5000")

Right now I have to set up a seperate sheet for each department. Wha
I want is to be able to add a statement that says... countif th
department column equals "department 1" and the costs column i
"<5000".

Is there a way to do this?

Thanks,

Jef
 
You could use an array formula like:

=SUM(IF((L1:L1000<5000)*(M1:M1000="Department 1"),1,0))

Enter this formula with Ctrl-Shift-Enter instead of just hitting Enter.

/i.
 
Or, if you don't want to use the array entered
=SUM(IF((L1:L1000<5000)*(M1:M1000="Department 1"),1,0))
Click to expand...
=SUMproduct((L1:L1000<5000)*(M1:M1000="Department 1"))
--
Don Guillett
SalesAid Software
(e-mail address removed)
immanuel said:
You could use an array formula like:

=SUM(IF((L1:L1000<5000)*(M1:M1000="Department 1"),1,0))

Enter this formula with Ctrl-Shift-Enter instead of just hitting Enter.

/i.
Click to expand...
 
Back
Top