VLOOKUP against two tabs that sums the results

I'm currently working on a stock spreadsheet, we get our weekly sales from two separate channels that then need to be combined and summed into one cell next to each product title. The weekly stock figures will not always contain all product titles that appear on the main stock sheet.

How can I in one formula lookup against two tabs (each with each set of sales) for the product title and combine the sales figures?

Any help appreciated!

Thanks

Maybe something like:
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheet1:Sheet2&"'!A1:A100"),A1,INDIRECT("'"&Sheet1:Sheet2&"'!B1:B100")))

Red: Your sheet names
Green: Product range
Blue: Sales figures range
Purple: the product that you are looking for

Hi Paul,

Thanks for your reply, I've tried putting the formula in but it just comes up with #N/A 'Argument must be a range', how can I get around this?

Thanks

Can you please upload a small sample file with the expected output? To upload a file, Go advanced, Mange attachments, Upload file. Thanks.

Hi Paul,

Sample file attached

Thanks for your help, unfortunately it's not adding the sales from NOTHS tab, only taking from the ConC tab. Can anyone help?

Thanks

Please see updated sheet attached, I have put in sales for the first product '12 Days Of Christmas Chocolate Selection' onto ConC & NOTHS tabs as an example but it's only taking from the ConC tab?

Hi,

I'm still only getting 8 with this formula, please see screenshot attached

Hi, I am using google sheets as I don't have excel

This one works, thank you for your help!