count if statements

[omegoku]

Hey
I've got a table which includes a column for type and a column for status

I want to do a count for each combination, for example where type is "LAN" and status is "open"

Type is in column D, Status is in column I
I've tried
=COUNT(IF(I22:I100="Open",IF(D22:D100="LAN",I22:I100)))
which I think should count if both are true but i get a 0
and I've tried
=COUNTIF(I22:I1000,"Open Late")+COUNTIF(D22:D1000, "LAN")
but that just adds up the 2 seperate counts, it doesn't count where they are both true only

So, does anyone see where the error lies? Anyone got any suggestions?
Thanks for any input

 

[erik.van.geit]

Hi,

have a look at SUMPRODUCT
=SUMPRODUCT(--(I22:I100="Open"),--(D22:D100="LAN"))

kind regards,
Erik

EDIT: this is not casesensitive

 

[omegoku]

Thank you kind sir, that worked perfectly!

 

[Artorius]

SUMPRODUCT works much better but the COUNT formula would have been I believe .. using COUNTA for counting text ......

=COUNTA(IF(I22:I100,"Open"),IF(D22:D100,"LAN"))

 

[erik.van.geit]

SUMPRODUCT works much better but the COUNT formula would have been I believe .. using COUNTA for counting text ......

=COUNTA(IF(I22:I100,"Open"),IF(D22:D100,"LAN"))

I cannot get this to work, but don't think it would.
did it for you ?

 

[Artorius]

Oops. Apologies. I used a 6 row/column example here and it "appeared" to work.

It did not work when I expanded ........ SUMPRODUCT does

Apologies for my moment of madness ... I'll test more thoroughly before posting next time.