Count the unique items in column F if E:E = "Chris" and M:M = "VIC" and N:N = "PT"

1. Count the unique items in column F if E:E = "Chris" and M:M = "VIC" and N:N = "PT"

Hi All,

I have a massive database and need to do a count on all unique items in column F, only if 3 conditions are ALL met.

ie.

Count the unique items in column F if E:E = "Chris" and M:M = "VIC" and N:N = "PT"

2. Re: Count the unique items in column F if E:E = "Chris" and M:M = "VIC" and N:N = "PT

How "massive" is "massive" ?

These types of calcs are generally quite expensive - if you try to run them over entire columns (not possible pre XL2007 for good reason) you will notice some severe lag / crash

There are generally speaking a couple of approaches - ie a SUMPRODUCT approach and also a Frequency Array

The Frequency Array might make more sense here

=SUM(IF(FREQUENCY(IF((\$E\$1:\$E\$1000="Chris")*(\$M\$1:\$M\$1000="Vic")*(\$N\$1:\$N\$1000="PT"),MATCH(\$F\$1:\$F\$1000,\$F\$1:\$F\$1000,0)),ROW(\$F\$1:\$F\$1000)-ROW(\$F\$1)+1)>0,1))
confirmed with CTRL + SHIFT + ENTER
(Enter alone will not suffice)

For sake of demo note shortened range references, adjust as you see fit.

(you might want to consider using dynamic named ranges - prob. created with INDEX rather than OFFSET to avoid Volatility)

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1