Using sumif on multiple sheets

[Jazzer]

Hi,

SUMIF doesn't support 3D ranges. This means that all the ranges used
with SUMIF function has to be in one sheet.

One possibility is to put SUMIF in each sheet and then use SUM
function to add all those SUMIF's together. SUM function has a 3D
support.

- Asser

 

[Fawn]

Can you use the sumif formula to calculate if the data is on 12 different
sheets. Any help please

Thanks

 

[Jazzer]

Hi,

I found an other way around this problem. This only works if all th
sheets that are used are named in a sequence. In my example I use th
default sheets: Sheet1, Sheet2 and Sheet 3. Then you can use an arra
formula like this:

=SUM(SUMIF(OFFSET(INDIRECT(ADDRESS(1,1,,,"Sheet"&ROW(1:3))),,,3,1),">2",OFFSET(INDIRECT(ADDRESS(1,1,,,"Sheet"&ROW(1:3))),,,3,1)))

Array formulas are not entered as normal formulas by pressing jus
Enter. Instead, you have to press Ctrl + Shift + Enter to enter th
formula. Array formulas are indicated with { } characters around th
formula when it is in a cell.

Now in this example the "Sheet"&ROW(1:3) means that all the sheet
from 1 to 3 are included in calculation. You should change that to wha
ever you need.

The range of the calculation is build i
OFFSET(INDIRECT(ADDRESS(1,1,,,"Sheet"&ROW(1:3))),,,3,1) part of th
funcion. Inside the ADDRESS function the first "1,1" means the row an
column numbers of the upper left corner of the area. In this case A1.

The last numbers of OFFSET function ("3,1") are the height and th
width of the area. So in this case the area is A1:A3.

Otherwise use the SUMIF function as you normally would use. In thi
case the criteria is ">2".


Hope you can make something out of this.

- Asse