SUMIF formula that crosses multiple sheet tabs

J

JDaywalt

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$10000)

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?
 
B

Bob Phillips

=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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top