Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT - (amended) Exclude LAST Row of Matched Criteria (Month & Year)
Hi All,
I have 2 worksheets within the same workbook, Sheet1 and Sheet2. Sheet1 has a dynamic named range "Data" spanning 7 columns and many rows. Sample Data Layout: Sheet1 1st col Col "A" = Numeric Ref 2nd col Col "B" = Date - Full Date (01/07/2006) 3rd-7th col Col "C" - "G" = Numeric Values (spans 5 columns) Ref Date COL1 COL2 COL3 COL4 COL5 1 03/07/2006 0 4 2 4 4 2 06/07/2006 2 1 3 4 4 3 07/07/2006 2 4 3 0 1 4 11/07/2006 2 0 3 0 3 5 13/07/2006 1 0 3 0 4 6 14/07/2006 4 0 1 0 2 7 17/07/2006 1 0 1 3 2 8 18/07/2006 1 0 1 3 4 The Formula below was provided by Domenic to sum the count of the Numeric Values (eg: 0, 1, 2, 3 & 4) housed in columns "C" - "G" for a specific Month & Year (month & year criteria will change) and return the results to Sheet2. =SUMPRODUCT((OFFSET(Data,0,2,,5)=F$23)*(MONTH(OFFS ET(Data,0,1,,1)) =MONTH($B26))*(YEAR(OFFSET(Data,0,1,,1))=YEAR($C26 ))*(OFFSET(Data,0,1,, 1)<"")) This Formula would be entered in cell F26 of Sheet2, copied across and down. Sample Data Layout: Sheet2 The Month is a single column (01/07/2005) but is formatted as Month Custom Date mmm (Jul). The Year is a single column (01/07/2005) but is formatted as Year Custom Date yyyy (2006). 1st col Col "A", Row23 = Ref - Numeric Ref 2nd col Col "B" = Months - Full Date ( 01/07/2006), using custom date mmm (Jul) Month 3rd col Col "C" = Years - Full Date ( 01/07/2006), using custom date yyyy (2006) Year 4th col Col "D" = Misc. 5th col Col "E" = Misc. 6th-10th col Col "F" - "J", Row23 = Numeric Labels (criterion) 0, 1, 2, 3, & 4 6th-10th col Col "F" - "J", Row26 to many rows = Results: Numeric Values - summed count of numeric values from Sheet1 dynamic range "Data". Row "A" "B" "C" "D" "E" "F" "G" "H" "I" 23 Ref Month Year Misc Misc 0 1 2 3 24 - 25 - 26 135 Jan 2006 48 15 1 1 1 1 27 136 Feb 2006 48 15 1 0 1 1 28 137 Mar 2006 54 15 3 2 0 2 29 138 Apr 2006 54 13 2 1 1 0 30 139 May 2006 54 11 1 1 2 1 31 140 Jun 2006 48 21 0 0 0 2 32 141 Jul 2006 54 18 0 0 0 2 33 142 Aug 2006 54 12 0 1 1 2 34 143 Sep 2006 54 16 0 2 3 0 35 144 Oct 2006 48 19 0 1 0 2 36 145 Nov 2006 54 15 1 2 1 1 37 146 Dec 2006 48 18 0 1 0 2 NB: Row 25 & 26 have comments & text. Column "J" of Results Table could not fit on page without wrapping around. How can I amend Domenic's Formula above to EXCLUDE the very LAST "Data" Row for the Specific Month & Year on Sheet1? There may be 10 instances of Jul 2006 but the very LAST instance must be EXCLUDED from the calculations. Per my Sample Data in Sheet1, the row with date 18/07/2006 is the LAST row for July 2006 and should be excluded from the calculations. Is it possible to use a single SUMPRODUCT Formula to achieve the results? Thanks, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200701/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT - (amended) Exclude LAST Row of Matched Criteria (Month & Year)
Sam,
If the data is sorted by date, then adding in this term: ((MONTH(OFFSET(Data,0,1,,1))=(MONTH(OFFSET(Data,1, 1,,1)) will exclude the latest data point. So the whole formula becomes: =SUMPRODUCT((OFFSET(Data,0,2,,5)=F$25)*(MONTH(OFFS ET(Data,0,1,,1))=MONTH($B28))*((MONTH(OFFSET(Data, 0,1,,1))=(MONTH(OFFSET(Data,1,1,,1)))*(YEAR(OFFSET (Data,0,1,,1))=YEAR($C28))*(OFFSET(Data,0,1,, 1)<"")))) But since it is a count, why not just subtract 1? HTH, Bernie MS Excel MVP "Sam via OfficeKB.com" <u4102@uwe wrote in message news:6bfd0e8d8b6db@uwe... Hi All, I have 2 worksheets within the same workbook, Sheet1 and Sheet2. Sheet1 has a dynamic named range "Data" spanning 7 columns and many rows. Sample Data Layout: Sheet1 1st col Col "A" = Numeric Ref 2nd col Col "B" = Date - Full Date (01/07/2006) 3rd-7th col Col "C" - "G" = Numeric Values (spans 5 columns) Ref Date COL1 COL2 COL3 COL4 COL5 1 03/07/2006 0 4 2 4 4 2 06/07/2006 2 1 3 4 4 3 07/07/2006 2 4 3 0 1 4 11/07/2006 2 0 3 0 3 5 13/07/2006 1 0 3 0 4 6 14/07/2006 4 0 1 0 2 7 17/07/2006 1 0 1 3 2 8 18/07/2006 1 0 1 3 4 The Formula below was provided by Domenic to sum the count of the Numeric Values (eg: 0, 1, 2, 3 & 4) housed in columns "C" - "G" for a specific Month & Year (month & year criteria will change) and return the results to Sheet2. =SUMPRODUCT((OFFSET(Data,0,2,,5)=F$23)*(MONTH(OFFS ET(Data,0,1,,1)) =MONTH($B26))*(YEAR(OFFSET(Data,0,1,,1))=YEAR($C26 ))*(OFFSET(Data,0,1,, 1)<"")) This Formula would be entered in cell F26 of Sheet2, copied across and down. Sample Data Layout: Sheet2 The Month is a single column (01/07/2005) but is formatted as Month Custom Date mmm (Jul). The Year is a single column (01/07/2005) but is formatted as Year Custom Date yyyy (2006). 1st col Col "A", Row23 = Ref - Numeric Ref 2nd col Col "B" = Months - Full Date ( 01/07/2006), using custom date mmm (Jul) Month 3rd col Col "C" = Years - Full Date ( 01/07/2006), using custom date yyyy (2006) Year 4th col Col "D" = Misc. 5th col Col "E" = Misc. 6th-10th col Col "F" - "J", Row23 = Numeric Labels (criterion) 0, 1, 2, 3, & 4 6th-10th col Col "F" - "J", Row26 to many rows = Results: Numeric Values - summed count of numeric values from Sheet1 dynamic range "Data". Row "A" "B" "C" "D" "E" "F" "G" "H" "I" 23 Ref Month Year Misc Misc 0 1 2 3 24 - 25 - 26 135 Jan 2006 48 15 1 1 1 1 27 136 Feb 2006 48 15 1 0 1 1 28 137 Mar 2006 54 15 3 2 0 2 29 138 Apr 2006 54 13 2 1 1 0 30 139 May 2006 54 11 1 1 2 1 31 140 Jun 2006 48 21 0 0 0 2 32 141 Jul 2006 54 18 0 0 0 2 33 142 Aug 2006 54 12 0 1 1 2 34 143 Sep 2006 54 16 0 2 3 0 35 144 Oct 2006 48 19 0 1 0 2 36 145 Nov 2006 54 15 1 2 1 1 37 146 Dec 2006 48 18 0 1 0 2 NB: Row 25 & 26 have comments & text. Column "J" of Results Table could not fit on page without wrapping around. How can I amend Domenic's Formula above to EXCLUDE the very LAST "Data" Row for the Specific Month & Year on Sheet1? There may be 10 instances of Jul 2006 but the very LAST instance must be EXCLUDED from the calculations. Per my Sample Data in Sheet1, the row with date 18/07/2006 is the LAST row for July 2006 and should be excluded from the calculations. Is it possible to use a single SUMPRODUCT Formula to achieve the results? Thanks, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200701/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT - (amended) Exclude LAST Row of Matched Criteria (Month & Year)
Hi Bernie,
Thank you very much for your time and assistance. The data is sorted by date and your inclusion ((MONTH(OFFSET(Data,0,1,,1))= (MONTH(OFFSET(Data,1,1,,1)) has provided me with the required results. Great! Cheers, Sam Bernie Deitrick wrote: Sam, If the data is sorted by date, then adding in this term: ((MONTH(OFFSET(Data,0,1,,1))=(MONTH(OFFSET(Data,1 ,1,,1)) will exclude the latest data point. So the whole formula becomes: =SUMPRODUCT((OFFSET(Data,0,2,,5)=F$25)*(MONTH(OFF SET(Data,0,1,,1))=MONTH($B28))*((MONTH(OFFSET(Data ,0,1,,1))=(MONTH(OFFSET(Data,1,1,,1)))*(YEAR(OFFSE T(Data,0,1,,1))=YEAR($C28))*(OFFSET(Data,0,1,, 1)<"")))) But since it is a count, why not just subtract 1? HTH, Bernie MS Excel MVP -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200701/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multple criteria dilemma | Excel Worksheet Functions | |||
Lookup returns wrong value | Excel Worksheet Functions | |||
Month Year Date Format | Excel Worksheet Functions | |||
How to sort by day and month while ignoring year? | Excel Worksheet Functions | |||
Insert Month and Year in my worksheet | Excel Worksheet Functions |