How to summarize group by advanced

If i have 4 columns of data and i want 1 column to be summed only if the other 3 columns match- how do i do that in the summarie tool or do i need another to to help?

ex: column a, b, see are all the same so just need 1 row returned with those items and column d summed

column a Column b Column c column d

ham cake. Soda 4

ham. Cake. Soda. 8

Developer Tools

Accepted answers

alexnajm

I dont - if you provide the data and expected output then I can try to help but otherwise based on the explanation it is group by the 9 columns and sum by the price

All comments

alexnajm

You’ll need a Data Cleansing tool first to make all of the values into the same case (for example, lowercase), and then the Summarize tool to group by the first three fields and sum by the fourth field!

alexnajm

They would end up on different rows if the case isn’t dealt with because the Summarize is case sensitive.

Dittau

Lets say i had 10 columns, wanted to group by 3 and sum 1 but still want the other 6 columns to show- how would i accomplish that?

alexnajm

They would also need to be grouped by (or some sort of aggregation like a concatenate) in order to come through the Summarize tool. Otherwise, they could be joined in somehow, appended in, etc but it'll highly depend on the use case

Dittau

Do you have a screenshot of how that may look. If you do 3 columns with (zipcode, city, state) and 6 columns with food item and 1 column with price…group by zipcode city and state and sum the price but also show the other 6 columns as well

alexnajm

I dont - if you provide the data and expected output then I can try to help but otherwise based on the explanation it is group by the 9 columns and sum by the price

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335