SUMIFS, multiple criteria incl. unique values

If a helper column is an option

Thanks, it is an option. I'll use that unless someone suggests an alternative.

By data points do you mean rows? Moving from the top row down, I see only three rows whose values in the "Value" column meet those criteria:

row 1	Name	#	Type	Value	Activation
row 2	James	0	type1	200	1	this has "type1" and "1" but # is not unique so sum only this value (200)
row 3	James	1	type3	2	0	this doesn't have "1" nor "type1," so ignore
row 4	James	2	type1	400	0	this doesn't have "1," so ignore
row 5	James	3	type2	40	1	this doesn't have "type1," so ignore
row 6	John	0	type1	200	1	this has "type1" and "1," but # isn't unique (row 2 has the same #), so ignore
row 7	John	4	type3	4	1	this doesn't have "type1," so ignore
row 8	John	2	type1	400	1	this has "type1," "1" and though # isn't unique, row 4 is ignored because it has a "0" value, so add 400 to 200 = 600
row 9	Caitlin	3	type2	40	1	this doesn't have "type1," so ignore
row 10	Caitlin	5	type1	200	1	this has "type1," "1" and # is unique so add 200 to 600 = 800

So that's rows 2, 8 and 10, as I see it. Maybe I should have specified that the 3 criteria above should be satisfied in that order, so that if either of the first 2 aren't satisfied, don't bother considering whether # is unique.

A nice solution was posted here.

With sample data in A2:E10, "Type1" in G1, 1 in G2

=SUMPRODUCT(IFERROR((C2:C10&E2:E10=G1&G2)/COUNTIFS(B2:B10,B2:B10,C2:C10,G1,E2:E10,G2),0),D2:D10)

Array confirmed with Shift Ctrl Enter.

That also works well, thanks.