CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formulaspreadsheet
mcclosa
mcclosa

Reputation: 1445

Increment sum range by 1 based on day of month

not entirely sure how to word this but I shall give it my best attempt.

This is the general structure of my spreadsheet.

  | A   |B      |
-----------------
1 | Jan |       |
2 | 1   | £3.00 |
3 | 2   | £4.00 |
4 | 3   | £5.00 |
5 | 4   | £0.00 |
6 | 5   | £1.00 |
..| ... | ..... |

I want to do a sum function based upon the day of the month. The A Column includes the day of the month. I want to check the current day of the month, then match it to the value in the column A, then sum from B2 until the B value adjacent to the current day of the month.

For example, if it's the 3rd of the month I want the formula to be B2:B4. Wasn't sure if I could do something like increment the day by 1, so for the 3rd day of the month the value would be 4 which could correspond to the cell at the end of the range B4.

Initially, I had this, but to do this for every day of every month seems a little verbose and wasn't sure if there is a more efficient option? 

=IF(DAY(TODAY()) = 1, SUM(B2), 
IF(DAY(TODAY()) = 2, SUM(B2:B3), 
IF(DAY(TODAY()) = 3, SUM(B2:B4), 
IF(DAY(TODAY()) = 4, SUM(B2:B5), 
IF(DAY(TODAY()) = 5, SUM(B2:B6), 0)))))

Any help would be greatly appreciated.

Upvotes: 2

Views: 413

Answers (3)

Forward Ed
Forward Ed

Reputation: 9874

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

Alternative format to get the range. It assumes a maximum day of 31.

Upvotes: 1

jsheeran
jsheeran

Reputation: 3037

You can use the value of DAY(TODAY()) as the height parameter of OFFSET to achieve this:

=SUM(OFFSET(B2,0,0,DAY(TODAY()),1)

Upvotes: 2

Harassed Dad
Harassed Dad

Reputation: 4704

=SUM(INDIRECT("B2:B" & MATCH(DAY(TODAY()),A2:A100)))

The match gives you the row for today, then we concatenate it into an address and then the indirect passes that to the sum function

Upvotes: 1

Related Questions