+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    Registered User
    Join Date
    02-20-2010
    Location
    hobart, Australia
    MS-Off Ver
    Excel 2007
    Posts
    8

    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"

    I have tried everything so please help me on this formula...

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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