Need SUMIF Formula Help

[LMB]

I'm trying to get a formula to work across multiple sheets in one
workbook. I can get the forumal to work for a single sheet - but not a
range of sheets. The goal is to look at cell A2 on each sheet and if it
isn't blank, I want to add the amount in cell D1 on that sheet to all
of the other sheets where A2 isn't blank.


Here's the formula that works for the individual sheet:
=SUMIF('Invoice (26)'!A2,"<>blank",'Invoice (26)'!D1)


Here's the formula I'm trying for the range of sheets that isn't
working:
=SUMIF('Invoice (1):Invoice (29)'!A2,"<>blank",'Invoice (1):Invoice
(29)'!D1)


Can anyone tell me what I'm doing wrong?


Thanks
LMB

 

[Fergus]

According to the Excel Help files, SUMIF is not on the list of functions that can accept a 3D reference (look up 3D references in Help). However SUM is on the list so maybe you should add another formula in say E1 of each invoice to read : =IF(A2>0,D1,0) then in place of your SUMIF put in =SUM('Invoice (1):Invoice (29)'!E1)

HTH

 

[Aladin Akyurek]

I'm trying to get a formula to work across multiple sheets in one
workbook. I can get the forumal to work for a single sheet - but not a
range of sheets. The goal is to look at cell A2 on each sheet and if it
isn't blank, I want to add the amount in cell D1 on that sheet to all
of the other sheets where A2 isn't blank.


Here's the formula that works for the individual sheet:
=SUMIF('Invoice (26)'!A2,"<>blank",'Invoice (26)'!D1)


Here's the formula I'm trying for the range of sheets that isn't
working:
=SUMIF('Invoice (1):Invoice (29)'!A2,"<>blank",'Invoice (1):Invoice
(29)'!D1)


Can anyone tell me what I'm doing wrong?


Thanks
LMB

See my post in:

http://www.mrexcel.com/board2/viewtopic.php?t=123314

 

[LMB]

Thanks

According to the Excel Help files, SUMIF is not on the list of functions that can accept a 3D reference (look up 3D references in Help). However SUM is on the list so maybe you should add another formula in say E1 of each invoice to read : =IF(A2>0,D1,0) then in place of your SUMIF put in =SUM('Invoice (1):Invoice (29)'!E1)

HTH

This works and is certainly a simple fix.

Thanks,
Linda