Counting all "F"s in column A where there is also an "A" in column

[Sleepless in NJ]

I am trying to find the count of all the cells that have an F in one column
and also have an A in another column and I'm getting an error. I hope Excel
can do this with a simple formula. I don't do macros.

Here's an example:

1 A F
2 B F
3 A P
4 A N/A
5 B P
6 A F

Basically I am trying to determine how many "A" level requirement have
Passed, how many have Failed, how many are N/A and the same for the B level
requirements. I've already tallied the total Fails and Passes with a countif
for each column. Now I need to find how many of the Fails are As, Bs and Cs.
Same for the Passes. Tell me it's simple!

 

[T. Valko]

For the combination of "A" and "F" try this...

=SUMPRODUCT(--(A1:A6="A"),--(B1:B6="F"))

 

[Dave Peterson]

If you're using xl2007+, take a look at =countifs() in Excel's help.

If you're using an earlier version:

=sumproduct(--(a1:a10="A"),--(b1:b10="F"))

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

=========
I think I'd use a pivottable. You could get a nice summary table that shows the
level in the first column and the various grades across the table.

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

 

[Sleepless in NJ]

That works! Great! Thanks so much!

For the combination of "A" and "F" try this...

=SUMPRODUCT(--(A1:A6="A"),--(B1:B6="F"))

--
Biff
Microsoft Excel MVP





.

 

[Sleepless in NJ]

Yes, just as in the post above yours, it works great! I'm using Office 2003
and it worked perfectly. Thanks!

 

[T. Valko]

You're welcome. Thanks for the feedback!