+ Reply to Thread
Results 1 to 4 of 4

SUMIF array formula to count unique values after a certain date

  1. #1
    Registered User
    Join Date
    01-14-2014
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Excel 2013
    Posts
    21

    SUMIF array formula to count unique values after a certain date

    Hi,

    So I need to count the unique entries in column D but only after 1st August 2013.

    The formula I have come up with is:

    =SUM(IF(FREQUENCY(IF(Projects!$U$1:$U$1328 >=1/8/2013, IF(Projects!$D$1:$D$1328<>"",MATCH(Projects!$D$1:$D$1328,Projects!$D$1:$D$1328,0))),ROW(Projects!$D$1:$D$1328)-ROW(Projects!$D$1)+1),1))

    But for some reason this is counting ALL the unique values in column D regardless of date. Can anyone tell me what I have done wrong?

    Thanks!

    Mish

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUMIF array formula to count unique values after a certain date

    Use a cell to hold the date then refer to that cell:

    A1 = 1/8/2013

    =SUM(IF(FREQUENCY(IF(Projects!$U$1:$U$1328>=A1,IF(Projects!$D$1:$D$1328<>"",MATCH(Projects!$D$1:$D$1328,Projects!$D$1:$D$1328,0))),ROW(Projects!$D$1:$D$1328)-ROW(Projects!$D$1)+1),1))

    Still array entered!

    The way you had it, Excel evaluated it as >= 1 divided by 8 divided by 2013 or 0.0000620963735717834.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    01-14-2014
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: SUMIF array formula to count unique values after a certain date

    Brilliant! Thanks!

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUMIF array formula to count unique values after a certain date

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Count unique values based on criteria but without array formula (CSE)
    By Villalobos in forum Excel General
    Replies: 3
    Last Post: 05-09-2015, 05:05 AM
  2. [SOLVED] Count unique values based on criteria without using array
    By jonpaulson in forum Excel General
    Replies: 14
    Last Post: 10-10-2014, 06:30 PM
  3. Count Unique Values With Multiple Conditions, Array Method
    By 5150 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-28-2014, 04:37 PM
  4. [SOLVED] Excel VBA Code Instead of SumIf Array Formula for Unique Values
    By excelforumkeys in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-12-2014, 03:39 PM
  5. [SOLVED] Count unique values in list but NOT using ARRAY formula
    By alx0101 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-15-2013, 08:15 AM
  6. Extract unique values and their count from a two dimensional Array
    By shahirslmn in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-17-2012, 09:37 PM
  7. Can an Array formula count unique values and report no. of times for each?
    By Father Guido in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-21-2005, 02:06 AM

Tags for this Thread

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