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
|
|||
|
|||
Help, I need a formula for work.
Help, I need a formula for work. Here is a generic version of my problem? Leauge Team Attendance exceed 20,000? American Yankees Yes American Red Sox No National Phillies Yes National Mets Yes National Giants Yes National Dodgers No American Twins No American Angels Yes =COUNTIF(E3:E10,"YES") Total would be 5 "YES'' Answers ______________________________________________ Now the spreadsheet is FILTERED to only show "AMERICAN" Leauge Team Attendance exceed 20,000? American Yankees Yes American Red Sox No American Twins No American Angels Yes =COUNTIF(E3:E10,"YES") Does anybody know of a formula that I may use the same range (e3:e10) but now since it is FILTERED the answer would be 2 instead of 5? Any help would be greatly appreciated. Thanks in advance. -- Che028 |
#2
|
|||
|
|||
Help, I need a formula for work.
On Sep 20, 4:23*pm, Che028 wrote:
Help, I need a formula for work. Here is a generic version of my problem? * * * * Leauge *Team * *Attendance exceed 20,000? * * * * * * * American * * * * * * Yankees * * * * Yes * * * * * * * * American * * * * * * Red Sox * * * * No * * * * National * * * * * * *Phillies * * * Yes * * * * * * * * National * * * * * * *Mets * * * * * * * * * * * Yes * * * * * * National * * * * * * *Giants * * * * Yes * * * * * * * * National * * * * * * *Dodgers * * * *No * * * * American * * * * * * *Twins * * * * *No * * * * American * * * * * * *Angels * * * * Yes * * * * =COUNTIF(E3:E10,"YES") Total would be 5 "YES'' Answers * * * * ______________________________________________ * * * * * * * * * Now the spreadsheet is FILTERED to only show "AMERICAN" * * * * Leauge *Team * *Attendance exceed 20,000? * * * * * * * American * * * *Yankees Yes * * * * * * American * * * *Red Sox No * * * * * * * American * * * *Twins * No * * * * * * * American * * * *Angels *Yes * * =COUNTIF(E3:E10,"YES") Does anybody know of a formula that I may use the same range (e3:e10) but now since it is FILTERED the answer would be 2 instead of 5? Any help would be greatly appreciated. Thanks in advance. -- Che028 You could use the SUBTOTAL function but you will need to have a column with 1 when the corresponding column E value is "Yes" and 0 when it isn't. This column of 1s and 0s could be achieved using =IF(E3="Yes", 1,0) filled down as far as required. Say the column with the 1s and 0s is column F, then the formula that would count the visible "Yes" values would be... =SUBTOTAL(109,F3:F10) Ken Johnson |
#3
|
|||
|
|||
Help, I need a formula for work.
Why not place the data into a Pivot Table and let Excel do the manipulation
without any formulas required... Spin the data to what ever view you wish/need... Place the cursor on any cell in a contiguous range and select DataPivotTable and PivotChart Reporting... -- Joe Mac "Che028" wrote: Help, I need a formula for work. Here is a generic version of my problem? Leauge Team Attendance exceed 20,000? American Yankees Yes American Red Sox No National Phillies Yes National Mets Yes National Giants Yes National Dodgers No American Twins No American Angels Yes =COUNTIF(E3:E10,"YES") Total would be 5 "YES'' Answers ______________________________________________ Now the spreadsheet is FILTERED to only show "AMERICAN" Leauge Team Attendance exceed 20,000? American Yankees Yes American Red Sox No American Twins No American Angels Yes =COUNTIF(E3:E10,"YES") Does anybody know of a formula that I may use the same range (e3:e10) but now since it is FILTERED the answer would be 2 instead of 5? Any help would be greatly appreciated. Thanks in advance. -- Che028 |
Thread Tools | |
Display Modes | |
|
|