# Unique Running Total Criteria Conditonal

1. ## Unique Running Total Criteria Conditonal

Hello,

I have a unique issue dealing with a running total with criteria and conditionals to make it calculate successfully . I've spent the past two days reading over forum posts and help documents and haven't yet been able to make headway. I have experience in BASIC, but I only want to turn to VBA as a last resort, as I plan on having the spreadsheet run on smartphones by friends and fellow co-workers and would like to minimize any security risks that come with enabling macros with inexperienced end users.

What I'm trying to do is have a column of daily totals summarized in the adjacent columns: a 7 day total and a 8 day total. It will calculate, including the current day, a running count of both the last 7 days and 8 days.

My problem lies with having a "reset" day in which the running totals are reset and begin counting over. These "reset" days could come at any time during the month, and happen more than once a week.

I've tried my best at attempts to create a formula string consisting of SUM, ADDRESS, MIN, MATCH and a few other functions that have resulted in failures. I can't wrap my head around this at all.

Attached is an example of what I'm trying to do. Note that some days the totals could be zero (0) but do not denote a "reset" day. Only the daily totals column will be manipulated by the user. I would like both the 7 and 8 day totals to dynamically calculate per the user's entry.

Any help or direction would be extremely appreciated. I'm at my wits end.

Much Thanks,
Jones

2. ## Re: Unique Running Total Criteria Conditonal

Put this nasty formula in C2:

=SUM(OFFSET(\$B\$1, INDEX(\$A:\$A, MATCH(IF(ISERROR(LOOKUP(2, 1/(\$B\$1:\$B2="RESET"))), MAX(\$A2-6, 1), MAX(\$A2-6, 1, LOOKUP(2,1/(\$B\$1:\$B2="RESET"), \$A\$1:\$A2))), \$A:\$A, 0)),,,):\$B2)

In D2:

=SUM(OFFSET(\$B\$1, INDEX(\$A:\$A, MATCH(IF(ISERROR(LOOKUP(2, 1/(\$B\$1:\$B2="RESET"))), MAX(\$A2-7, 1), MAX(\$A2-7, 1, LOOKUP(2,1/(\$B\$1:\$B2="RESET"), \$A\$1:\$A2))), \$A:\$A, 0)),,,):\$B2)

...then copy those two cells downward.

3. ## Re: Unique Running Total Criteria Conditonal

I think this one will be slightly less nasty for C2 copied down

=SUM(INDEX(B\$1:B2,MAX(ROWS(B\$1:B2)+1-7,LOOKUP(9^9,IF({1,0},1,MATCH(2,INDEX(1/(B\$1:B2="reset"),0)))))):B2)

change the 7 to an 8 for D2

4. ## Re: Unique Running Total Criteria Conditonal

I'd like to say first, thank you both for helping me out with this.

I tried both of your solutions and I'm afraid I may be making mistakes. I should note that right now I'm on a linux virtual machine as I don't have access to my desktop and do not have MS Excel on hand. I'm running the latest version of OpenOffice.

OO's syntax is a bit strange as it requires you to have semi-colons ";" instead of commas "," to separate parameters with each function in the formula. I replaced all commas with semi-colons and inserted both of your respective formulas into C2.

I received an error of 511 for JBeaucaire's solution, and #VALUE! for daddylonglegs's. I'm quite certain it is an error with OpenOffice and I'll work to debug it as soon as I possibly can or find access to Excel. Unfortunately my options are a bit limited as I'm away from home.

After I made my initial post, I did make some headway. It seems I was finally able to string a successful formula together that seems to be working. I've tested it and it seems to be successful. However I used INDIRECT and strings (ie: "\$B\$2", "\$B\$8", etc) that must be changed by hand per each new cell in the column.

Below is my solution; however I have a feeling that it isn't the most clean or optimized way of getting to my destination. Note that I'm having to use semi-colons to seperate parameters per OpenOffice's formula syntax requirements.

In C8:
=SUM(INDIRECT(IF(SUMPRODUCT(MAX((B2:B8="RESET")*(ROW(B2:B8))))=0;"\$B\$2";"\$B\$"&SUMPRODUCT(MAX((B2:B8="RESET")*(ROW(B2:B8))))+1)&":"&"\$B\$8"))

It's a dirty hack for sure. I have to change by hand each string reference to cells. As well, for the first 7 entries I have to manually manipulate the range so that my range is limited to each respective count of the total days to count.

I'll analyze both of your solutions to see if I can successfully recreate them in OpenOffice without errors.

Thank you both again,
Jones

5. ## Re: Unique Running Total Criteria Conditonal

The LOOKUP() function nested inside my formula is a bit of an Excel trick, I wouldn't think OpenOffice would behave the same out of the gate. Good luck on the conversion process.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

6. ## Re: Unique Running Total Criteria Conditonal

Sorry for the late edit; I have been traveling all this week.

Between my fix and the excel solution, I believe this has been resolved.

Thanks again,
Jones

There are currently 1 users browsing this thread. (0 members and 1 guests)