Looking for a Formula that would do a Lookup and then Sum the numbers.

Hi,

The attached workbook has 3 sheet showing the monthly sale of fruits with fruit name in column A and Fruit Qty in column B and one Main sheet that totals the month wise quantity. Currently I am using the following formula to get the sum total =SUM('1:3'!B2) however I am searching for a formula that would first lookup the item name from main sheet with the other sheets and give me the sum total of the Qty next to it.

I have attached a sample spreadsheet which might be of help. Any suggestions would be greatly helpful.

Regs.

If you'd still like a formula approach with some kind of lookup this file has drop downs in column A of the Main sheet.

The formula that references them is in B2:B6.

Formula:

Please Login or Register  to view this content.

hi,

Check this

Punnam

Sum_Lookup.xlsx

take a look at this spreadsheet i added 2 types of solutions based on how your info will look

Thank you all for your awesome and quick suggestions.

MarvinP: Using consolidated ranged Pivot is a very neat idea but I cannot apply it to my workbook as I would need to remove one or more fruits from the main sheet. Although I am surely gonna use this option for my other piece. Thank you very much for the details information. The video really came handy. +1 rep for you.

FlameRetired & daveisalwayshere: I went through your formula and although it took me some time to link the dots (since I am still don't understand the sumproduct formula) the result that the formula provided was pretty impressive. Its just that the formula would get pretty lengthy if added more sheets to it. Thank you for your suggestion though.

Punnam: This is the 1st time I have seen a formula that has INDIRECT, SUMIF & the difficult to understand SUMPRODUCT all at once. And you have created a masterpiece. Your formula is very much flexible and fits perfect per my requirement. I will apply this to my actual main sheet "hope I'm able to" Thanxx a ton... +1 rep for you.