Summing Between Condition

[fowlerc]

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):

Date	Job	Type	Hours	Desired Output
9/23/2013	1	S	6	6
9/27/2013	1	Q	0
1/6/2014	1	S	2
2/21/2014	1	S	2
4/30/2014	1	S	3
4/30/2014	1	S	0.5	7.5
5/2/2014	1	Q	0
5/2/2014	1	Q	0
10/16/2014	1	E	0.5
10/16/2014	1	S	2.5	2.5
3/12/2015	1	Q	0
4/16/2015	1	Q	0
4/16/2015	1	Q	0
4/16/2015	1	Q	0
4/16/2015	1	Q	0
4/18/2015	1	E	0.5
4/23/2015	1	Q	0
4/23/2015	1	Q	0
4/23/2015	1	Q	0
4/23/2015	1	Q	0
4/23/2015	1	Q	0
4/24/2015	1	S	3	3
4/27/2015	20	S	1	1
5/4/2015	20	Q	0
5/19/2015	20	E	0.4
7/22/2015	20	Q	0
12/9/2015	20	Q	0
2/12/2016	20	S	6	6
2/17/2016	20	Q	0
5/26/2016	20	E	0.5
5/26/2016	20	S	6	6
8/24/2016	20	E	0.5
1/16/2017	20	S	4.5
1/17/2017	20	S	1.5	6
1/20/2017	20	E	0.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 solution]

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 solution]

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! 🙂