OFFSET,COUNT formula for current month?

[Big Lar]

Sheet1 is a datasheet with Column Headers in Row1 and values in Columns A2:AG<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Column A is Date mm/dd/yy
<o> </o>
<o> </o>
This formula in Sheet2 provides the last row values in a report (“Today’s Totals)<o></o>
=OFFSET(Sheet1!T1,COUNT(Sheet1!T:T),0)+OFFSET(Sheet1!U1,COUNT(Sheet1!U:U),0)/4<o></o>
<o> </o>
This formula in Sheet2 provides the column totals in a report (“Year to Date”)<o></o>
=SUM(Sheet1!T:T)+SUM(Sheet1!U:U)/4<o></o>
<o> </o>
Is there a formula that will return the SUM of column values for the current month for a “Month to Date” report?<o></o>
i.e. If the last value in Column A is 05/21/11, COUNT(Sheet1!T:T) for May dates only?<o></o>

 

[Scott R]

Rather than OFFSET(), I'd go with INDEX().

Today's total formulas would be:
=INDEX(T:T,COUNT(T:T))
=INDEX(U:U,COUNT(U:U))/4

I'd put the latest entry date in a cell somewhere (I put it in Y1):
=INDEX(A:A,COUNT(A:A))

Then you can use a variation of the following to provide May totals (latest entry month):
=SUMIFS(T:T,$A:$A,">="&EOMONTH($Y$1,-1)+1,$A:$A,"<=" & $Y$1)
=SUMIFS(U:U,$A:$A,">="&EOMONTH($Y$1,-1)+1,$A:$A,"<=" & $Y$1)/4

 

[Big Lar]

Scott,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks for your advice and suggestions. However,<o></o>
<o></o>
=INDEX(T:T,COUNT(T:T)) returns the next to last row of data. Deleting the worksheet’s header row resolves this issue…but I really need the header row.<o></o>
<o></o>
I put =INDEX(A:A,COUNT(A:A)) in cell AL1 and<o></o>
<o></o>
=SUMIFS(T:T,$A:$A,">="&EOMONTH($<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:State w:st="on">AL</st1:State>$1,-1)+1,$A:$A,"<=" & $<st1:State w:st="on"><st1lace w:st="on">AL</st1lace></st1:State>$1) in cell AM1<o></o>
<o></o>
Result equals #NAME?<o></o>
<o></o>
I changed the format of Column A and cell AL1 from Date “mm/dd/yy” to General…no change in the result.<o></o>
<o></o>
Any suggestions?<o></o>

 

[Scott R]

You should be ok w/this in AL1:
=INDEX(A:A,COUNT(A:A)+1)

 

[Big Lar]

Hummm...

Still results in #NAME?

Is date format the issue?(grasping at straws)

 

[Scott R]

Did AL1 formula return the last date in Column A?

 

[Big Lar]

Yes

 

[Scott R]

SUMIFS is available with Excel 2007 and later. Are you using an earlier version?

 

[Big Lar]

2003

 

[Scott R]

Put this in AM1:
=SUMIF($A:$A,">=" & DATE(YEAR($AL$1),MONTH($AL$1),1),T:T)