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 ?

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

On Jun 25, 12:10*am, medestar
wrote:
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 ?

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.

"Pete_UK" wrote:

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

On Jun 25, 12:10 am, medestar
wrote:
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 ?

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)*She et2!M13+(Sheet3!D8=101)*Sheet3!M13)
Type
SUM((Sheet1!D8=101)*Sheet1!M13+
the copy/paste and modify to complete the formula
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"medestar" wrote in message
...
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 ?

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.

"Bernard Liengme" wrote:

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)*She et2!M13+(Sheet3!D8=101)*Sheet3!M13)
Type
SUM((Sheet1!D8=101)*Sheet1!M13+
the copy/paste and modify to complete the formula
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"medestar" wrote in message
...
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 ?

Drat !!

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

Pete

On Jun 25, 1:17*am, "Bernard Liengme"
wrote:
Have a look in Help for *"3D formula"
SUMIF is not in the list of functions that work with 3D references

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?

"Pete_UK" wrote:

Drat !!

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

Pete

On Jun 25, 1:17 am, "Bernard Liengme"
wrote:
Have a look in Help for "3D formula"
SUMIF is not in the list of functions that work with 3D references

=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" wrote in message
...
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?

"Pete_UK" wrote:

Drat !!

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

Pete

On Jun 25, 1:17 am, "Bernard Liengme"
wrote:
Have a look in Help for "3D formula"
SUMIF is not in the list of functions that work with 3D references

That worked great, thank you very much for your help

"Bob Phillips" wrote:

=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" wrote in message
...
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?

"Pete_UK" wrote:

Drat !!

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

Pete

On Jun 25, 1:17 am, "Bernard Liengme"
wrote:
Have a look in Help for "3D formula"
SUMIF is not in the list of functions that work with 3D references