Combine rows and sum specifc data

[arieta]

Hello, I have some issues on presenting certain data from a file in a specific way. So, I would like to create a formula and sum the appropriate rows of my file. This is essential in order to correctly match those rows with another file. Please see below an example:

Column as is now:

Column 1	Stage	Line	Data1	Data2
Apple	Stage I-III	ALL LINES	5	8
Apple	Stage IV	ALL LINES	2	6
Apple	Stage IV	FIRST LINE	1	7
Banana	Stage I-III	ALL LINES	6	4
Banana	Stage IV	ALL LINES	7	8

Column as I want it to be:

Column 1	Stage	Line	Data1	Data2
Apple	ALL STAGES	ALL LINES	7	14
Apple	Stage IV	FIRST LINE	1	7
Banana	ALL STAGES	ALL LINES	13	12

As you can see in this example, I would like to combine the first 2 rows and the last 2 rows of the ‘stage’ column, and name the cells as ‘ALL STAGES’ and sum the numbers in columns ‘Data1’ and ‘Data2’. Could you please help me with this? Thank you!

[Jonathan-Sherman]

Hi @arieta,

I think this is what you're trying to achieve?

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[arieta]

Hello, 

Thanks a lot for your help! Unfortunately, this does not work as, in the 'stage' column, apart from 'STAGE I-III' and 'STAGE IV' (that I need to combine), I have other text too, such as 'RELAPSED' that should not be combined with the other 2 stages above. Please see below an updated example: 

Column as is now:

Column 1	Stage	Line	Data1	Data2
Apple	Stage I-III	ALL LINES	5	8
Apple	Stage IV	ALL LINES	2	6
Apple	Stage IV	FIRST LINE	1	7
Apple	RELAPSED	ALL LINES	3	4
Apple	RELAPSED	ALL LINES	2	5
Banana	Stage I-III	ALL LINES	6	4
Banana	Stage IV	ALL LINES	7	8

Column as I want it to be:

Column 1	Stage	Line	Data1	Data2
Apple	ALL STAGES	ALL LINES	7	14
Apple	Stage IV	FIRST LINE	1	7
Apple	RELAPSED	ALL LINES	5	9
Banana	ALL STAGES	ALL LINES	13	12

[Jonathan-Sherman · Accepted solution]

Hi @arieta,

I've made a couple of changes and think it's what you're looking for?

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan