Searching for exact term with date range

Hi All

Hoping you can help, I'm annoyingly close!

I've got the following formula:

=SUMPRODUCT((Source!$A$2:$A$5000>calculation!$A$2)*(Source!$A$2:$A$5000<=calculation!$A$4)*(ISNUMBER(SEARCH(calculation!B262,Source!$B$2:$B$5000))))

I have the dates in cells A2 and A4 on the 'calculation sheet' and I want to search for the for a term (B262 on the calculation sheet in this case) in cells B2-B5000 in the 'source' sheet. I'm trying to match up names but the part where it's going wrong is where I am looking for the surname 'Na' it's counting every name that includes 'Na' such as 'Donald'.

In the Source sheet I have dates in column A and the names in B. In the calculation sheet I have the names I'm searching for in column A.

Sorry if this is confusing but I can give more info if needed?

Thanks in advance

Can anyone help?

try use FIND instead of SEARCH. It's case sensitive.

Thanks for the quick reply, here you go...

Originally Posted by zbor

try use FIND instead of SEARCH. It's case sensitive.

FIND would work except that in my sheet there's a mix of full names and surnames only. For example, I'm looking for all cells that contain 'Na' only but if there was a 'Steve Na' this would also be picked up

How about this:


=SUMPRODUCT(--(Source!$B$2:$B$1000=calculation!A7), --(Source!$A$2:$A$1000>=$A$2), --(Source!$A$2:$A$1000<$A$4))

Originally Posted by zbor

How about this:


=SUMPRODUCT(--(Source!$B$2:$B$1000=calculation!A7), --(Source!$A$2:$A$1000>=$A$2), --(Source!$A$2:$A$1000<$A$4))

We have a winner. Thanks guys.

Originally Posted by Domski

Can't open your example on this pc but you should be able to use ISNUMBER/MATCH if you are looking to count the whole contents of a cell rather than part of it.

Dom

Strangely enough I did try MATCH but just couldn't get it to work. I'm sure it was something simple but nevertheless I've got a working formula now. Thanks