Is it possible to combine Filter and Sum?

[jakeman]

I have been working on function that will create a unique list of values that sum the amounts for each value.

For example:

Ctr Code	Date	Amount
001	1/2/23	55.00
002	1/15/23	12.75
001	1/21/23	38.22
002	1/23/23	100.01

What I would like to do is create a unique list of Ctr Codes that would read as follows:

Ctr Code	Amount
001	93.22
002	112.76

I'm stuck on how to combine filter function which filter on certain dates and the sum function which would add the Amount field together.

 

[Sufiyan97]

Do you want something like?

Book16
	A	B	C	D	E	F	G
1	Ctr Code	Date	Amount
2	1	1/2/2023	55		1	93.22
3	2	1/15/2023	12.75		2	112.76
4	1	1/21/2023	38.22
5	2	1/23/2023	100.01
6
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=SUM(FILTER($C$2:$C$5,$A$2:$A$5=E2,""))

 

[Fluff]

What version of Excel are you using?

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

 

[jakeman]

Do you want something like?

Book16
	A	B	C	D	E	F	G
1	Ctr Code	Date	Amount
2	1	1/2/2023	55		1	93.22
3	2	1/15/2023	12.75		2	112.76
4	1	1/21/2023	38.22
5	2	1/23/2023	100.01
6
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=SUM(FILTER($C$2:$C$5,$A$2:$A$5=E2,""))

Hi Sufiyan - I appreciate your attempt.

So this isn't exactly what I need. My desired solution is to return an array that has summed on the Ctr Code and shows the total amount next to the Ctr Code, all within the same array.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	Ctr Code	Date	Amount
2	1	02/01/2023	55		1	93.22
3	2	15/01/2023	12.75		2	112.76
4	1	21/01/2023	38.22
5	2	23/01/2023	100.01
6
Lists

Cell Formulas
Range	Formula
E2:F3	E2	=LET(u,UNIQUE(FILTER(A2:A100,A2:A100<>"")),HSTACK(u,SUMIFS(C:C,A:A,u)))
Dynamic array formulas.