Results 1 to 5 of 5

Thread: Count Unique Dates in Date Range which also meet another criteria

  1. #1

    Count Unique Dates in Date Range which also meet another criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello:

    I have a data table with multiple columns, including a date column. The dates span several years and some dates occur multiple times. I need a formula that returns the number of unique dates in a given date range that also meet another criteria.
    For example, if Column A is Date and Column B is Color, I want to count the number of days in 2014 when the Color was Red.

    Thanks in advance.

    Gary

  2. #2
    Try this array formula

    =COUNT(1/FREQUENCY(IF($B$2:$B$100="Red",IF($A$2:$A$100<>"",$A$2:$A$100)),IF($B$2:$B$100="Red",IF($A$2:$A$100<>"",$A$2:$A$100))))

  3. #3
    Thank you for the quick reply. I'm probably just being dense, but don't see how this formula limits the range of dates to which the calculation applies. My table goes from 2004 to current. The formulae (one for each year) will keep a running count of the number of days for each year on which the designated criteria (Red) occurs. So within the Date column I need to limit to, say, 2014, and the count the number of days where Red occurred.

    My confusion is in the <>"" parts of the formula. I'm not sure if that is to exclude empty cells (I have none) or if the limiting dates are to be entered in the ""; in which case I don't understand why <> appears twice rather than >and then <.

    (I'm a rookie to these forums, so, again, perhaps I'm just misunderstanding. I understand array functions just well enough to be dangerous).

  4. #4
    You didn't mention that you want a specific year, but you just need to add another condition

    =COUNT(1/FREQUENCY(IF((YEAR($A$2:$A$100)=2014)*($B$2:$B$100="Red"),IF($A$2:$A$100<>"",$A$2:$A$100)),IF((YEAR($A$2:$A$100)=2014)*($B$2:$B$100="Red"),IF($A$2:$A$100<>"",$A$2:$A$100))))

  5. #5
    Got it. Success. Thanks so much.

Posting Permissions

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