Dynamic range in sum formula with constraints

Question

I'd like to sum up some data within the current month but only until the current date.

The sheet looks something like this:

enter image description here

So there are 2 things to consider:

I'd like to not use the static formula =SUM(B32:B59) to sum up all values from february but rather have some kind of dynamic formula which automatically sets the ranges of the SUM to the first & last date of the month in which the sum formula "resides".
The end of the sum range should be constrained to not exceed the current date

Is this possible at all and if so, how?

Thanks

So you just want the sum up to where you have marked <<today, which would be 91? Do you get <<today using the today() function, i.e. it would be 17/04/2015 at time of writing, or is it a past date stored in the spreadsheet somewhere? — Tom Sharpe

Byron Wall Byron Wall · Accepted Answer · 2015-04-17T15:42:40

An array formula is an excellent way to combine criteria and a summary function. You can combine several criteria by multiplying their results together. The final result will be a 0 or 1 depending on whether or not the criteria were met. You can then take that result, multiply by the data and SUM.

Here is an example that applies your conditions to some sample date. This needs to be entered as an array formula with CTRL+SHIFT+ENTER. It also requires that your dates really be dates and not a string. Formula for cell D32. You can copy/paste this to each end of month row. It will give the same answer for any cell in the month though.

=SUM((MONTH($B$2:$B$37)=MONTH(B32))*($B$2:$B$37<TODAY())*$C$2:$C$37)

The image shows cell D17 expanded. I did this so that you can see that the results for April are correct.

image of sample data

The formula works with two conditions:

Check that the month is the same month as the current data point
Check that the date is less than current date

Dynamic range in sum formula with constraints

2 Answers