Forum Discussion

Copper Contributor

Mar 01, 2018

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 ...

Copper Contributor

Mar 01, 2018

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.

Brand.xlsx28 KB

Karen Heinrich

Copper Contributor

Mar 01, 2018

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?

Brand.xlsx29 KB

Tinn Keeper
Brass Contributor
Mar 05, 2018
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
- Karen Heinrich
  Copper Contributor
  Mar 05, 2018
  Thank you for the information. I am using the following formula in a different worksheet =IF(C3=0,"",SUM(C$3:C3)) for YTD totals But I still need to have it show zeros if that is what the total is for that cell. Is that possible?
  - Tinn Keeper
    Brass Contributor
    Mar 05, 2018
    Just replace the "" by zero ;)
    =IF(C3=0,0,SUM(C$3:C3))