Count unique values based on criteria without using array

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.

I've entered a Helper column and formula (although this can be changed if needed). For each value in the Helper column a count of unique values in Column A should be made and place in Column H.

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



I also have a second question which is separate from the question above and more for my understanding. I adjusted the array in Column D for the attached example and it didn't count correctly. How come in Column D when I use a range extending to row 100 it works but if I adjust it to the actual range of the table (to row 51) as in Column E it gives the wrong information.


Thanks for the help,
Jon

Download and install the FREE MICROSOFT PowerPivot Addin. It allows for Unique Count natively.

http://chandoo.org/wp/resources/dist...t-power-pivot/
http://blog.contextures.com/archives...l-pivot-table/
https://www.youtube.com/watch?v=1YJ0GMFm7IM

Once you have powerpivot you can solve this in seconds and wonder why Excel didnt have this BEFORE the addin.

Maybe this will be of use. I added two helper columns the second of which gives the results of the original column D. Neither of the formulae are Array formulae. What I have done is highlighted in Yellow.

Thanks for giving feedback.