Compound CountIf formula

[Pete Rooney]

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?

Thanks in advance

Pete

 

[Ivyleaf]

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?

Thanks in advance

Pete

Hi Pete,

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

=OR(AND(A1="A",J1="Y"),AND(A1="B",J1="Y"),AND(A1="C",J1="Y"))

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

Cheers,
Ivan.

 

[Teethless mama]

=COUNTIF(J:J,"Y")

 

[Dave Peterson]

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).

Then (in xl2003 menus):
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/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Pete Rooney]

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]

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.