Updating a formula =Min but Sum up first

[zone709]

Hi I use this formula so it caps the number from another sheet i have. I need an update thought on it if i can formula=MIN((G2),1718.15)
The sample sheet below where G has the numbers. Need total of the numbers by date in column H on the last line in before the date changes and use the formula =MIN((G2),1718.15) or something else so it caps it. The green is the total and capped as you can see. Can the formula be updated so i can just drag it down.

Book1
	G	H	I
2	23.56		4/15/2023
3	23.56		4/15/2023
4	23.56	70.68	4/15/2023
5	1440.53		4/22/2023
6	625.2		4/22/2023
7	20.5		4/22/2023
8	40.56	1718.15	4/22/2023
Sheet1

 

[hagia_sofia]

Hello, not sure if I understand it correctly:

=IF(COUNTIFS(I$1:I1,I1)=MAX(COUNTIFS($I$1:$I$7,I1)),MIN(SUMIFS($G$1:$G$7,$I$1:$I$7,I1),1718.15),"")

 

[zone709]

HI it works fine but i need to raise the number since the sheet it long. I raised the 7 to 6000 and then it returns nothing. Do you know why?

 

[zone709]

Hi i figured out why because when i pull formula and if the date is the same on another line above somewhere. Its returning zero. Anyway t to change it so if sums by date but if same date shows up again down the sheet it wont do this?

 

[zone709]

You know what it is. In Column E and didn't realize it. It also changes names at some point. Which then starts with dates already used sorry. So when i up the Sheet number it doesn't work. Anyway around it.
Column E names go by last name first name. Strave, Joe

 

[hagia_sofia]

Could you please possibly upload a larger sample?

 

[zone709]

Ok basically when going down the sheet If the name changes in Column E, You can see my sheet. The date may start as the same above. This is throwing the formula off when i put it in H and drag it down.

Book6
	A	B	C	D	E	F	G	H	I	J
1	se	c	te	N	Name	H	E		P	date
2					Ruff, Mike	16.0	1,462.40		1,176.37	4/5/2023
3					Ruff, Mike	-	1.00		1.00	4/5/2023
4					Ruff, Mike	2.0	365.60		182.80	4/5/2023
5					Ruff, Mike	-	46.20		46.20	4/5/2023
6					Ruff, Mike	-	90.00		90.00	4/5/2023
7					Ruff, Mike	-	87.78		-	4/12/2023
8					Ruff, Mike	32.0	2,924.80		1,241.09	4/12/2023
9					Ruff, Mike	-	1.90		1.90	4/12/2023
10					Ruff, Mike	3.0	548.40		274.20	4/12/2023
11					Ruff, Mike	-	171.00		171.00	4/12/2023
12					Bet, Joe	-	87.78		-	4/5/2023
13					Bet, Joe	32.0	2,924.80		1,241.09	4/5/2023
14					Bet, Joe	-	1.90		1.90	4/5/2023
15					Bet, Joe	3.0	548.40		274.20	4/5/2023
16					Bet, Joe	-	171.00		171.00	4/5/2023
17					Bet, Joe	-	152.46		-	4/12/2023
18					Bet, Joe	40.0	3,656.00		238.02	4/12/2023
19					Bet, Joe	-	297.00		297.00	4/12/2023
20					Bet, Joe	13.0	2,376.40		1,153.17	4/12/2023
21					Bet, Joe	-	3.30		-	4/12/2023
Sheet1

 

[zone709]

Hi anything on this. My guess the countif will not work if it spots the same year in column J going down. The reason why the same year comes up again because the name changes in Column E as you can see in Line 12

 

[Fluff]

How about

=IF(AND(J2=J3,E2=E3),"",MIN(1715.15,SUMIFS(G:G,E:E,E2,J:J,J2)))

 

[zone709]

Fluff thank you. Works great