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.

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.