Find the max value of a pivot table column

shockey8oz

New Member
Joined
Sep 17, 2012
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Good evening-

I am attempting to get the max value of data or a pivot table the is using dates and hours to sort the data. Either a formula or VBA solution is acceptable.

Example of the regular data, the "Number" (Column D) needs to be summed for each hour ie: Hour 23 = 10, Hour 0 = 18, Hour 1 = 8 and then find the max of that --> 18

I can utilize the raw data or make a pivot table. I tried utilizing =getpivotdata with no success. Thanks in advance.

RawData.png
PivotTableData.png
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Could this work for you?

22 01 11.xlsm
BCDEFGHIJ
1DateHourNumberRow LabelsSum of NumberMax
210/01/202223110/01/20221018
310/01/20222352310
410/01/202223411/01/202239
511/01/202202018
611/01/20220718
711/01/2022092313
811/01/202211Grand Total49
911/01/202213
1011/01/202214
1111/01/2022235
1211/01/2022238
13
Max
Cell Formulas
RangeFormula
J2J2=MAXIFS(H2:H8,G2:G8,"<24")
 
Upvote 0
Solution
Peter-

I update my profile.

Thank you for the quick response. I have implemented the formula in my first sheet of real world data and it worked. I will be trying it out further tomorrow. I appreciate your help!
 
Upvote 0
You're welcome. Thanks for the follow-up. :)

.. and for updating your details. (y)

Given your version, You could also try from the raw data without the Pivot Table, like this

22 01 11.xlsm
BCDEL
1DateHourNumberMax
210/01/202223118
310/01/2022235
410/01/2022234
511/01/202202
611/01/202207
711/01/202209
811/01/202211
911/01/202213
1011/01/202214
1111/01/2022235
1211/01/2022238
13
Max
Cell Formulas
RangeFormula
L2L2=MAX(SUMIFS(D2:D12,B2:B12,B2:B12,C2:C12,C2:C12))
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top