Count unique values based on criteria without using array

[JCP2]

I have a large worksheet with multiple columns of arrays which slow everything down and I'd like to replace each of them with a helper column and another formula which would hopefully speed things up. I have attached an example.

Column D is the array formula which I'm trying to replace. It counts the unique values in Column A if the criteria in Column B and Column C are matched.
Array formula in Column D copied down the column:
=IF(C2="F","",SUM(IF(FREQUENCY(IF($C$2:$C$100="R",IF($B$2:$B$100=B2,MATCH($A$2:$A$100,$A$2:$A$100,0))),ROW($A$2:$A$100)-ROW($A2)+1),1)))

I was thinking that a helper formula could be used along with another function.

I have several of these arrays to replace but getting through one should allow me to finish the rest.

In the example below, the first set of 3's in Column D are generated because there are 3 unique values in Column A for which Sequence = 14466 and Flat/Round = R
For the set of 2's below that, there are 2 unique values in Column A for which Sequence = 14446 and Flat/Round = R


[TABLE="width: 413"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Seq/Size/
Shape[/TD]
[TD]Sequence[/TD]
[TD]Flat/
Round[/TD]
[TD]Number of unique Round seq/size/shape per Sequence[/TD]
[/TR]
[TR]
[TD]14466/25/20F[/TD]
[TD]14466[/TD]
[TD]F[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14466/25/20F[/TD]
[TD]14466[/TD]
[TD]F[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14466/25/20F[/TD]
[TD]14466[/TD]
[TD]F[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14466/25/20F[/TD]
[TD]14466[/TD]
[TD]F[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14466/25/20F[/TD]
[TD]14466[/TD]
[TD]F[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14466/24/20R[/TD]
[TD]14466[/TD]
[TD]R[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14466/24/20R[/TD]
[TD]14466[/TD]
[TD]R[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14466/24/20R[/TD]
[TD]14466[/TD]
[TD]R[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14466/24/20R[/TD]
[TD]14466[/TD]
[TD]R[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14466/20/17R[/TD]
[TD]14466[/TD]
[TD]R[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14466/20/17R[/TD]
[TD]14466[/TD]
[TD]R[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14466/20/17R[/TD]
[TD]14466[/TD]
[TD]R[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14466/20/17R[/TD]
[TD]14466[/TD]
[TD]R[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14466/20/16R[/TD]
[TD]14466[/TD]
[TD]R[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14466/20/16F[/TD]
[TD]14466[/TD]
[TD]F[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14466/20/16F[/TD]
[TD]14466[/TD]
[TD]F[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14466/20/16F[/TD]
[TD]14466[/TD]
[TD]F[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14446/24/19R[/TD]
[TD]14446[/TD]
[TD]R[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]14446/24/19R[/TD]
[TD]14446[/TD]
[TD]R[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]14446/24/19R[/TD]
[TD]14446[/TD]
[TD]R[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]14446/24/19F[/TD]
[TD]14446[/TD]
[TD]F[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14446/24/19F[/TD]
[TD]14446[/TD]
[TD]F[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14446/19/15R[/TD]
[TD]14446[/TD]
[TD]R[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]14446/19/15R[/TD]
[TD]14446[/TD]
[TD]R[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]14446/19/15R[/TD]
[TD]14446[/TD]
[TD]R[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]14446/19/15R[/TD]
[TD]14446[/TD]
[TD]R[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]14446/19/15F[/TD]
[TD]14446[/TD]
[TD]F[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14446/19/15F[/TD]
[TD]14446[/TD]
[TD]F[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14446/19/15F[/TD]
[TD]14446[/TD]
[TD]F[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14339/24/19R[/TD]
[TD]14339[/TD]
[TD]R[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14339/24/19F[/TD]
[TD]14339[/TD]
[TD]F[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14339/23/19R[/TD]
[TD]14339[/TD]
[TD]R[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14339/19/15R[/TD]
[TD]14339[/TD]
[TD]R[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14339/19/15R[/TD]
[TD]14339[/TD]
[TD]R[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14339/19/15R[/TD]
[TD]14339[/TD]
[TD]R[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14339/19/15F[/TD]
[TD]14339[/TD]
[TD]F[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


Thanks for the help,
Jon

 

[Domenic]

Try...

D2, copied down:

=IF(C2="R",1/COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2),"")

E2, copied down:

=IF(LEN(D2),SUMIFS($D$2:$D$100,$B$2:$B$100,B2),"")

Hope this helps!

 

[JCP2]

Perfect. Thanks for the help.

 

[Domenic]

You're very welcome!