SUM (TODAY thru EOM)

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

Excel2003 ...

Range A2:A32 contains Days of Month (1-31)
Range B2:B32 contains Values

Cell B33 ... I wish to SUM Col B Values from TODAY thru End of Month.

ie: If TODAY is 04/18 ... Sum B Values 04/18 thru EOM
ie: If TODAY is 04/19 ... Sum B Values 04/19 thru EOM

Thanks ... Kha
 
If that is how your data will always be laid out then try:

=SUM(INDEX(B2:B32,DAY(TODAY())):B32)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Ken,

I'm assuming you have dates in A1 - A32 so tru this

=SUM(IF(DAY(A2:A32)>=DAY(TODAY()),B2:B32,0))
It's an array so commit with Ctrl+Shift+Enter.

Mike
 
Sandy ... (Good morning)

Exactly as requested ... & I will be using ... but one more twist.

Should Col A Date not start with 1st of Month (1-31) & I need to INDEX Col A
into this equation with remaining requirements the same ... How do I do this?

ie:

Find TODAY in Col A ... SUM Col B Range from associated Value in Col B to EOM

Thanks ... Kha
 
If by:
Should Col A Date not start with 1st of Month (1-31)
Click to expand...

you mean that some of the cells in the start of the range A2:A32 are blank
then the formula given will still work because it is not actually using any
of the dates in Column A.

If you mean that the date in A2 may not be the 1st of the month then try:

=SUM(INDEX(B2:B32,MATCH((TODAY()),A2:A32)):B32)

This assumes that the cell from the end of your data to row 32 are empty, if
not, change the A32 & B32 to the ends of your data.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Dead Nuts ... Perfect ... :)

=SUM(INDEX(B2:B32,MATCH((TODAY()),A2:A32)):B32)

I am always grateful for those who provide the many solutions on these boards.

Thank you ... Kha
 
Glad that it worked for you. Thanks for the feedback.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top