Thanks Max

is it possible to change the formula if my results worksheet has the date
consolidated. SO rather than Column A= yr, B = mth, c= day, Column a would be
formated as 1-Jan=10, 2-Jan-10, 3-Jan-10, etc...

Thanks

In a different worksheet called results (same workbook)

Column A represents the Year ( 2009/ 2010)
Column B represents the month of the year (format Jan thru Dec)
Column C represents the day of the month (ex: 1 through 31)

Row 4, columns D thru S represent the divisions


"Max" wrote:

Oops, errata, all source ranges need to be fixed with $ signs since the
formula will be copied across

If B5 down contains TEXT: Jan, Feb, etc (not real dates formatted to
display: mmm)
then place this in D5:
=SUMPRODUCT((x!$E$2:$E$10=D$4)*(x!$H$2:$H$10=$A5)* (x!$F$2:$F$10=$C5)*(TEXT(DATE(x!$H$2:$H$10,x!$G$2: $G$10,1),"mmm")=$B5)*(x!$N$2:$N$10=TRUE),x!$I$2:$I $10)

If B5 down contains real dates formatted to display as "mmm" (Jan, Feb, etc)
then use this in D5:
=SUMPRODUCT((x!$E$2:$E$10=D$4)*(x!$H$2:$H$10=$A5)* (x!$F$2:$F$10=$C5)*(TEXT(DATE(x!$H$2:$H$10,x!$G$2: $G$10,1),"mmm")=TEXT($B5,"mmm"))*(x!$N$2:$N$10=TRU E),x!$I$2:$I$10)

--
Max
Singapore
---