Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum with multiple conditions
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum with multiple conditions
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$1 0) 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum with multiple conditions
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 --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum with multiple conditions
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 --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum with multiple conditions
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum with multiple conditions
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" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum with multiple conditions
As-is, no. You need to check/touch up the source data (in x) for completeness
(eg via autofilter). -- Max Singapore --- "Curtis" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting multiple conditions to return a figure from multiple cells | Excel Discussion (Misc queries) | |||
SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple rec | Excel Discussion (Misc queries) | |||
How do I add multiple values that match multiple conditions? | Excel Discussion (Misc queries) | |||
Combining Text from multiple cells under multiple conditions | Excel Worksheet Functions | |||
How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions |