Sumproduct for 3 Criteria (between 2 dates, plus a matching text entry other cell)

Hi All,

Long time reader, first time poster - I really hope you guys can help!

I'm setting up an excel tracker that looks for the date in another tab and checks it is between 2 dates. Sounds simple enough at the moment - for this i'm using the following formula:

=SUMPRODUCT(--('It Drives Me Crazy'!$C$3:$C$65000>=D2*1),--('It Drives Me Crazy'!$C$3:$C$65000<=D3*1))

(The starting date is in cell D2 and end date for range I'm looking for is in D3)

The next part is where I'm hitting a problem. I want to do the same search, but add on a criteria which does the same search, but also needs to match the site I'm specifying in another column of the tab I'm referencing. For example, in the "It Drives Me Crazy" tab, I want to find all Cardiff site records in my date range. I've tried using SUMPRODUCT, SUMIFS, COUNTIFS, everything but can't get it to work.

At the moment I'm trying this formula:

=SUMPRODUCT(--('It Drives Me Crazy'!$C$3:$C$65000>=D2*1),--('It Drives Me Crazy'!$C$3:$C$65000<=D3*1),--('It Drives Me Crazy'!G3:G7="Cardiff"))

I'm not returning anything in the cell at the moment other than ###

Can anyone suggest how to fix my formula?

Thanks in advance

[QUOTE=Lemice;3204712]One solution to this is to change the reference of the last criteria

Please Login or Register  to view this content.

Lemice - Thank you so much! This has worked and I'm now starting to question why I hadn't seen this myself. I didn't realise that the range for the columns needed to be the same. Nice tip too about using the $B5 reference - I've been doing a find & replace after copying the forumla to update the sites I'm looking for.

+Rep - thanks again