Find the max value of a pivot table column

[shockey8oz]

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.

 

[Peter_SSs]

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’)

 

[Peter_SSs]

Could this work for you?

22 01 11.xlsm
	B	C	D	E	F	G	H	I	J
1	Date	Hour	Number			Row Labels	Sum of Number		Max
2	10/01/2022	23	1			10/01/2022	10		18
3	10/01/2022	23	5			23	10
4	10/01/2022	23	4			11/01/2022	39
5	11/01/2022	0	2			0	18
6	11/01/2022	0	7			1	8
7	11/01/2022	0	9			23	13
8	11/01/2022	1	1			Grand Total	49
9	11/01/2022	1	3
10	11/01/2022	1	4
11	11/01/2022	23	5
12	11/01/2022	23	8
13
Max

Cell Formulas
Range	Formula
J2	J2	=MAXIFS(H2:H8,G2:G8,"<24")

 

[shockey8oz]

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!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and for updating your details.

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

22 01 11.xlsm
	B	C	D	E							L
1	Date	Hour	Number								Max
2	10/01/2022	23	1								18
3	10/01/2022	23	5
4	10/01/2022	23	4
5	11/01/2022	0	2
6	11/01/2022	0	7
7	11/01/2022	0	9
8	11/01/2022	1	1
9	11/01/2022	1	3
10	11/01/2022	1	4
11	11/01/2022	23	5
12	11/01/2022	23	8
13
Max

Cell Formulas
Range	Formula
L2	L2	=MAX(SUMIFS(D2:D12,B2:B12,B2:B12,C2:C12,C2:C12))