# Need SUMIF Formula Help

#### LMB

##### New Member
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

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Fergus

##### Well-known Member
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

##### MrExcel MVP
LMB said:
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

##### New Member
Thanks

Fergus said:
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

Replies
2
Views
211
Replies
12
Views
310
Replies
6
Views
355
Replies
1
Views
411
Replies
6
Views
130

1,195,956
Messages
6,012,536
Members
441,704
Latest member
Perez1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back