Count unique values against multiple lookups

[gruntingmonkey]

Hello,
I am looking for a formula that enables me to count the unique values in Column A (Reference) against the Institution and year (Columns B & C). I might be able to remove the Year section if this causes an issue.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[gruntingmonkey]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks, I have done that now. 2016 currently.

 

[Fluff]

Thanks for that.How about

=SUM(--(FREQUENCY(IF($B$3:$B$100=$F3,IF($C$3:$C$100=G$2,MATCH($A$3:$A$100,$A$3:$A$100,0))),ROW($A$3:$A$100)-ROW($A$3)+1)>0))

this needs to be confirmed with Ctrl Shift Enter, rather than just Enter.

 

[gruntingmonkey]

Thanks for this Fluff, it absolutely works, however, I have two columns of array formulas and 40k lines of data which is taking over 10-12 hours to compute. I was wondering if there was a way I could translate this into VBA for a swifter response? Or maybe add helper columns?

 

[Fluff]

It could be done with VBA, but it would not calculate automatically. However as that's a different question, you would need to start a new thread for it.