Excel - Compound CountIf formula

Asked By PeteRoone on 16-Apr-08 08:42 AM
```Good afternoon,
I have a database where column A contains one of three values, A, B or C,
and Column J contains a status indicator "Y" or "N"

I want to write formulae that display counts of all rows where Col A = "A"
and Col J = "Y", where Col A = "B" and Col J = Y" and where Col A = "C" and
Col J = "Y".
Is there a quick way to do this using COUNTIF without having to set up a
criteria range and doing it with DSUM?

Pete```

Ivyleaf replied on 18-Apr-08 02:55 AM
```On Apr 16, 10:42=A0pm, Pete Rooney
A"
=3D "C" and

Hi Pete,

If you could handle having a 'helper' column, you could put the
following formula in say column L:

=3DOR(AND(A1=3D"A",J1=3D"Y"),AND(A1=3D"B",J1=3D"Y"),AND(A1=3D"C",J1=3D"Y"))

Then just do your COUNTIF on column L with a criteria of TRUE.

Cheers,
Ivan.```
Teethlessmam replied on 16-Apr-08 09:55 AM
`=COUNTIF(J:J,"Y")`
Dave Peterson replied on 16-Apr-08 10:01 AM
```If you're using xl2007, look at =countifs() in Excel's help.

In any version...

=sumproduct(--(a1:a10="a"),--(j1:j10="Y"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

=========

You may want to consider using a pivottable.
You can a row of headers to all the columns (if you don't have them).

Data|Pivottable
follow the wizard until you get to a dialog with Layout on it.
Click that Layout button
Drag the header for ColA into the row field.
Drag the header for ColJ into the column field.
Then drag the header for ColJ (a second time!) into the Data field).
(It should say "Count of..." in the data field.)

Then finish up the wizard.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

--

Dave Peterson```
PeteRoone replied on 16-Apr-08 11:12 AM
```Dave,

This works just fine - I had lots of SUMPRODUCT examples, just not one to do
this. You've just been responsible for the death of a worksheet full of
criteria ranges - I hope you can live with yourself! :-)

Thanks

Pete```
Dave Peterson replied on 16-Apr-08 12:38 PM
```Before you celebrate too much, you may want to try the pivottable.

I bet with lots and lots of these =sumproduct() formulas (and lots and lots of
rows to check), the calculation time can slow downnnnnnnnnnnn.

--

Dave Peterson```