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 Answers 2

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

5
  • 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, 2013 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
    Nov 15, 2013 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, 2013 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
    Nov 15, 2013 at 22:52
  • I did see the summation ;)
    – Stephan
    Nov 15, 2013 at 23:03
-4

Try this:

=MAX(Start Cell:End Cell)

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

0

Your Answer

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

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