More efficient than counting using SUMPRODUCT (date criteria)

[kcmuppet]

Hi all,

I've got a 20,000+ row table in which I'm trying to add a column that keeps a count with criteria, but my COUNTIFS attempt didn't work with Years of dates in the criteria...

=COUNTIFS(YEAR([Visit Date]),YEAR([@[Visit Date]]),[Visit Type],"Initial",[Patient],[@Patient])

...so I used the following sumproduct, but its not efficient (i.e. causes excel to run slowly). Is there a way to use something that will run more quickly?

=SUMPRODUCT(--(YEAR([Visit Date])=YEAR([@[Visit Date]])),--([Visit Type]="Initial"),--([Patient]=[@Patient]))

 

[RoryA]

You could try something like:

=COUNTIFS([Visit Date],">="&DATE(YEAR([@[Visit Date]]),1,1),[Visit Date],"<="&DATE(YEAR([@[Visit Date]]),12,31),[Visit Type],"Initial",[Patient],[@Patient])

but as soon as you get into multiple criteria you are going to see some performance hit, especially if you have a large table.

 

[kcmuppet]

You could try something like:

=COUNTIFS([Visit Date],">="&DATE(YEAR([@[Visit Date]]),1,1),[Visit Date],"<="&DATE(YEAR([@[Visit Date]]),12,31),[Visit Type],"Initial",[Patient],[@Patient])

but as soon as you get into multiple criteria you are going to see some performance hit, especially if you have a large table.

Thanks this is considerably quicker