Prev: Desire Weekday returned in every 5th Cell
Next: How do I format cells in Excel to make 175/5 read 175?
From: Curtis on 21 Feb 2010 15:51
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

From: Max on 22 Feb 2010 02:13
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
Singapore
---
"Curtis" wrote:
> 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
>
From: Max on 22 Feb 2010 02:31
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)

--
Max
Singapore
---
From: Curtis on 22 Feb 2010 09:53
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=TRUE),x!$I$2:$I$10)
>
> --
> Max
> Singapore
> ---
From: Max on 22 Feb 2010 11:08
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
--
Max
Singapore
---
"Curtis" wrote:
> 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

 |  Next  |  Last
Pages: 1 2
Prev: Desire Weekday returned in every 5th Cell
Next: How do I format cells in Excel to make 175/5 read 175?