-3

I have a dataset of usage data per account, with multiple usage metrics for four years represented monthly as 'Jan.21'. I need yearly aggregates in columns in pivot table. Columns: 'Account name', 'Usage Metric', 'Jan.21', 'Feb.21', etc. ...

Example data:

Account Name;Metric;Feb.23;Mar.23;Apr.23
Organization_A;searches_platform;9;11;7
Organization_A;total_requests;13;6;3
Organization_A;unique_requests;10;5;3
Organization_B;searches_platform;101;54;66
Organization_B;total_requests;301;82;174
Organization_B;unique_requests;269;68;162
Organization_C;searches_platform;7;1;71
Organization_C;total_requests;107;3;40
Organization_C;unique_requests;104;2;31

In the pivot table, I can only add columns one by one (I have 48 columns), and then later, I can sum selected 12 columns for each year manually. Or I can add a calculated field, but this is also taxing, as I have to select each column manually.

excel pivot table screenshot

Is there a more elegant solution in excel?

I tried changing column datatypes to date as suggested by lots of forums, but this didn't help.

I need the resulting table to look like this: usage metric filter, 'account name' and total usage per 'year x', 'year y'. I use Excel for mac 16.93.

Improve this question
1
  • 1
    Please show us what you have tried and explain how it fails to achieve the desired result Commented Feb 7 at 20:28

1 Answer 1

Reset to default
1

Try this in a new sheet:

Account Name Metric 44958 44986 44986 Account Name Metric 2023 2024 2025
Organization_A searches_platform 9 11 7 =A2 =B2 =SUMIFS($C2:$E2,$C$1:$E$1,">="&DATE(I$1,1,1),$C$1:$E$1,"<="&DATE(I$1,12,31)) =SUMIFS($C2:$E2,$C$1:$E$1,">="&DATE(J$1,1,1),$C$1:$E$1,"<="&DATE(J$1,12,31)) =SUMIFS($C2:$E2,$C$1:$E$1,">="&DATE(K$1,1,1),$C$1:$E$1,"<="&DATE(K$1,12,31))
Organization_A total_requests 13 6 3 =A3 =B3 =SUMIFS($C3:$E3,$C$1:$E$1,">="&DATE(I$1,1,1),$C$1:$E$1,"<="&DATE(I$1,12,31)) =SUMIFS($C3:$E3,$C$1:$E$1,">="&DATE(J$1,1,1),$C$1:$E$1,"<="&DATE(J$1,12,31)) =SUMIFS($C3:$E3,$C$1:$E$1,">="&DATE(K$1,1,1),$C$1:$E$1,"<="&DATE(K$1,12,31))
Organization_A unique_requests 10 5 3 =A4 =B4 =SUMIFS($C4:$E4,$C$1:$E$1,">="&DATE(I$1,1,1),$C$1:$E$1,"<="&DATE(I$1,12,31)) =SUMIFS($C4:$E4,$C$1:$E$1,">="&DATE(J$1,1,1),$C$1:$E$1,"<="&DATE(J$1,12,31)) =SUMIFS($C4:$E4,$C$1:$E$1,">="&DATE(K$1,1,1),$C$1:$E$1,"<="&DATE(K$1,12,31))
Organization_B searches_platform 101 54 66 =A5 =B5 =SUMIFS($C5:$E5,$C$1:$E$1,">="&DATE(I$1,1,1),$C$1:$E$1,"<="&DATE(I$1,12,31)) =SUMIFS($C5:$E5,$C$1:$E$1,">="&DATE(J$1,1,1),$C$1:$E$1,"<="&DATE(J$1,12,31)) =SUMIFS($C5:$E5,$C$1:$E$1,">="&DATE(K$1,1,1),$C$1:$E$1,"<="&DATE(K$1,12,31))
Organization_B total_requests 301 82 174 =A6 =B6 =SUMIFS($C6:$E6,$C$1:$E$1,">="&DATE(I$1,1,1),$C$1:$E$1,"<="&DATE(I$1,12,31)) =SUMIFS($C6:$E6,$C$1:$E$1,">="&DATE(J$1,1,1),$C$1:$E$1,"<="&DATE(J$1,12,31)) =SUMIFS($C6:$E6,$C$1:$E$1,">="&DATE(K$1,1,1),$C$1:$E$1,"<="&DATE(K$1,12,31))
Organization_B unique_requests 269 68 162 =A7 =B7 =SUMIFS($C7:$E7,$C$1:$E$1,">="&DATE(I$1,1,1),$C$1:$E$1,"<="&DATE(I$1,12,31)) =SUMIFS($C7:$E7,$C$1:$E$1,">="&DATE(J$1,1,1),$C$1:$E$1,"<="&DATE(J$1,12,31)) =SUMIFS($C7:$E7,$C$1:$E$1,">="&DATE(K$1,1,1),$C$1:$E$1,"<="&DATE(K$1,12,31))
Organization_C searches_platform 7 1 71 =A8 =B8 =SUMIFS($C8:$E8,$C$1:$E$1,">="&DATE(I$1,1,1),$C$1:$E$1,"<="&DATE(I$1,12,31)) =SUMIFS($C8:$E8,$C$1:$E$1,">="&DATE(J$1,1,1),$C$1:$E$1,"<="&DATE(J$1,12,31)) =SUMIFS($C8:$E8,$C$1:$E$1,">="&DATE(K$1,1,1),$C$1:$E$1,"<="&DATE(K$1,12,31))
Organization_C total_requests 107 3 40 =A9 =B9 =SUMIFS($C9:$E9,$C$1:$E$1,">="&DATE(I$1,1,1),$C$1:$E$1,"<="&DATE(I$1,12,31)) =SUMIFS($C9:$E9,$C$1:$E$1,">="&DATE(J$1,1,1),$C$1:$E$1,"<="&DATE(J$1,12,31)) =SUMIFS($C9:$E9,$C$1:$E$1,">="&DATE(K$1,1,1),$C$1:$E$1,"<="&DATE(K$1,12,31))
Organization_C unique_requests 104 2 31 =A10 =B10 =SUMIFS($C10:$E10,$C$1:$E$1,">="&DATE(I$1,1,1),$C$1:$E$1,"<="&DATE(I$1,12,31)) =SUMIFS($C10:$E10,$C$1:$E$1,">="&DATE(J$1,1,1),$C$1:$E$1,"<="&DATE(J$1,12,31)) =SUMIFS($C10:$E10,$C$1:$E$1,">="&DATE(K$1,1,1),$C$1:$E$1,"<="&DATE(K$1,12,31))

Is it enouth already?

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

yes, that worked after I replaced commas with semicolons. Thank you! I hoped to avoid writing formulas with pivot tables. Please let me know if that kind of data aggregation is possible at all using pivots.
I do not see a way to extract the desired result from the data via a pivot table. In order to achive it by that, you should rearrange the data. You'd need 4 columns: Account Name; Metric; Year; Value. For example, your sample data should become a range 27 rows high (+1 for headers) and 4 columns wide. The first 2 columns would have the same data of your sample repeated 3 times, the Year column would contain the year of each headers repeated 7 times, the Value column would have the usage data for the corrisponding slot. Only then you could apply a pivot table to get the desired result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.