Are multiple COUNTIFs possible?

Posted by Wayne on March 17, 2001 11:16 PM

Is it possible to compair two (or more) ranges and if both cell values are true then perform a count (not a sum)? This is a simplified version of what I want to do. If range A1:A9 is "red" while the same row number in range B1:B9 is "blu" then perform a count. The answer here should be 3. I've been trying COUNTIFs, SUMIFS and IF(ANDs but I havn't gotten this to work. I've even tried the CSE (Ctrl-Shift-Enter) but I'm not sure if I've tried all the combinations yet.

A B
1 red blu
2 grn red
3 blu grn
4 red blu
5 grn red
6 blu grn
7 red blu
8 grn red
9 blu grn

Posted by Aladin Akyurek on March 18, 2001 12:17 AM

Array-enter:

=SUM((A1:A9="red")*(B1:B9="blu"))

Aladin

Posted by Dave Hawley on March 18, 2001 1:13 AM

Hi Wayne

An array formula will certainly do the count for you and is fine if you only have a few of them, but I would suggest using one the data base functions for this. My reason being is, one they are easier to edit and two, they wont slow down Excels recalculation. In your case you would be looking for the DCOUNTA and would use it like this:

1. Copy your Column headings to any 2 cell, say C1 and D1. We'll call them Color1 and Color2

2. Below Color1 (in C2) put the word: Red

3. Below Color2(in D2) put the word: Blue

Now put the formula:
=DCOUNTA(A1:B100,"Color1",C1:D2)

Where "A1:B100" is your List or Database
"Color1" is your heading heading
"C1:D2" is criteria

Using named ranges makes the formula very easy to use and could look like:

=COUNTA(Database,"Color1",Criteria)

To make life even easier still you could also use validation with a list for your criteria cells.

Dave

OzGrid Business Applications