Find a sheet based on cell and then sumif or sumproduct

**jimstrongy** · 02-09-2012, 03:10 PM

I need a formula that will look at sheet1 column A and based on that cell find the coresponding sheet in the same workbook. When sheet is located I need sum if or sumproduct formula that will look at the column C and find all "sales" and then go to column E and subtotal all amounts that have " sales" in column C. This formula will be placed on sheet 1 column B

Thank you

**Fotis1991** · 02-09-2012, 03:15 PM

Would you like to upload a sample workbook for this???

**bebo021999** · 02-09-2012, 03:18 PM

Hope this help:
In B1:

Please Login or Register  to view this content.

Joking
:You look like a tourist guide!

**jimstrongy** · 02-09-2012, 04:33 PM

Hi Bebo,
Formula works perfect,
What about adding additional item to be sumed for eaxmple "sales" and 2nd item "stores". Can that be added to this formula or I need to copy paste same formula and change tha name from sales to stores?

**bebo021999** · 02-09-2012, 10:25 PM

=SUMPRODUCT(((INDIRECT(A1&"!C:C")="sales")+(INDIRECT(A1&"!C:C")="stores"))*INDIRECT(A1&"!E:E"))
Hope this help!

**jimstrongy** · 02-09-2012, 11:47 PM

Originally Posted by bebo021999

=SUMPRODUCT(((INDIRECT(A1&"!C:C")="sales")+(INDIRECT(A1&"!C:C")="stores"))*INDIRECT(A1&"!E:E"))
Hope this help!

Thanks that helps a lot unfortunately I'm running into a bigger problem where my tab names are actually dates for example(10-1-10). And I'm having a hard time formating my cells to pick up tabs and sum the data.
Any suggestions how to go around this problem??

**bebo021999** · 02-10-2012, 12:06 AM

Try this in any cell you want:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
to pick up your current tab name automatically.
If it doesnt help, try to upload an example file to get it solved soonest.