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