SUMPRODUCT - (amended) Exclude LAST Row of Matched Criteria (Month & Year)

[Sam via OfficeKB.com]

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(OFFSET(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

 

[Bernie Deitrick]

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(OFFSET(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]

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

Sam,

If the data is sorted by date, then adding in this term:

will exclude the latest data point.

So the whole formula becomes:
=SUMPRODUCT((OFFSET(Data,0,2,,5)=F$25)*(MONTH(OFFSET(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)<>""))))