+ Reply to Thread
Results 1 to 3 of 3

COUNTIFS works on single date, but fails to count between date range

  1. #1
    Registered User
    Join Date
    05-28-2014
    Posts
    2

    COUNTIFS works on single date, but fails to count between date range

    Hi,

    I have a sheet where I am checking names (names listed in EB:EB, and specified in C5 through a list), against a date (dates listed in BD:BD, specified in C3 through a list), and finally counting the score listed in BF:BF (what I am trying to count for each staff member, in this case, a score of "10").

    Below is a snap shot of the selection criteria on the working sheet:

    Capture.PNG

    Below is a snapshot of the raw data sheet:

    Capture2.PNG

    If I run the below formula it works to count the instances of a specific score for a specific person on a specific day - perfect!:

    COUNTIFS('Medallia Amended'!EB:EB,C5,'Medallia Amended'!BD:BD,C3,'Medallia Amended'!BF:BF,10)

    However, when I try to extend this formula to count between a date range I have no luck, with only "0" being reported:

    =COUNTIFS('Medallia Amended'!EB:EB,C5, 'Medallia Amended'!BD:BD, ">=" &$C$3, 'Medallia Amended'!BD:BD,"<=" &$C$4,'Medallia Amended'!BF:BF,10)

    I have also tried a SUMPRODUCT formula, with similar results (displays #VALUE when using an array formula as below, and #NA when using a standard formula):

    =SUMPRODUCT(--('Medallia Amended'!BD4:BD100000>=Display!C3),--('Medallia Amended'!BD4:BD100000<=Display!C4),--('Medallia Amended'!EB4:EB100000=Display!C5),--('Medallia Amended'!BF4:BF100000=10))

    Any help you're able to offer would be fantastic! I have been stuck on this for days now, and have tried dozens of links on Google without luck.


    John.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,409

    Re: COUNTIFS works on single date, but fails to count between date range

    instead of a longwided story, please add an small excel file, without confidential information.

    Please also add the desired result in your file.

    You will see, you get better help on your question if you follow above advice.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    05-28-2014
    Posts
    2

    Re: COUNTIFS works on single date, but fails to count between date range

    Never mind, while building a simplified version to post I figured out I was using the correct formula, and it was the data source that was causing the error.

    Thanks anyway.

+ 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. VBA code to count the occurrence in a single date and the range of date
    By Sushil.thakur in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-28-2014, 02:28 AM
  2. [SOLVED] Count Duplicate Occurances, by date range and single date
    By Pierce Quality in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-18-2013, 03:04 PM
  3. Countifs to count rows with a date which match a range of dates
    By pharmerjoe7 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-07-2013, 06:19 AM
  4. [SOLVED] Date Filter based on Cell Input - single date or range of dates
    By babaloo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-23-2012, 10:32 AM
  5. How to count date as a criteria in countifs
    By huntersdad in forum Excel General
    Replies: 7
    Last Post: 03-29-2011, 07:38 AM

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