# Searching for exact term with date range

1. ## 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?

Can anyone help?

2. ## Re: Searching for exact term with date range

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

3. ## Re: Searching for exact term with date range

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

4. ## Re: Searching for exact term with date range

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

5. ## Re: Searching for exact term with date range

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

6. ## Re: Searching for exact term with date range

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

7. ## Re: Searching for exact term with date range

Originally Posted by zbor

=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.

8. ## Re: Searching for exact term with date range

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

9. ## Re: Searching for exact term with date range

Originally Posted by zbor
=SUMPRODUCT(--(Source!\$B\$2:\$B\$1000=calculation!A7), --(Source!\$A\$2:\$A\$1000>=\$A\$2), --(Source!\$A\$2:\$A\$1000<\$A\$4))
And for the record if for whatever reason you needed a case sensitive version of the same (eg Na <> na) you can use Exact:

``Please Login or Register  to view this content.``

10. ## Re: Searching for exact term with date range

Originally Posted by 97hills
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
I was misunderstanding the issue I think.

Dom

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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