Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Formula needed
Scenario:
Six Columns, the first column is CITY and the other five are Fiscal Year Columns (i.e., FY05, FY04, FY03, etc.) but not all cells under the fiscal year columns are populated. I want to count the populated cells under the fiscal year (FY) column if it appears next to the cell with a specific city. (Under the city column, a city_name can appear multiple times.) Basically, if the city is X and the FY cell is populated, then count the cell. Any suggestion on how to create this formula/function? |
#2
|
|||
|
|||
One way ..
Assume source table below is in Sheet1 -------- FY05 FY04 FY03 FY02 City1 6000 6000 6000 6000 City2 6000 6000 6000 6000 City1 6000 6000 6000 6000 City2 6000 6000 6000 6000 etc In Sheet2: ----- You have the table below, with City listed in A2 down -------- FY05 FY04 FY03 FY02 City1 City2 etc Put in B2: =SUMPRODUCT((Sheet1!$A$2:$A$10=$A2)*(Sheet1!B$2:B$ 10<"")) Copy B2 across and down to populate the table Adapt the ranges to suit -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- Viewpoint wrote in message ... Scenario: Six Columns, the first column is CITY and the other five are Fiscal Year Columns (i.e., FY05, FY04, FY03, etc.) but not all cells under the fiscal year columns are populated. I want to count the populated cells under the fiscal year (FY) column if it appears next to the cell with a specific city. (Under the city column, a city_name can appear multiple times.) Basically, if the city is X and the FY cell is populated, then count the cell. Any suggestion on how to create this formula/function? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! Formula needed. | Excel Discussion (Misc queries) | |||
Formula needed | Excel Worksheet Functions | |||
Help, Excel Formula Needed -- Urgent | Excel Worksheet Functions | |||
Look Up and Cell Reference - Formula Help Needed | Excel Worksheet Functions | |||
Complex formula help needed | Excel Worksheet Functions |