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)

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]criteria 1[/TD]
[TD]criteria 2[/TD]
[TD]criteria 3[/TD]
[TD]values to sum[/TD]
[TD]wishing for:[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Alpha[/TD]
[TD]Blue[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Alpha[/TD]
[TD]Blue[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Beta[/TD]
[TD]Blue[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Beta[/TD]
[TD]Blue[/TD]
[TD]9[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Beta[/TD]
[TD]Green[/TD]
[TD]8[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Delta[/TD]
[TD]Green[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Delta[/TD]
[TD]Green[/TD]
[TD]6[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Alpha[/TD]
[TD]Yellow[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Alpha[/TD]
[TD]Blue[/TD]
[TD]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Alpha[/TD]
[TD]Blue[/TD]
[TD]1[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

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
criteria 1	criteria 2	criteria 3	Helper Column	values to sum	wishing for:	Formula
A	Alpha	Blue	AAlphaBlue
A	Alpha	Blue	AAlphaBlue
A	Beta	Blue	ABetaBlue
A	Beta	Blue	ABetaBlue
A	Beta	Green	ABetaGreen
B	Delta	Green	BDeltaGreen
B	Delta	Green	BDeltaGreen
B	Alpha	Yellow	BAlphaYellow
C	Alpha	Blue	CAlphaBlue
C	Alpha	Blue	CAlphaBlue

<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>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]

</tbody>

​

[TABLE="width: 85%"]
<tbody>[TR]
[TD][TABLE="width: 100%"]
<thead>[TR="bgcolor: &lt"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: &lt"]G2[/TH]
[TD="align: left"]=IF(COUNTIF($D$2:$D$11,D2)=COUNTIF($D$2:$D2,D2),SUMIF($D$2:$D$11,D2,$E$2:$E$11),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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:

[TABLE="class: cms_table_grid, width: 50"]
<tbody>[TR]
[TD]criteria 1[/TD]
[TD]criteria 2[/TD]
[TD]criteria 3[/TD]
[TD]values to sum[/TD]
[TD][FONT=&quot]wishing for:[/FONT][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Alpha[/TD]
[TD]Blue[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Alpha[/TD]
[TD]Blue[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Beta[/TD]
[TD]Blue[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Beta[/TD]
[TD]Blue[/TD]
[TD]9[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Alpha[/TD]
[TD]Blue[/TD]
[TD]8[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Delta[/TD]
[TD]Green[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Delta[/TD]
[TD]Green[/TD]
[TD]6[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Alpha[/TD]
[TD]Yellow[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Alpha[/TD]
[TD]Blue[/TD]
[TD]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Alpha[/TD]
[TD]Blue[/TD]
[TD]1[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!