Sum unique values based on multiple criteria

[liulilli]

Good morning,
I'm trying to figure out how to obtain the sum of a set of records but I've been unable to do so. The solution proposed here worked perfectly but I've encountered an additional problem while trying to apply it to my dataset.
This is an example of the original dataset

Type of product	Date	Count
a	202011	5
b	202011	7
b	202011	7
c	202011	6
a	202012	5
a	202012	5
b	202012	9
c	202012	2
c	202012	2
a	202101	4
b	202101	5

The desired results would be

CONDITION A	5 + 5 + 4 = 14
CONDITION B	7 + 9 + 5 = 21
CONDITION C	6 + 2 = 8

Basically

1. If the number in column C is repeated but the date in column B is the same it should only sum that number once
2. If the number in column C is repeated and the date in column B is different from another instance where the number was the same it should be taken into consideration for the sum

I would need to do this possibly without using pivots or using helper columns.
Thank you so much in advance for your help

 

[shift-del]

Hi

=SUM(INDEX(UNIQUE(FILTER($B$2:$C$12,$A$2:$A$12="a")),,2))

 

[liulilli]

Thank you so much for your help, it works perfectly!
I just need to ask you one additional thing: what if I wanted to do the same thing but only considering from for example 202012 onwards? (The original database goes up until 202111).

 

[Phuoc]

Try this

Book1
	A	B	C
1	Type of product	Date	Count
2	a	202011	5
3	b	202011	7
4	b	202011	7
5	c	202011	6
6	a	202012	5
7	a	202012	5
8	b	202012	9
9	c	202012	2
10	c	202012	2
11	a	202101	4
12	b	202101	5
13
14	CONDITION
15	a	14
16	b	21
17	c	8
Sheet1

Cell Formulas
Range	Formula
B15:B17	B15	=SUM(IF(($A$2:$A$12=A15)*MATCH($A$2:$A$12&$B$2:$B$12&"|"&$C$2:$C$12,$A$2:$A$12&$B$2:$B$12&"|"&$C$2:$C$12,0)=ROW($A$2:$A$12)-1,$C$2:$C$12))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[liulilli]

Try this

Book1
	A	B	C
1	Type of product	Date	Count
2	a	202011	5
3	b	202011	7
4	b	202011	7
5	c	202011	6
6	a	202012	5
7	a	202012	5
8	b	202012	9
9	c	202012	2
10	c	202012	2
11	a	202101	4
12	b	202101	5
13
14	CONDITION
15	a	14
16	b	21
17	c	8
Sheet1

Cell Formulas
Range	Formula
B15:B17	B15	=SUM(IF(($A$2:$A$12=A15)*MATCH($A$2:$A$12&$B$2:$B$12&"|"&$C$2:$C$12,$A$2:$A$12&$B$2:$B$12&"|"&$C$2:$C$12,0)=ROW($A$2:$A$12)-1,$C$2:$C$12))
Press CTRL+SHIFT+ENTER to enter array formulas.

Both formulas work but maybe this works better because in the original database I have columns between date and count that I don't need to take into consideration and I'm afraid that shift-del's formula wouldn't work (even though it works in the sample as I've already said). Thank you so much!

 

[shift-del]

I just need to ask you one additional thing: what if I wanted to do the same thing but only considering from for example 202012 onwards? (The original database goes up until 202111).

I'm using an Excel table (Tabelle1).

=LET(
_t,UNIQUE(Tabelle1),
_type,INDEX(_t,,1),
_date,INDEX(_t,,2),
_count,INDEX(_t,,3),
_filter,FILTER(_count,(_type="a")*(_date>=202012),0),
SUM(_filter))

 

[liulilli]

T

I'm using an Excel table (Tabelle1).

=LET(
_t,UNIQUE(Tabelle1),
_type,INDEX(_t,,1),
_date,INDEX(_t,,2),
_count,INDEX(_t,,3),
_filter,FILTER(_count,(_type="a")*(_date>=202012),0),
SUM(_filter))

Thank you! Would it be possible without formatting as a table?

 

[shift-del]

Just replace the table name with the table range.

 

[liulilli]

Just replace the table name with the table range.

Thank you so much! Everything works perfectly