# How to do sum(max(...))?

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
``````

``````   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
``````

``````   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 ?

Perhaps you can use the following formula.

## Formula

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

## 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` Commented 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.` Commented 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. Commented 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. Commented Nov 15, 2013 at 22:52
• I did see the summation ;) Commented Nov 15, 2013 at 23:03

Try this:

``````=MAX(Start Cell:End Cell)
``````

Here is a demo: