How do you create a "pivot table" and sum multiple criteria

[gchong96]

if i have data like this, and want to summarize it based on entity and account category how do i do that?

From this:

entity	account #	account category	2022	2023	2024	2025
canada	1000	cash	100	200	100	400
canada	1001	cash
us	1000	cash	400	200	200	200
us	1005	cash	300	200	200	200
australia	100	cash	100			200
australia	400	cash	100
nz	100	cash		100		100
nz	400	cash

to this

entity	account category	2022	2023	2024	2025
canada	cash	100	200	100	400
us	cash	700	400	400	400
australia	cash	200			200
na	cash		100		100

[alexnajm · Accepted solution]

Transpose one way (first three are key fields, the year fields are the years), then Crosstab back (group by entity and account category, header is name, value is value, and sum for aggregation)

[flying008]

Hi, @gchong96 

FYI.

[alexnajm]

Exactly as I said 😊 the benefit to the transpose Crosstab option is the fact that if more years come in, they will

get automatically included!

[flying008]

Hi, @alexnajm 

Yes, you are !