=SUMIF

Question

I have a similar problem, I have tried the sandwich approach, the formula I have used is as follows =SUMIF(START:END!F5:F23,">0",START:END!K5:K23).  I have seven worksheets between the START and END sandwich.  The error reads #VALUE!.  Any suggestions much appreciated.

Answer 1

=SUMIF(START:END!F5:F23,">0",START:END!K5:K23)

You can use SUM() to cover multiple cells within a single worksheet or a single cell through multiple worksheets, but you cannot use it to SUM() multiple cells through multiple worksheets.

Click the START worksheet's name tab to activate it then hold the Shift key down and click the FINISH worksheet's name tab. In a cell that is blank to all of the selected worksheets put in this formula,

=SUMIF(F5:F23, ">0", K5:K23)

That will place the formula on that cell (e.g. AA1 for example) in all of the worksheets you've selected.

On your summary page, you can now use SUM() to drill through the worksheets with,

=SUM(START:FINISH!AA1)

Answer 2

Hi,

You essentiall want to perform a 3D sum.  Try this

1. Assume sheet names are sheet1, sheet2, sheet etc.
2. Enter these sheet names in a range of cells on your summary sheet, say I3:I5
3. On any cell in the summary sheet, enter the following formula

=SUMPRODUCT(SUMIF(INDIRECT(I3:I5&"!F5:F23"),">0",INDIRECT(I3:I5&"!K5:K23")))

Hope this helps.

Answer 3

Thank you so much, much appreciated.   I have spent days trying to solve this one, thank you once again!!!