it is even not optimization because the speed will be very close but

I have tested several methods

SUMPRODUCT(Cond1*Cond2)

SUMPRODUCT(Cond1;Cond2)

SUM(Cond1*Cond2) array formula

Generally SUMPRODUCT(Cond1;Cond2) is favorite.

But in this case FREQUENCY is winner

=SUM(INDEX(($C$3:$C$5002=F3)*($B$3:$B$5002<>"")/COUNTIFS($C$3:$C$5002,$C$3:$C$5002&"",$B$3:$B$5002,$B$3:$B$5002&""),)) 5,078125

=SUMPRODUCT(($C$3:$C$5002=F3)*($B$3:$B$5002<>""),1/COUNTIFS($C$3:$C$5002,$C$3:$C$5002&"",$B$3:$B$5002,$B$3:$B$5002&"")) 5,050781

=IFERROR(SUM(IF(FREQUENCY(IF($C$3:$C$5002=F3,MATCH($B$3:$B$5002,$B$2:$B$5002,0)),ROW($B$3:$B$5002)-ROW($B$3)+1),1)),"") 0,0078125

=SUM(INDEX(($C$3:$C$9002=F3)*($B$3:$B$9002<>"")/COUNTIFS($C$3:$C$9002,$C$3:$C$9002&"",$B$3:$B$9002,$B$3:$B$9002&""),)) 17,1875

=SUMPRODUCT(($C$3:$C$9002=F3)*($B$3:$B$9002<>""),1/COUNTIFS($C$3:$C$9002,$C$3:$C$9002&"",$B$3:$B$9002,$B$3:$B$9002&"")) 16,74609

=IFERROR(SUM(IF(FREQUENCY(IF($C$3:$C$9002=F3,MATCH($B$3:$B$9002,$B$2:$B$9002,0)),ROW($B$3:$B$9002)-ROW($B$3)+1),1)),"") 0,015625

=SUM(INDEX(($C$3:$C$18002=F3)*($B$3:$B$18002<>"")/COUNTIFS($C$3:$C$18002,$C$3:$C$18002&"",$B$3:$B$18002,$B$3:$B$18002&""),)) 67,43359

=SUMPRODUCT(($C$3:$C$18002=F3)*($B$3:$B$18002<>""),1/COUNTIFS($C$3:$C$18002,$C$3:$C$18002&"",$B$3:$B$18002,$B$3:$B$18002&"")) 69,51172

=IFERROR(SUM(IF(FREQUENCY(IF($C$3:$C$18002=F3,MATCH($B$3:$B$18002,$B$2:$B$18002,0)),ROW($B$3:$B$18002)-ROW($B$3)+1),1)),"") 0,03125

However

FREQUENCY could be extracted

array formula

is shorter and power also

=SUM(INDEX(($C$3:$C$18002=F3)*($B$3:$B$18002<>"")/COUNTIFS($C$3:$C$18002,$C$3:$C$18002&"",$B$3:$B$18002,$B$3:$B$18002&""),)) 66,38672

=SUMPRODUCT(($C$3:$C$18002=F3)*($B$3:$B$18002<>""),1/COUNTIFS($C$3:$C$18002,$C$3:$C$18002&"",$B$3:$B$18002,$B$3:$B$18002&"")) 65,95703

=IFERROR(SUM(IF(FREQUENCY(IF($C$3:$C$18002=F3,MATCH($B$3:$B$18002,$B$3:$B$18002,0)),ROW($B$3:$B$18002)-ROW($B$3)+1),1)),"") 0,0234375

=IFERROR(SUM(--IF($C$3:$C$18002=F3,MATCH($B$3:$B$18002,$B$3:$B$18002,0)=ROW($B$3:$B$18002)-ROW($B$3)+1)),"") 0,02734375

=IFERROR(SUM(--IF($C$3:$C$18002=F3,MATCH($B$3:$B$18002,$B:$B,)=ROW($B$3:$B$18002))),"") 0,0234375

## Bookmarks