Sumif formula multiple sheets

Hi
Hope someone can help me.
I want to search through multiple sheets to find a value that is associated with that search item and add them all up.
Example:
As attached, I have multiple tabs, each one corresponding to a customer, inside that tab is the material used for that specific customer. What i want to do is for each line in the Purchased tab, i want to search each customer for that specific line and add up all the numbers in the QTY column that relates to that item, and have a final figure in the used column in the PURCHASED tab.
Not sure i am explaining this very well.



TEST.xlsx

Hello,
Please check attached sheet... Lets tackle step by step.. first let us know Do you want to take a total of col. c of all sheets with criteria??

Hi Mangesh
Please delete all the numbers in col d in the purchased tab, sorry i forgot to do it.

So i want to have a total across all sheets in that column, so effectively i want excel to take that part# ie. TPS2CE7050, and find it in each tab, then look at column A in that tab and add them altogether to get a final USED amount, so for the first part number it will find it twice in tab A and the QTY (col A) is 140 (A4 - 60, A27 - 80)
Then in tab B it appears 3 times (B54, B69, B91) for a total of 215 plus the 140 from TAB A, running total of 355 and so on untill all tabs are checked, does that make it clearer?

So i guess that equates to i want to take a total of col. A with criteria and the criteria is col. B

Actually what you want is in attached sheet but problem with your sheet is you want to take a total of left side col...
Is it possible to shift col A to the right side in all sheet??

So that cannot be done then?
As you can see it is a vast sheet, it would be very inconvenient to change the location of the col, if i can avoid it i would prefer not to move it.

not impossible but hard..

It is combination of three formulae.. Sumproduct formula is generally used to take sum of products of specified ranges but there is only single array so it took only summation.. You know the sumif formula.. Indirect function is used to specify indirect reference for different sheets i.e. you can specify sheet names by giving Range in same sheet..

Finally Done What you want... Please check some items randomly coz this is first array formula have created in my life...

Also don`t copy this formula through out the cells just drag the formula from cell.. If you don`t know how to drag then revert...

Please note that the name given to this thread is not proper as per forum rule no. 1 change it to "Sumif formula to left columns accross multiple sheet" so that other people get benefit..

This is array formula.. required Ctrl+Shift+Enter

Formula:

Please Login or Register  to view this content.

Using SUMIF would be more efficient than SUMPRODUCT.

regular formula:

=SUMIF(INDIRECT("'"&C$3&"'!$C$2:$C$5000"),$A4,INDIRECT("'"&C$3&"'!$A$2:$A$5000"))

Originally Posted by AlKey

=SUMIF(INDIRECT("'"&C$3&"'!$C$2:$C$5000"),$A4,INDIRECT("'"&C$3&"'!$A$2:$A$5000"))

Quoted range references will automatically be evaluated as absolute references so there's no need to include the dollar signs.

=SUMIF(INDIRECT("'"&C$3&"'!C2:C5000"),$A4,INDIRECT("'"&C$3&"'!A2:A5000"))

Originally Posted by Tony Valko

Quoted range references will automatically be evaluated as absolute references so there's no need to include the dollar signs.

=SUMIF(INDIRECT("'"&C$3&"'!C2:C5000"),$A4,INDIRECT("'"&C$3&"'!A2:A5000"))

I know. I just copied the range from the sumproduct formula and put into SUMIF.

Agree .... Actually I was trying to create a formula which will work across multiple sheets but it is working only with single sheet otherwise showing #VALUE! error so I have just paste this here... Here is attached sheet if anybody could solve this.. marked it yellow..

Thanks for reputation point