ISNUMBER with multiple criteria

Hi

this is my first time posting so fingers crossed. I have a formula which counts a number of entries which have been inputted within a date range and match a criteria.
=SUMPRODUCT(($G$21:$G$1501>=$A$1502)*($G$21:$G$1501<=$A$1503)*(ISNUMBER(SEARCH("Clinic",$H$21:$H$1501))))

A1502 & A1503 contain the date range
rows 21 - 1501 contain the data

this formula work fine and counts the number of "clinic" entries within the date range but when I try to add another criteria it goes wrong.
I wish to search for the word "incomplete" within column K but I have tried several times using AND and I either get a 0 or #Value

help!!

Sharon

You don't say how you wish to apply that criteria, but try this:

=SUMPRODUCT(($G$21:$G$1501>=$A$1502)*($G$21:$G$1501<=$A$1503)*(ISNUMBER(SEARCH("Clinic",$H$21:$H$1501)*(ISNA(SEARCH("incomplete",$K$21:$K$1501))))

This will exclude from the count any values in column K that contain the word incomplete.

Hope this helps.

Pete

thanks so much - works brilliant

thanks Pete - all good

Well, it's either ISNUMBER or ISNA in the end parameter, depending on whether you want to include or exclude rows with "incomplete" in column K.

Pete