sumproduct two criteria across 12 sheets

**alphobic** · 04-20-2011, 03:58 AM

=SUMPRODUCT((JAN!A3:A1003="Winstanley")*(JAN!I3:I1003="i"))+SUMPRODUCT((FEB!A3:A1003="Winstanley")*(FEB!I3:I1003="i"))+SUMPRODUCT((MAR!A3:A1003="Winstanley")*(MAR!I3:I1003="i"))+SUMPRODUCT((APR!A3:A1003="Winstanley")*(APR!I3:I1003="i"))+SUMPRODUCT((MAY!A3:A1003="Winstanley")*(MAY!I3:I1003="i"))+SUMPRODUCT((JUN!A3:A1003="Winstanley")*(JUN!I3:I1003="i"))+SUMPRODUCT((JUL!A3:A1003="Winstanley")*(JUL!I3:I1003="i"))+SUMPRODUCT((AUG!A3:A1003="Winstanley")*(AUG!I3:I1003="i"))+SUMPRODUCT((SEP!A3:A1003="Winstanley")*(SEP!I3:I1003="i"))+SUMPRODUCT((OCT!A3:A1003="Winstanley")*(OCT!I3:I1003="i"))+SUMPRODUCT((NOV!A3:A1003="Winstanley")*(NOV!I3:I1003="i"))+SUMPRODUCT((DEC!A3:A1003="Winstanley")*(DEC!I3:I1003="i"))

How can I simplify the above?

**Domski** · 04-20-2011, 04:36 AM

Keep all your data on one worksheet and add a column to show the date it relates to. You will find any analysis that you do far easier.

Spreading similar data across more than one worksheet is just making things difficult for yourself unless it won't fit.

Dom

**alphobic** · 04-20-2011, 04:54 AM

Thanks Domski, However each sheet has a large amount of data for each month of the year and this is only a small part of data analysis which is on a thirteenth sheet.

**Domski** · 04-20-2011, 05:00 AM

More than would fit in 1 million+ rows if combined?

Dom

**Cutter** · 04-20-2011, 10:33 AM

You could do the SUMPRODUCT()'s (which BTW should be changed to COUNTIFS() if you're using XL 2010 as per your profile) on each monthly sheet in a discrete cell and then do a 3D sum of that cell on those sheets. Like

=SUM(JAN:DEC!Z1)

that will sum the 12 Z1 cells from sheet JAN to sheet DEC (that assumes no sheets are between those 2 named sheets other than the other 10 monthly sheets).

**alphobic** · 04-20-2011, 01:27 PM

Thanks for suggestion and time given. The sumproduct example give uses a letter and code and ward name and I still wonder if it is possible to have to criteria and sumproduct across multiple sheets. each sheet is identical in layout except for month and variation of data.