Accumulative Formula required for daily cadence?

chris.italiano78351

Good Morning

Hope someone can help me. I want to keep track of the accumulative total for each spreadsheet.

For example see below - Tomorrow when i enter in Thursdays actual data of 5 - i would like:

1. The bottom total to add up to Target total 160
2. The Actual Column to add up to 105 (if actual number is 5 for the day)
3. The +/1 Column to adjust itself to -55 (160-105).

The tricky part is i only want this to add up when i enter the Actual figure this can be any time of the day.

Thanks everyone for your help Chris

Debbie Sawyer

Hi Chris

Try using this formula in the Target column (in place of your 152 total here):

=SUMIF(Actual1:Actual31, >=0, Target1:Target31)

Then in your +/- column (where your -52 total is):

=SUMIF(Actual1:Actual31, >=0, [+/-]1:[+/-]31)

This formula is looking at the Actual column and if there is a 0 or a number greater than 0 present it is adding up the total of the Target (or +/-) column for the same rows. (nb use relevant row numbers in your range - I have chosen 1 - 31 here as this is the number of rows in your screenshot!)

It works for me:

Hope this helps

Kind regards

​​Debbie Sawyer Consultant & Training Manager

 

Andrée Starå

@chris.italiano78351

Hi Chris,

Here's another alternative to Debbies excellent one.

Try something like this.

Target

=SUMIF(Created:Created; <=TODAY(); Target:Target)

The same version but with the below changes for your and others convenience.

=SUMIF(Created:Created, <=TODAY(), Target:Target)

Actual

=SUM(Actual:Actual)

+/-

=Actual@row - Target@row

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Or you could just use

=SUM(range)

in the Target and Actual cells.

=SUM(Target1:Target31)

=SUM(Actual1:Actual31)

Then use

=Actual@row - Target@row

in the [+/-] cell.

There is no need to specify to ignore blank fields as they are counted as zero anyway and won't change your final numbers.

Debbie Sawyer

Hi Chris

Try using this formula in the Target column (in place of your 152 total here):

=SUMIF(Actual1:Actual31, >=0, Target1:Target31)

Then in your +/- column (where your -52 total is):

=SUMIF(Actual1:Actual31, >=0, [+/-]1:[+/-]31)

This formula is looking at the Actual column and if there is a 0 or a number greater than 0 present it is adding up the total of the Target (or +/-) column for the same rows. (nb use relevant row numbers in your range - I have chosen 1 - 31 here as this is the number of rows in your screenshot!)

It works for me:

Hope this helps

Kind regards

​​Debbie Sawyer Consultant & Training Manager

 

Andrée Starå

@chris.italiano78351

Hi Chris,

Here's another alternative to Debbies excellent one.

Try something like this.

Target

=SUMIF(Created:Created; <=TODAY(); Target:Target)

The same version but with the below changes for your and others convenience.

=SUMIF(Created:Created, <=TODAY(), Target:Target)

Actual

=SUM(Actual:Actual)

+/-

=Actual@row - Target@row

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Or you could just use

=SUM(range)

in the Target and Actual cells.

=SUM(Target1:Target31)

=SUM(Actual1:Actual31)

Then use

=Actual@row - Target@row

in the [+/-] cell.

There is no need to specify to ignore blank fields as they are counted as zero anyway and won't change your final numbers.

Andrée Starå

@Paul Newcome

If I do not misunderstand Chris's need, that won't work. Target will be filled first, and then the values will not be correct because he only wants to show them after the Actual is added.

Paul Newcome

@Andrée Starå Apparently it is too early in the morning for me. I missed that part. You're right.

@chris.italiano78351 Disregard my previous comment. In my attempt to simplify things, I missed the part that said it wasn't that simple.

Andrée Starå

@Paul Newcome

It's 1-1

🤣

https://media.giphy.com/media/enqNHfovAzcMPnZjF3/giphy.gif

Paul Newcome

Haha. Good thing you're only looking at today. If we went far enough back, you'd have me blown out of the water. 🤣

chris.italiano78351

Thanks Heaps Debbie, Andrea and Paul - Exactly what i was after. 👍👍

Stay Safe and take care

Chris

Andrée Starå

@chris.italiano78351

Excellent!

You are more than welcome!