Sum cells based on current Month

[nicko54]

Hi all,

I am trying to sum up the month to date of values in cells.

I need to find a way to look up yesterdays date and sum values in a column from the start of the month to yesterdays date.

So for this month it would be sum 1 Nov to 3 Nov (if today is the 4th Nov)

I have attached an example to give a bit more of a guide.

Any ideas would be much appreciated

[Pete_UK]

You've already got something very similar in E4.

Yesterday is given by TODAY()-1, and the first day in yesterday's month is:

DATE(YEAR(TODAY()-1),MONTH(TODAY()-1),1)

Perhaps you can incorporate those in a modified SUMIFS formula.

Hope this helps.

Pete

[nicko54]

Thanks Pete,

I had help putting together that formula (E4) so am not quite sure how to adjust it.

I have just attached an attempt but it looks like it is coming out higher when I manually count up the numbers.

Any obvious mistakes I have made?

[Pete_UK]

I think you need this in G4:

=SUMIFS('Budget KPIs'!$C$3:$C$370,'Budget KPIs'!$A$3:$A$370,">="&DATE(YEAR(TODAY()-1),MONTH(TODAY()-1),1),'Budget KPIs'!$A$3:$A$370,"<="&TODAY()-1)

or, if you want to refer to yesterday's date in E1:

=SUMIFS('Budget KPIs'!$C$3:$C$370,'Budget KPIs'!$A$3:$A$370,">="&DATE(YEAR($E$1),MONTH($E$1),1),'Budget KPIs'!$A$3:$A$370,"<="&$E$1)

(slightly shorter). Note that if today were 1st of the month this would give you last month's total.

Hope this helps.

Pete

P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[nicko54]

brilliant works a treat. Thanks heaps Pete.

[Pete_UK]

Hi Pete,

Thanks for your help with this.

I want to add in a Year To Date column (Starting from the 1st July to yesterday)

Is it possible to manipulate the Month to date formula to capture Year to date?

You asked the above in a Visitor Message - you can do this:

=SUMIFS('Budget KPIs'!$C$3:$C$370,'Budget KPIs'!$A$3:$A$370,">="&DATE(2013,7,1),'Budget KPIs'!$A$3:$A$370,"<="&$E$1)

Hope this helps.

Pete

[nicko54]

brilliant thanks Pete! works great.