Sum with multiple conditions

  • Thread starter Thread starter Curtis
  • Start date Start date
C

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
 
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
 
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)
 
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)
 
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
 
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
Click to expand...

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