countif with more than 1 criteria

[JotSousa]

Hi all,

I'm trying to use the countif function to do the following:

I have a table like the one below

Column A Column B Column C
Grade New/Existing Month started if New
AM New Mar-07
CSM Existing
CSM New Feb -07

etc..

Bascially, I need to be able to countif the grade is AM and the person is New and they started in Month Mar-07.

Any ideas???

Thanks so so much

JOT

 

[patrickmuldoon99]

Create a column adjacent to your data set, that concatenates the values like so:

=A1 & "@" & B1 & "@" & C1

This will then create a concatenated field that should look like

AM@New@Mar-07

You can then do a countif on that column like so:

=COUNTIF("D1:D100","AM@New@Mar-07")

Hope this helps

Patrick

 

[northwolves]

Use sumproduct to solve it as the following:

=sumproduct((a1:a100="AM")*(B1:B100="New")*(c1:c100="Mar-07"))

Best Regards.

 

[JotSousa]

Hi both

Thanks very much for your help. I ended up creating a table and using the dcounta function, but both of your suggestions are great and I'll use them in future.

Have a great day!

JOT :wink: