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

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)