SUMIF? -multiple worksheets, add all cells adjacent to cell w/ value matching query

[Giustino]

Hi!

I have a working knowledge of excel but am stumped; I have an excel file that has roughly 50 worksheets inside of it, 49 of which are invoices (identical in format, differing only in specific product codes/quantities).
On each invoice column E (E19:E23) contains the product codes and to the right column G (G19:G23) contains the quantity sold of said product on the same row.

The 50th worksheet (I've named it "PULLS") is a breakdown on summaries of how many of each product (of which there are several dozen) are sent out between all the accounts. That is; on said summary sheet ("PULLS") I have two columns, column A (A2:A48) display each of the different 46 product codes, column B (B2:B48) will search all the other workbooks for incidents of the corresponding product code, then add up all of the cells that appear in column G on the same row as those incident cells. That'll tell me how many of each product code were sold in total between all the accounts. If I wasn't dealing with adjacent cells then I could probably figure this formula out.

EG

On the summary worksheet ("PULLS"):
B32= sum(all cells in each worksheet in column g that are on the same row as the product code that matches A32 on the summary worksheet)

I've attached a copy of the sheet but they have different pack codes and quantities than what we actually send out (not that it matters, just that I'm required by company policy not to release)

I hope that is descriptive enough, thanks in advance for the help


Edit: I figured out how to put it together from one worksheet but I don't know how to include all worksheets.

[NBVC]

If you install a free addin from here: Morefunc.xll

Then you can apply this formula in D4 of the Pulls sheet, which uses the THREED function from the addin:

=SUMPRODUCT(--(THREED('ISLET - ISLET CLINIC:UOFA - CS-SPD BULK #1'!$E$19:$E$41)=C4),THREED('ISLET - ISLET CLINIC:UOFA - CS-SPD BULK #1'!$G$19:$G$41))

copied down.

Note: The addin has the added functionality that allows you to embed it into the workbook so that you can share it without users having to reload addin.

[Giustino]

I appreciate your help, I am having a little trouble however.

I installed morefunc, restarted excel, opened the exact copy of my workbook that I updated, pasted the THREED formula you provided into cell D4 on the "PULLS" page but I get a #REF! error, even if I drag the formula all the way down the pulls page. Please advise.

[NBVC]

Here is your sample workbook with the formula I submitted...

It worked fine for me.

Can you double-check... also that your sheetnames match those in the formula exactly...

[Giustino]

Thanks, NBVC! That sheet works great. I don't really know how it worked but I've been spending time playing with those functions, specifically "threed".

I have a question, with the exact same sheet as above. What I want to do now is run a total (same as what I have) but have it count the total if the quota is listed to go THAT day. On each quota sheet (B11:H11) is an X if the quota is filled that day. I need to draw, in the pulls sheet, a table that counts out the total packs sent out per day.. (function will sift through all sheets, and sum each pack on that sheet against all other packs on sheets going that day, providing a total on the pulls page per pack, per day)

What I want it to tell me is.. all the packs going out on each day of the week essentially. What I've tried to do is:

=SUMPRODUCT(--(THREED('ISLET - ISLET CLINIC:UOFA - CS-SPD BULK #1'!B11)=X),(THREED('ISLET - ISLET CLINIC:UOFA - CS-SPD BULK #1'!$E$19:$E$41)=C7),THREED('ISLET - ISLET CLINIC:UOFA - CS-SPD BULK #1'!$G$19:$G$41))

Which I think is way off base. I'm new to multi-sheet functions so it's a bit over my head - I really appreciate your help - I hate to seem like I'm asking someone to make this sheet for me.. I've been trying for the past week to tie it up. I've attached the new sheet with the aforementioned table.

[Giustino]

Getting closer; bump