Formula needed

[Guest]

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?

 

[Max]

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