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(O FFSET(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

--
Message posted via http://www.officekb.com

Here's an array formula

=SUM(--(OFFSET(Results,0,3,MAX(INDEX((MONTH(OFFSET(Result s,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" <u4102@uwe wrote in message
news:6be6e996f8bc5@uwe...
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(O FFSET(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

--
Message posted via http://www.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(O FFSET(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 wrote:
Here's an array formula

=SUM(--(OFFSET(Results,0,3,MAX(INDEX((MONTH(OFFSET(Result s,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.

--
Message posted via http://www.officekb.com

Sam,

You just convert it wholesale to SUMPRODUCT.

=SUMPRODUCT(--(OFFSET(Results,0,3,MAX(INDEX((MONTH(OFFSET(Result s,0,1,,1))=MONTH($B26))*
(YEAR(OFFSET(Results,0,2,,1))=YEAR($C26))*(ROW(Res ults)),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)


"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:6be8311b92ea9@uwe...
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(O FFSET(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 wrote:
Here's an array formula

=SUM(--(OFFSET(Results,0,3,MAX(INDEX((MONTH(OFFSET(Result s,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.

--
Message posted via http://www.officekb.com

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.99 999999999999E+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.99 999999999999E+307,Sheet
1!$D$2:$D$65536))

Click Add

Name: Yr

Refers to:

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

Click Ok

Then, try the following...

E26:

=LARGE(IF(Mnth<"",IF(MONTH(Mnth)=MONTH($B26),IF(Y EAR(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!

In article <6be6e996f8bc5@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

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(O FFSET(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

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

Bob Phillips wrote:
Sam,

You just convert it wholesale to SUMPRODUCT.

=SUMPRODUCT(--(OFFSET(Results,0,3,MAX(INDEX((MONTH(OFFSET(Result s,0,1,,1))=MONTH($B26))*
(YEAR(OFFSET(Results,0,2,,1))=YEAR($C26))*(ROW(Re sults)),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 ...'?

--
Message posted via http://www.officekb.com

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" <u4102@uwe wrote in message
news:6beff761fdea3@uwe...
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

Bob Phillips wrote:
Sam,

You just convert it wholesale to SUMPRODUCT.

=SUMPRODUCT(--(OFFSET(Results,0,3,MAX(INDEX((MONTH(OFFSET(Result s,0,1,,1))=MONTH($B26))*
(YEAR(OFFSET(Results,0,2,,1))=YEAR($C26))*(ROW(R esults)),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 ...'?

--
Message posted via http://www.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

Bob Phillips wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1

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

Domenic wrote:
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.9 9999999999999E+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.9 9999999999999E+307,Sheet
1!$D$2:$D$65536))

Click Add

Name: Yr

Refers to:

=Sheet1!$D$2:INDEX(Sheet1!$D$2:$D$65536,MATCH(9.9 9999999999999E+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!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1

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

Bob Phillips wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1

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

--
Message posted via http://www.officekb.com