Summing Between Condition

Question

I am still trying to learn this software as well as the search function on the community, but wanted to get this out there as a tandem to find solution quicker hopefully.

I have a dataset as seen below, and the desired output is the last column (I will then use that desired output for further analysis):

DateJobTypeHoursDesired Output9/23/20131S669/27/20131Q0 1/6/20141S2 2/21/20141S2 4/30/20141S3 4/30/20141S0.57.55/2/20141Q0 5/2/20141Q0 10/16/20141E0.5 10/16/20141S2.52.53/12/20151Q0 4/16/20151Q0 4/16/20151Q0 4/16/20151Q0 4/16/20151Q0 4/18/20151E0.5 4/23/20151Q0 4/23/20151Q0 4/23/20151Q0 4/23/20151Q0 4/23/20151Q0 4/24/20151S334/27/201520S115/4/201520Q0 5/19/201520E0.4 7/22/201520Q0 12/9/201520Q0 2/12/201620S662/17/201620Q0 5/26/201620E0.5 5/26/201620S668/24/201620E0.5 1/16/201720S4.5 1/17/201720S1.561/20/201720E0.2

I am basically wanting to sum the Hours between Types and only for S type, but also make sure it only sums the "S" type hours by the Job.

So if the Job changes but there is not a change in Type it needs to sum those separately (ex. Job 1 to 20)

Trying to work this out with the formula tool, but not having much luck.

AngelosPachis · Accepted Answer

Hi @fowlerc ,

You can achieve that with a multi-row formula tool, which is similar to a conventional formula tool, with only difference that it works from the top of a column and downwards.

In the first multi-row formula tool, I am just summing from the top to the bottom all the values that have a Type S, whilst assigning 0 to the other types.

That will restart if the job changes, so notice how the calc restarts when the job turns from 1 to 20.

Then the following multi-row formula was just to flag the maximum values for each Job-Type combination, because in your desired output you only had the maximum values. With a similar logic, I flagged the values as keep and null and then got rid of everything that was not flagged as "Keep".

Hope that helps, let me know if you have any questions.

phoebe_kelley · Accepted Answer

Here's another way, still using the multi-row formula, but a little differently than the previous reply. the first multi-row formula is creating a "sub job" to separate the job into blocks of the same type. Then a running total grouped by that sub-job and type. The final multi-row formula determines if the row is the last "s" in a group, and only takes the subtotal numbers for that row. That was a fun problem, thanks for sharing! 🙂