SUMPRODUCT Multiple Criteria with Distinct Values

Since you are using 365, can you use this formula instead of SUMPRODUCT?

=COUNTA(UNIQUE(FILTER($B$6:$C$18,$C$6:$C$18=$C$3)))/2

Originally Posted by Gregb11

Since you are using 365, can you use this formula instead of SUMPRODUCT?

=COUNTA(UNIQUE(FILTER($B$6:$C$18,$C$6:$C$18=$C$3)))/2

Thanks for this! In this instance I am actually having to use Office 2016 and I can't get the above to function.

I am also using this within the context of a SUBTOTAL/OFFSET to calculate for visible cells only (based on some other filtering required), therefore, I will need to use a SUMPRODUCT which currently looks like this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C6:C18,ROW(C6:C18)-MIN(ROW(C6:C18)),,1)),(--EXACT($C$3,C6:C18)),(--(B6:B18<>""))/COUNTIF(B6:B18,B6:B18&"")+0)

Please let me know,
KJ

Originally Posted by CARACALLA

f5=SUM(IF(FREQUENCY(IF($C$6:$C$18=C3,MATCH(B6:B18,B6:B18,0)),ROW(B6:B18)-ROW(B6)+1),1))

control+shift+enter

Thanks, this appears to work in isolation, but I am hoping to build into a SUMPRODUCT to maintain the ability to calculate for visible cells only - is there a way to combine this in to a SUMPRODUCT?