Sum with multiple conditions

[Curtis]

I have a source data sheet which has the following

Column E is the division
Column F represents the day of the month (ex: 1 thru 31)
Column G represents the month (ex 1 thru 12)
Column H represents the year (ex 2009 2010)
Column I represent s the sales revenue
Column N represents whether the sale went through (TRUE or FALSE)

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

I need a formula that will look into the source sheet and sum the sales
revenue by the above criteria ( division, year, month, day of month,
confirmed sale)

hope that makes sense

Thanks

 

[Max]

Lightly tested, this sumproduct rendition seems to do it ...

Assume your source data sheet is named: x
where all cols, especially cols F, G, H are fully populated from row2 down

In sheet: results,
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)
Copy D5 across/fill down to populate

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=TRUE),x!I$2:I$10)
Copy D5 across/fill down to populate
Modify the source ranges in the expression to suit the actual extents
Success? high five it, hit YES below

 

[Max]

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=TRUE),x!$I$2:$I$10)

 

[Curtis]

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)

 

[Max]

If A5 down contains real dates (eg 12 Nov 2009) -- the date format applied is
immaterial -- then this simpler rendition in D5 should work fine:
=SUMPRODUCT((x!$E$2:$E$10=D$4)*(DATE(x!$H$2:$H$10,x!$G$2:$G$10,x!$F$2:$F$10)=$A5)*(x!$N$2:$N$10=TRUE),x!$I$2:$I$10)
Copy D5 across to S5, fill down to populate

As mentioned in my 1st response, the source cols F, G, H (in x) containing
the day, month, year numbers should be fully populated throughout the range,
otherwise the formula will return #NUM! Success? celebrate it, hit YES below

 

[Curtis]

Thanks...This works however my source data cols F, G, H (in x) containing

the day, month, year numbers may not always have data in th respective range for all divisions. Anyway around this

Thanks

 

[Max]

As-is, no. You need to check/touch up the source data (in x) for completeness
(eg via autofilter).