count if statements

omegoku

New Member
Joined
May 15, 2006
Messages
38
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

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

kind regards,
Erik

EDIT: this is not casesensitive
 

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Board Regular
Joined
Apr 18, 2006
Messages
176
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.
 

Forum statistics

Threads
1,141,757
Messages
5,708,346
Members
421,566
Latest member
7Nabisco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top