If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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 news 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! |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 news 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! . |
#5
|
|||
|
|||
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 . |
Thread Tools | |
Display Modes | |
|
|