SUMIF formula that crosses multiple sheet tabs
 

I have 14 sheet tabs that are named 1, 2, 3, 4, .... 14. These tabs are
positioned next to each other in the workbook. I am trying to create a
summary page that does a "SUMIF" across all of these tabs based upon criteria
that exists in cell B3 of the Summary tab. Here are the specifics:

Sheets 1-14 have product descriptions in column G
Sheets 1-14 have sales for each product in column K
cell B3 on Summary tab = "Soup"

Here is the formula I currently have in cell B4 of the Summary tab:

=SUMIF('1:14'!$G$9:$G$10000,B3,'1:14'!$K$9:$K$1000 0)

The formula results in a #VALUE error. I tried entering as an array formula
too (Ctrl+Shift+Enter), but got the same result. Clearly the SUMIF formula
is not working, but is there another alternative to accomplish the same goal?

SUMIF formula that crosses multiple sheet tabs
 

=SUMPRODUCT(SUMIF(INDIRECT(ROW(INDIRECT("1:14"))&" !$G$9:$G$10000"),B3,INDIRECT(ROW(INDIRECT("1:14")) &"!$K$9:$K$10000")))

--
HTH

Bob

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

"JDaywalt" wrote in message
...
I have 14 sheet tabs that are named 1, 2, 3, 4, .... 14. These tabs are
positioned next to each other in the workbook. I am trying to create a
summary page that does a "SUMIF" across all of these tabs based upon
criteria
that exists in cell B3 of the Summary tab. Here are the specifics:

Sheets 1-14 have product descriptions in column G
Sheets 1-14 have sales for each product in column K
cell B3 on Summary tab = "Soup"

Here is the formula I currently have in cell B4 of the Summary tab:

=SUMIF('1:14'!$G$9:$G$10000,B3,'1:14'!$K$9:$K$1000 0)

The formula results in a #VALUE error. I tried entering as an array
formula
too (Ctrl+Shift+Enter), but got the same result. Clearly the SUMIF
formula
is not working, but is there another alternative to accomplish the same
goal?