Count distinct values based on criteria (large data)

Hi,

It's unfortunate that the reciprocal COUNTIF(S) construction for counting unique values is so widely touted across the various Excel help sites/forums, since it is vastly inefficient.

Far better is the FREQUENCY/MATCH set-up, i.e., array formula**:

=SUM(IF(FREQUENCY(IF(Brio[Sales Year]=$G$1,IF(Brio[Sales Month]=$G$2,IF(Brio[Sales Date]<=G3,MATCH(Brio[Invoice No],Brio[Invoice No],0)))),ROW(Brio[Invoice No])-MIN(ROW(Brio[Invoice No]))+1),1))

Also, I notice that you were referencing entire columns within your formula, which is not at all a good idea: array formulas calculate over all cells passed to them, whether technically beyond the last-used cells in those ranges or not.

Besides, you appear to be using a Structured Table here, which will automatically expand/contract as new rows are added/deleted, so simply by using Structured References within your formula, as I have done above, you avoid the need to manually set the upper row reference.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Did you read the part about Structured References/Tables in my previous post?

Regards

Pivot table with data model for distinct count

http://www.filedropper.com/countdist...tabledatamodel


Tutorial

https://www.youtube.com/watch?v=sId8vpdp1qw



Saluti XOR LX

Buona alternativa, CARACALLA. Inoltre, sarą pił efficiente.

Spero che stia bene li a Milano. Tempi difficili qui al nord...

Saluti,