sum a column based on 3 other criteria match

[myduki]

Hi!
I have a table where I have 4 columns (3 criteria and 1 value to sum if the criteria are meet), but I need that the value only sums at the end of the match (so it will only be shown ones that are "true" and empty when false)

criteria 1	criteria 2	criteria 3	values to sum	wishing for:
A	Alpha	Blue	2
A	Alpha	Blue	3	5
A	Beta	Blue	5
A	Beta	Blue	9	14
A	Beta	Green	8	8
B	Delta	Green	7
B	Delta	Green	6	13
B	Alpha	Yellow	10	10
C	Alpha	Blue	11
C	Alpha	Blue	1	12

<tbody>
</tbody>

I have tried this (but is sums up in every cell, and I need it to be empty when it repeats and only show the value ONCE):
=IF(AND(COUNTIF(B$2:B390;B218)=COUNTIF(B:B;B218);COUNTIF(T$2:T390;T218)=COUNTIF(T$2:T390;T218);COUNTIF(U$2:U390;U218)=COUNTIF(U$2:U390;U218));SUMIFS(V:V;B:B;B218;T:T;T218;U:U;U218);" ")

Can somebody help? (I need a formula, not pivot table)
Thanks!

 

[steve the fish]

Hi. See if this works:

=IF(AND(B2=B3,T2=T3,U2=U3),"",SUMIFS($V$2:V2,$B$2:B2,B2,$T$2:T2,T2,$U$2:U2,U2))

Change the ranges to suit.

 

[skorpionkz]

Try this

	A	B	C	D	E	F	G
1	criteria 1	criteria 2	criteria 3	Helper Column	values to sum	wishing for:	Formula
2	A	Alpha	Blue	AAlphaBlue	2
3	A	Alpha	Blue	AAlphaBlue	3	5	5
4	A	Beta	Blue	ABetaBlue	5
5	A	Beta	Blue	ABetaBlue	9	14	14
6	A	Beta	Green	ABetaGreen	8	8	8
7	B	Delta	Green	BDeltaGreen	7
8	B	Delta	Green	BDeltaGreen	6	13	13
9	B	Alpha	Yellow	BAlphaYellow	10	10	10
10	C	Alpha	Blue	CAlphaBlue	11
11	C	Alpha	Blue	CAlphaBlue	1	12	12

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

​

Cell Formula G2 =IF(COUNTIF($D$2:$D$11,D2)=COUNTIF($D$2:$D2,D2),SUMIF($D$2:$D$11,D2,$E$2:$E$11),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[myduki]

Hi,
thanks but this only works if all the data is perfectly order (and it is not). I changed the list for a better view on my problem:

criteria 1	criteria 2	criteria 3	values to sum	[FONT=&quot]wishing for:[/FONT]
A	Alpha	Blue	2
A	Alpha	Blue	3
A	Beta	Blue	5
A	Beta	Blue	9	14
A	Alpha	Blue	8	13
B	Delta	Green	7
B	Delta	Green	6	13
B	Alpha	Yellow	10	10
C	Alpha	Blue	11
C	Alpha	Blue	1	12

<tbody>
</tbody>

 

[skorpionkz]

Try solution I gave you. It doesn't care about order and add value to the last entry for the set

 

[steve the fish]

Heres a couple of ways:

=IF(COUNTIFS($B$2:B2,B2,$T$2:T2,T2,$U$2:U2,U2)=COUNTIFS(B:B,B2,T:T,T2,U:U,U2),SUMIFS($V$2:V2,$B$2:B2,B2,$T$2:T2,T2,$U$2:U2,U2),"")
=IF(ROW()=LOOKUP(2,1/((B:B=B2)*(T:T=T2)*(U:U=U2)),ROW(B:B)),SUMIFS($V$2:V2,$B$2:B2,B2,$T$2:T2,T2,$U$2:U2,U2),"")

 

[myduki]

Try solution I gave you. It doesn't care about order and add value to the last entry for the set

Actually this did worked! (I must have had a typo while converting it to my table then)

Thanks a million! to everybody for your ideas as well!

 

[myduki]

Heres a couple of ways:

=IF(COUNTIFS($B$2:B2,B2,$T$2:T2,T2,$U$2:U2,U2)=COUNTIFS(B:B,B2,T:T,T2,U:U,U2),SUMIFS($V$2:V2,$B$2:B2,B2,$T$2:T2,T2,$U$2:U2,U2),"")


This one worked like a charm as well!
Thanks a lot!