Forum Discussion
Sumifs cumulative to current month, where dates are grouped
Hi
I'm using '=SUMIFS(B:B,A:A,"<="&EOMONTH(H1,0)) - where H1 = TODAY(), to capture the cumulative balance to date, but this stops working when I group the dates by month.
I also want to add in the argument where it should only sum column B when the header contains a specific word (company name).
I've tried so many options! If anyone can help please.
Below is a sample of the pivot. I want to link back to this and show where company name is Amazon, return the cumulative balances to current month, expected answer is €50,500 for balances up to 31-Jan-18
| Sum of Balance | Column Labels | |
| Row Labels | Amazon | |
| 2015 | ||
| Jan | 1000 | |
| Feb | 4000 | 3000 |
| 2016 | ||
| Mar | 3000 | 4000 |
| Apr | 4000 | 5000 |
| 2017 | ||
| Jun | 6000 | 7000 |
| Jul | 7000 | 7250 |
| 2018 | ||
| Jan | 25500 | 9500 |
| May | 5000 | 6000 |
| Aug | 8000 | 8900 |
1 Reply
Hi Clara,
If calculate on source table better to use SUMPRODUCT.
If in Pivot Table when in Value Field Settings select Show Value As "Running Total In" with proper option.
If you group the dates Running total will be shown for each period and starts over for every new period. If you need running total for entire history when ungroup the dates.
