2

Here is my raw data:

   Site    IP        Visits    Age (in hours)
1  uu.com  1.2.3.4   10        3                  
2  vv.com  2.9.7.5   2         4
3  kk.com  2.9.7.5   8         24
4  uu.com  1.2.3.4   12        24                  
5  vv.com  2.9.7.5   10        24
6  kk.com  2.9.7.5   8         48

I have build a pivot table like this in Google spreadsheet:

   Col. A    Col. B   MAX of Visits   MAX of Age
1  1.2.3.4   uu.com   12              24    
2  2.9.7.5   kk.com   8               48
3            vv.com   10              24

When I ask Google Spreadsheet to give the totals, I get the following result:

   Col. A             Col. B   MAX of Visits   MAX of Age
1  1.2.3.4            uu.com   12              24    
2  Total for 1.2.3.4           12              24
3  2.9.7.5            kk.com   8               48
4                     vv.com   10              24
5  Total for 2.9.7.5           10              48

This is not what I'm expecting because I want the SUM('Max of visits') as total. Clearly, this is the final pivot table I want:

   Col. A             Col. B   MAX of Visits   MAX of Age
1  1.2.3.4            uu.com   12              24    
2  Total for 1.2.3.4           12              24
3  2.9.7.5            kk.com   8               48
4                     vv.com   10              24
5  Total for 2.9.7.5           18              48

Notice on line 5 , column 'Max of Visits', I want 18 and not 10 as Google Spreadsheet gives me.

How can I acheive that ?

2

Perhaps you can use the following formula.

Formula

=QUERY(DATA!C1:F7, "SELECT C, MAX(E), MAX(F) GROUP BY C PIVOT D")

Screenshot

enter image description here

Remark

In order to get the summation, I simply added a summation for the consecutive columns (see example).

Example

I've created an example file for you: SUM and MAX in PIVOT TABLE

  • Tks for your useful answer.Actually, I'd like to perform this: SELECT D,C,SUM(MAX(E)) FROM <DATA> GROUP BY D,C – Stephan Nov 15 '13 at 22:37
  • @Alex The <DATA> is contained in the SELECT statement....Quote from Google Query Language: The "from" clause has been eliminated from the language. – Jacob Jan Tuinstra Nov 15 '13 at 22:40
  • You're right, but I have tried this SELECT D,C,SUM(MAX(E)) GROUP BY D,C and got #VALUE error. – Stephan Nov 15 '13 at 22:50
  • @Alex Haha, added a summation below the result (didn't you see that....haha). There's no other way. I will edit my answer. – Jacob Jan Tuinstra Nov 15 '13 at 22:52
  • I did see the summation ;) – Stephan Nov 15 '13 at 23:03
-3

Try this:

=MAX(Start Cell:End Cell)

Here is a demo: =MAX(Start Cell:End Cell)

  • How do I group by ip and site ? – Stephan Nov 9 '13 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.