+ Reply to Thread
Results 1 to 2 of 2

If Frequency Formula count based on date is off by 1

  1. #1
    Registered User
    Join Date
    04-02-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    97

    If Frequency Formula count based on date is off by 1

    Please see attached sample.

    In column L, I have an array that is counting the distinct numbers in column C for for the specific Month and Year in Column A. For some reason, for the months that don't start in row A2, in this case March and April, the count are off by 1. In summary, Feb 2015 count is spot on, but then March and April are both 1 too many.

    Any ideas on what may be causing this?

    Thanks in advance for your time.
    Attached Files Attached Files
    Last edited by antexity; 05-07-2015 at 04:11 PM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: If Frequency Formula count based on date is off by 1

    I think that you will be counting blanks as zero values in some circumstances, try this revised version

    =SUM(IF(FREQUENCY(IF(MONTH($A$2:$A$25000)=MONTH(A2),IF(YEAR($A$2:$A$25000)=YEAR(A2),IF($C$2:$C$25000<>"",$C$2:$C$25000))),$C$2:$C$25000)>0,1))

    or you can shorten the MONTH/YEAR parts like this for the same results:

    =SUM(IF(FREQUENCY(IF($A$2:$A$25000-DAY($A$2:$A$25000)=A2-DAY(A2),IF($C$2:$C$25000<>"",$C$2:$C$25000)),$C$2:$C$25000),1))
    Audere est facere

+ 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. Replies: 13
    Last Post: 04-19-2015, 11:51 PM
  2. Frequency count for rolling current date
    By K Rose in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-15-2015, 10:28 PM
  3. Dynamic Count on Running Date
    By K Rose in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 04-14-2015, 08:55 AM
  4. Calculate next due date based on start date frequency and current date
    By ironoverload in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-09-2014, 07:08 AM
  5. Replies: 0
    Last Post: 12-27-2005, 06:10 PM

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