Help with finding specific number and returning the right sum

[Hybride]

You'll have to excuse me, I'm not quite sure how to word this. I think the formula I'm thinking of is either HLOOKUP or VLOOKUP.

Basically, I have multiple worksheets in one Excel document, I want a formula the searches each different worksheet, checks to see if there's an ID code (which I'll specify) in the worksheet and return the sum of products sold to the "main" worksheet.

I hope that makes sense! I've attached the Excel document as an attachment if you need to take a look at what I mean.

Thanks for any help in advance!

[NBVC]

Your worksheet came up with errors and produced blank sheets.. but essentially you will need to do either one of these:

1. Use SUMIF on each sheet to sum each sheet based on condition and put the result in the same cell of each sheet.

e.g. =SUMIF(A1:A100,"X",B1:B100) this would be applied to each sheet and says to sum all values in column B1:B100 where A1:A100 = "X" (without quotes).

Then in your summary tab, you use formula like: =SUM('Sheet A:Sheet X'!X1) where Sheet A:Sheet X is the range of sheets and X1 is the cell in each sheet containing the individual SUMIF formulas.

2. List the names of all the sheets in your Summary tab somewhere then apply formula like:

=SUMPRODUCT(SUMIF(INDIRECT("'"&X1:X10&"'!A1:A100"),"X",INDIRECT("'"&X1:X10&"'!B1:B100")))

where X1:X10 contains the names of the sheets and like before B1:B100 on each sheet would be summed based on A1:A100 on each sheet containing an "X"


Note: Option 1 is more efficient as it doesn't use SUMPRODUCT (which is more resource intensive) and it doesn't include volatile functions like INDIRECT (which recalculate with an direct or indirect change you make in your sheet).

[Hybride]

I know this must have been a really easy and obvious question for you to answer but in my eyes you are a genius. Thank you so much, you've just made my life a lot easier