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

[Sam via OfficeKB.com]

Hi All,

I'm using this Formula provided by Domenic to sum the count of the Numeric
Value housed in
cell F23 for a Specific Month & Year (month & year criteria will change).

=SUMPRODUCT((OFFSET(Results,0,3,,5)=F$23)*(MONTH(OFFSET(Results,0,1,,1))
=MONTH($B26))*(YEAR(OFFSET(Results,0,2,,1))=YEAR($C26))*(OFFSET(Results,0,1,,
1)<>""))

"Results" is a Dynamic Range spanning 8 Columns and many Rows.
The Numeric Value is checked from the 4th to the 8th Column (spans 5 columns).

The Month is a single column (01/07/2005) but is formatted as Custom Date mmm
(Jul) located in the 2nd column of the Dynamic Range.
The Year is a single column (01/07/2005) but is formatted as Custom Date yyyy
(2005) located in the 3rd column of the Dynamic Range.

How can I amend the Formula above to EXCLUDE the very LAST "Results" Row (of
data) for the Specific Month & Year? There may be 10 instances of Jul 2005
but the very LAST instance must be EXCLUDED from the calculations.

Thanks,
Sam

 

[Bob Phillips]

Here's an array formula

=SUM(--(OFFSET(Results,0,3,MAX(INDEX((MONTH(OFFSET(Results,0,1,,1))=MONTH($B26))*(YEAR(OFFSET(Results,0,2,,1))=YEAR($C26))*(ROW(Results)),0)-MIN(ROW(Results))),5)=$F$23))

as an array formula, commit it with Ctrl-Shift-Enter, not just Enter.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Sam via OfficeKB.com]

Hi Bob,

Thanks for reply. I've entered the array Formula (with Ctrl-Shift-Enter) but
it does not provide the correct results. It appears to be incrementing the
cell value row by row.

Is it possible to have the original Formula using SUMPRODUCT but EXCLUDE the
very LAST "Results" Row (of data) for the Specific Month & Year?

Original Formula:
=SUMPRODUCT((OFFSET(Results,0,3,,5)=F$23)*(MONTH(OFFSET(Results,0,1,,1))
=MONTH($B26))*(YEAR(OFFSET(Results,0,2,,1))=YEAR($C26))*(OFFSET(Results,0,1,,
1)<>""))


Further assistance most appreciated.

Cheers,
Sam

 

[Bob Phillips]

Sam,

You just convert it wholesale to SUMPRODUCT.

=SUMPRODUCT(--(OFFSET(Results,0,3,MAX(INDEX((MONTH(OFFSET(Results,0,1,,1))=MONTH($B26))*
(YEAR(OFFSET(Results,0,2,,1))=YEAR($C26))*(ROW(Results)),0)-MIN(ROW(Results))),5)=$F$23))

I just developed it as an array formula (as I originally included an IF),
but they work the same.

My testing works as I understand the spec, I created a Results range of
A1:H20, filled D1:H20 with random values, filled B1:B20 and C1:C20 with
dates,and F23 with 11. I put 01/06/2006 in B26 and C26. In my data, June was
in rows 9, 10 and 12, and row 9 had a single 11, row 10 and two 11s, and row
12 had 1. Domenic's formula returned 4 in my test, and mine (and the
SUMPRODUCT version) returns 3, i.e. it ignores row 12. This is what you
wanted isn't it?

But what do you mean by '...It appears to be incrementing the cell value row
by row ...'?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Domenic]

For simplicity, let's assume the following...

Sheet1 contains the data

Data starts on Row 2

Column C contains your first date, which is evaluated for the month

Column D contains your second date, which is evaluated for the year

Column E through Column I contains the remainder of your data

Sheet2 contains the results

Define the following...

Insert > Name > Define

Name: Data

Refers to:

=Sheet1!$E$2:INDEX(Sheet1!$E$2:$I$65536,MATCH(9.99999999999999E+307,Sheet
1!$D$2:$D$65536),0)

Click Add

Name: Mnth

Refers to:

=Sheet1!$C$2:INDEX(Sheet1!$C$2:$C$65536,MATCH(9.99999999999999E+307,Sheet
1!$D$2:$D$65536))

Click Add

Name: Yr

Refers to:

=Sheet1!$D$2:INDEX(Sheet1!$D$2:$D$65536,MATCH(9.99999999999999E+307,Sheet
1!$D$2:$D$65536))

Click Ok

Then, try the following...

E26:

=LARGE(IF(Mnth<>"",IF(MONTH(Mnth)=MONTH($B26),IF(YEAR(Yr)=YEAR($C26),ROW(
Yr)-MIN(ROW(Yr))+1))),1)

....confirmed with CONTROL+SHIFT+ENTER

F26:

=SUM(IF(ROW(Mnth)<$E26,IF(Mnth<>"",IF(MONTH(Mnth)=MONTH($B26),IF(YEAR(Yr)
=YEAR($C26),IF(Data=F$23,1))))))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Bob,

Thank you very much for your reply.

Your interpretation is close.

SUMPRODUCT version) returns 3, i.e. it ignores row 12. This is what you
wanted isn't it?

In an attempt to be brief - I omitted:
The dates in B1:B20 & C1:C20 are in ascending order.
For example; I require the exclusion of the row with the LAST instance of the
month June, whether or not a specific number is in that row. The row
EXCLUSION should be based soley on the LAST instance of the particular Month
in question per cells B26 & C26 searching B1:B20 & C1:C20 "Results" range,
and NOT based on excluding the last row with the Numeric Value that cell F23
holds (eg; 11).

I hope this helps.

Cheers,
Sam

Sam,

You just convert it wholesale to SUMPRODUCT.

I just developed it as an array formula (as I originally included an IF),
but they work the same.

 

[Bob Phillips]

That is what that formula does, it ignores the last row for the date. If the
last row has no 11s I get the same result as Domenic got, if it contains 11s
these are ignored.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Sam via OfficeKB.com]

Hi Bob,

The results that you get is actually what I require. I've been revamping the
worksheet bit by bit. Something may have gone awry on my sheet that holds the
data and the one I'm returning the results to. I need to start from scratch
and post back.

Thank you for all your help thus far.

Cheers,
Sam

 

[Sam via OfficeKB.com]

Hi Domenic,

Thanks very much for reply and assistance. I've been trying to get Bob's
version of the Formula to work on my worksheet. I made some changes to the
worksheets which is probably causing the problem!

I haven't tried your solution as yet. I need to sort a few things out and
then post back.

Cheers,
Sam

For simplicity, let's assume the following...

Sheet1 contains the data

Data starts on Row 2

Column C contains your first date, which is evaluated for the month

Column D contains your second date, which is evaluated for the year

Column E through Column I contains the remainder of your data

Sheet2 contains the results

Define the following...

Insert > Name > Define

Name: Data

Refers to:

Click Add

Name: Mnth

Refers to:

Click Add

Name: Yr

Refers to:

Click Ok

 

[Sam via OfficeKB.com]

Hi Bob,

I've posted a new thread "SUMPRODUCT - (amended) Exclude LAST Row of Matched
Criteria (Month & Year)". Hope you can have a look. Apologies for my
confusion in the original post.

Cheers,
Sam

 

[Sam via OfficeKB.com]

Hi Domenic,

I've posted a new Thread "SUMPRODUCT - (amended) Exclude LAST Row of Matched
Criteria (Month & Year)". Hope you can have a look. Apologies for my
confusion in the original post.

Cheers,
Sam