Sumifs with duplicate criteria Max Count

[aaleem]

Hi,

The SumIfs formula with duplicate criteria is working fine, however, the result is coming on the first count, how can we bring this result to the max count of the same subset.

Any help will be highly appreciated.

thanks
aleem

Results					Lookup Data
Cust no.	Cust. Name	Amount			Doc Number	G/L Date	LT 1 Amount	Cust no.
806290	David Miller	(15,000.00)	<--IF(COUNTIF($A$3:A3,A3)=1,SUMIFS(H:H,I:I,A3),0)		10001816	10.01.11	(10,000.00)	806290
806290	David Miller	-			15000341	16.05.16	(5,000.00)	806290
806290	David Miller	-			19054479	26.01.20	10,000.00	806290
806290	David Miller	-			12004480	26.01.20	5,000.00	806290
806290	David Miller	-			21004479	26.01.20	(10,000.00)	806290
806290	David Miller	-			23004480	26.01.20	(5,000.00)	806290
806290	David Miller	-			10001819	11.01.11	(5,000.00)	807263
806290	David Miller	-	<--Desired Result --The sumifs result should come here		15000344	17.05.16	(2,500.00)	807263
					19054482	27.01.20	5,000.00	807263
807263	Tim Pane	(5,000.00)			12004483	27.01.20	2,500.00	807263
807263	Tim Pane	-			21004482	27.01.20	(5,000.00)	807263
807263	Tim Pane	-
807263	Tim Pane	-	<--Desired Result --The sumifs result should come here

 

[jtakw]

Hi,

May be something like this?

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	Results					Lookup Data
2	Cust no.	Cust. Name	Amount			Doc Number	G/L Date	LT 1 Amount	Cust no.
3	806290	David Miller	0.00			10001816	40553	-10000	806290
4	806290	David Miller	0.00			15000341	42506	-5000	806290
5	806290	David Miller	0.00			19054479	43856	10000	806290
6	806290	David Miller	0.00			12004480	43856	5000	806290
7	806290	David Miller	0.00			21004479	43856	-10000	806290
8	806290	David Miller	0.00			23004480	43856	-5000	806290
9	806290	David Miller	0.00			10001819	40554	-5000	807263
10	806290	David Miller	-15,000.00			15000344	42507	-2500	807263
11			0.00			19054482	43857	5000	807263
12	807263	Tim Pane	0.00			12004483	43857	2500	807263
13	807263	Tim Pane	0.00			21004482	43857	-5000	807263
14	807263	Tim Pane	0.00
15	807263	Tim Pane	-5,000.00
Sheet916

Cell Formulas
Range	Formula
C3:C15	C3	=IF(COUNTIF(A$3:A3,A3)=COUNTIF(A$3:A100,A3),SUMIFS(H:H,I:I,A3),0)

 

[aaleem]

Yes perfect jtakw, Thank you so much.

kind regards
Aleem

 

[jtakw]

You're welcome, thanks for the feedback.