Forum Discussion
Cumulative Totals and SumProduct Formulas
Needing a few more tweaks to my formulas. We are changing a cumulative log into a monthly log. I am needing to calculate the total items in C19:c233 and divide it by counts in J19:233, for the month that the dates in column b fall into. But show it in the Monthly count area of C3 through c14. The way it is set up now only adds the counts of J. I also need to calculate the monthly % in D3:d14, which I think the formula I have enter will work once I correct the c3:c14 counts. Then how do I go about calculating a YTD Count and YTD % in E3:e14? I have attach my worksheet to show what I have so far.
Hi Karen,
The main difficulty is the calculation with the months in row B. Instead of the "January", you should use 01/01/2018 and then format this cell as "mmmm".
Afterwards, it will be easier to work with MONTH().
Attached a suggestion for your file.
If you have any comments, please do not hesitate.
Kr
TK
Thank you for the information. I have added another column to help with the counts and they are working. I was told to add YTD totals columns but unsure of the formula to calculate them. The monthly % is working. But the YTD % is not. Any Suggestions?
- Hi Karen,
In cell D3, add the formula =SUM($C$3:C3) and copy it in the rest of the column.
In cell F3, use the formula =SUM($E$3:E3) and copy it in the rest of the column.
And in column H (YTD % Total), use the formula =F3/D3.
I suppose this is the simplest way to do it.
Kr
TK
