Sum if same ID and same date - only once

[Mariella]

Hi there,

I am trying to sum quantities in a database. Thereby it is important that for each ID, when the date is the same, the value is only added once. I am absolutely struggling to calculate that.

Maybe someone can help me? I would really appreciate it!

Best,
Mariella

 

[Mariella]

select your original table
from the ribbon Data - From Table
then in Power Query Editor open Advanced Editor
and replace all code there with code copied from the post
then Done , Close&Load

after that:

Cool!! Thank you

 

[sandy666]

Cool!! Thank you

You are welcome
Have a nice day

 

[Peter_SSs]

I would have to list the options before

Well, we could do that with a formula as well, but with that many rows I suspect the SUMPRODUCT formula would slow the sheet down considerably.

Book1
	A	B	C	D	E	F	G
1	id	date	qty			ID	Sum Once
2	1	12/12/2019	5			1	19
3	1	13/12/2019	5			2	11
4	2	7/12/2019	5			3	6
5	3	13/12/2019	6
6	2	13/12/2019	6
7	2	13/12/2019	6
8	2	7/12/2019	5
9	1	12/12/2019	5
10	1	10/12/2019	9
Sum

Cell Formulas
Range	Formula
F2:F4	F2	=IFERROR(INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($F$1:F1,$A$2:$A$10)+(A$2:A$10=""),0),0)),"")
G2:G4	G2	=SUMPRODUCT((A$2:A$10=F2)*(MATCH(A$2:A$10&"|"&B$2:B$10,A$2:A$10&"|"&B$2:B$10,0)=ROW(A$2:A$10)-ROW(A$2)+1),C$2:C$10)