sumif function with multiple sheets

[medestar]

I have several sheets in a workbook. I am using the first page (other than
the summary sheet at the very front) as page1, the last page is page3. I am
trying to add the numbers in a specific cell (m13) from each page if another
cell has specific criteria (d8=89). here is the formula I am using:

=SUMIF(Sheet1:Sheet3!D8,101,Sheet1:Sheet3!M13)

however, I keep getting the #VALUE! error message. I cannot figure out
where the error is. ANy help ?

 

[Pete_UK]

Are you sure that M13 on those sheets does actually contain a number
(or blank)? You usually get that error message if you try to add some
text values together, so check that you don't have text values that
happen to look like numbers.

Hope this helps.

Pete

 

[Bernard Liengme]

Have a look in Help for "3D formula"
SUMIF is not in the list of functions that work with 3D references

This works
SUM((Sheet1!D8=101)*Sheet1!M13+(Sheet2!D8=101)*Sheet2!M13+(Sheet3!D8=101)*Sheet3!M13)
Type
SUM((Sheet1!D8=101)*Sheet1!M13+
the copy/paste and modify to complete the formula
best wishes

 

[medestar]

Yes, I just checked now, and they do contain numbers. I also formatted the
cells as numbers. One thing to note, is that the cell may be blank, but in
this case, I ensured each cell has a number.

 

[medestar]

I can see where that would work. However, new sheets will be added (it may
end up being a large number), so I want to avoid having to list every sheet
in the formula.

 

[Pete_UK]

Drat !!

I forgot about that Bernard - time for some coffee, I think !! <bg>

Pete

 

[medestar]

Ok, I looked up 3D formula, and as you said, the sumif function is not
listed. So how can I work around this ? Any thoughts?

 

[Bob Phillips]

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!D8"),101,
INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!M31")))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[medestar]

That worked great, thank you very much for your help