Sumproduct for mutiple tabs

**djfatboyfats** · 02-06-2008, 06:28 AM

I need a formula for looking for a specific word in the same column on all tabs,i know how to do the formula for 1 tab

=SUMPRODUCT((FEB!$A$15:$A$1000=SUMMARY!A4)*(FEB!$F$15:$F$1000=FEB!$AF$3))

but over muliple tabs i have to repeat it for each tab. Example

=SUMPRODUCT((FEB!$A$15:$A$1000=SUMMARY!A6)*(FEB!$F$15:$F$1000=FEB!$AF$3))+SUMPRODUCT((MAR!$A$15:$A$1000=SUMMARY!A6)*(MAR!$F$15:$F$1000=FEB!$AF$3))+SUMPRODUCT((APR!$A$15:$A$1000=SUMMARY!A6)*(APR!$F$15:$F$1000=FEB!$AF$3))

The must be an easier way.

Thanks

DJ

**EdMac** · 02-06-2008, 07:49 AM

SUMPRODUCT(('Feb:April'!A15:A10000=Your value)*etc

Ed

**vane0326** · 02-06-2008, 10:33 AM

May we see a sample workbook?

**djfatboyfats** · 02-06-2008, 01:24 PM

Still not getting this to work does anyone have any ideas why?

=SUMPRODUCT(('Feb:[DEC]DEC'!A15:A10000=A4)*(FEB!F15:F1000="PPP"))

**vane0326** · 02-06-2008, 02:21 PM

List your worksheets names in cells B2:B4

Select B2

Insert > Name > Define

Name: wks

Refers to:

=TRANSPOSE(Sheet1!$B$2:$B$4)

Click Ok

Then try...

=SUMPRODUCT((T(OFFSET(INDIRECT("'"&wks&"'!A15:A1000"),ROW(INDIRECT("15:1000")),0))=A4)*(T(OFFSET(INDIRECT("'"&wks&"'!F15:F1000"),ROW(INDIRECT("15:1000")),0))=$AF$3))

If it doesn't work please post a sample workbook.