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

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!

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

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

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

--
Biff
Microsoft Excel MVP


"Sleepless in NJ" <Sleepless in wrote in
message ...
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!

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

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 he
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/assistan...lconPT101.aspx



Sleepless in NJ wrote:

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!

--

Dave Peterson

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

That works! Great! Thanks so much!


"T. Valko" wrote:

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

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

--
Biff
Microsoft Excel MVP


"Sleepless in NJ" <Sleepless in wrote in
message ...
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!


.

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

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

"Dave Peterson" wrote:

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 he
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/assistan...lconPT101.aspx



Sleepless in NJ wrote:

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!

--

Dave Peterson
.

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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Sleepless in NJ" wrote in message
...

That works! Great! Thanks so much!


"T. Valko" wrote:

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

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

--
Biff
Microsoft Excel MVP


"Sleepless in NJ" <Sleepless in wrote in
message ...
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!


.