SUM VALUES on MULTIPLE SHEETS

L

lotivbarjoh

I need a formula to sum values in the same column on multiple sheets
if it finds a specified value in the same row of a different column.
SUMIF works fine if I select a single sheet but as soon as I select
multiple sheets it errors and returns #VALUE
 
H

Herbert Seidenberg

Assume you have a Summary sheet and
3 sheets called Y2005, Y2006 and Y2007.
On each of the year sheets, you have 2 columns
named Month and Sales.
On the Summary sheet, you want to find the
the total January sales for all years.
On the Summary sheet enter January into
the cell named Target and enter this formula:
=SUMPRODUCT(SUMIF(INDIRECT("Y"&TEXT(ROW(INDIRECT
("2005:2007")),"0000")&"!Month"),Target,
INDIRECT("Y"&TEXT(ROW(INDIRECT("2005:2007")),"0000")&"!Sales")))
 
Top