# COUNTIF on more than one condition

#### gpouliot

##### New Member
Hi,

I'm using the following to count the records for anything in 815 + CSP/Trunk. But the results I'm getting = FALSE??

=COUNTIF(ONSITEINVT!\$A\$2:\$A\$25000,"815")=COUNTIF(ONSITEINVT!\$F\$2:\$F\$25000,"CSP/Trunk")

How do I get this to work to give me my count value base on my two conditions?

Thanks

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### c_m

##### Well-known Member
Try:
=
SUMPRODUCT(--(ONSITEINVT!\$A\$2:\$A\$25000,815),--(ONSITEINVT!\$F\$2:\$F\$25000,"CSP/Trunk"))

#### gardnertoo

##### Well-known Member

That would give an artificially high number. It would add up every "815" and every "CSP/TRUNK", even if they don't appear together. The SUMPRODUCT approach is the way to go.

#### gpouliot

##### New Member
This now works! The issue I'm now having is I did a manual sort (Filter) on my ONSITEINVT 815 and CSP/Trunk and when I highlight the records the count = 47 records. Which is correct but when I use the formulas I'm getting 996 records?
What I'm trying to do is anything in district "815" that is also a "CSP/Trunk" then count how many records.

#### Colin Legg

##### MrExcel MVP
There's a couple of typos on that SUMPRODUCT() formula though...

Assuming 815 is numeric and not a number stored as text:
Rich (BB code):
``````=SUMPRODUCT(
--(ONSITEINVT!\$A\$2:\$A\$25000=815),
--(ONSITEINVT!\$F\$2:\$F\$25000="CSP/Trunk"))``````

Or, in Excel 2007, use COUNTIFS:
Rich (BB code):
``````=COUNTIFS(
ONSITEINVT!\$A\$2:\$A\$25000,815,
ONSITEINVT!\$F\$2:\$F\$25000,"CSP/Trunk")``````

#### gpouliot

##### New Member
Thanks Colin it works perfectly!!

Replies
0
Views
129
Replies
5
Views
168
Replies
1
Views
251
Replies
0
Views
509
Replies
3
Views
245

1,191,029
Messages
5,984,226
Members
439,878
Latest member
melodysc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back