COUNTIF on more than one condition

[gpouliot]

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

 

[c_m]

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

 

[FryGirl]

Try a + sign inbetween the two formulas instead of the =

 

[gardnertoo]

Try a + sign inbetween the two formulas instead of the =

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]

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]

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

Assuming 815 is numeric and not a number stored as text:

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

Or, in Excel 2007, use COUNTIFS:

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

 

[gpouliot]

Thanks Colin it works perfectly!!