Count if text meets given criteria

[NMT]

Hi,

I have a table of 3 types of information -
Column A - Query type
Column B - Resolver Name
Coulmn C - Resolution date

I would like to count the number of cases completed by person X (Stated in
column B) on a given date (Stated in Column C) if the query types includes
text "ABC" (Stated in column A)

Have tried Sumproduct ---
=Sumproduct((A2:A22="*ABC*")*(B2:B22="Nicky")*(C2:C22="1/2/2009"))
Can i use the wild card criteria? If I use a normal criteria I can count the
required data.

 

[Teethless mama]

First - you can not use wild card with SUMPRODUCT
Second - you can not use date c2:c22="1/2/2009" use like this
c2:c22=--"1/2/2009"

=SUMPRODUCT((ISNUMBER(SEARCH("ABC",A2:A22)))*(B2:B22="Nicky")*(C2:C22=--"1/2/2009"))

or

=SUMPRODUCT((ISNUMBER(SEARCH("ABC",A2:A22)))*(B2:B22="Nicky")*(C2:C22=DATE(2009,1,2)))

 

[NMT]

Hi,

I tried this formula ... it works provided I dont add the date criteria ...
if i add the date criteria the count shows 0 which is incorrect -

Entered the formula as
=SUMPRODUCT((ISNUMBER(SEARCH("Geocode",Sheet1!$A$2:$A$33)))*(Sheet1!$B$2:$B$33=E4)*(Sheet1!$C$2:$C$33=Sheet2!A5))

=SUMPRODUCT((ISNUMBER(SEARCH("Geocode",Sheet1!$A$2:$A$33)))*(Sheet1!$B$2:$B$33=D4)*(Sheet1!$C$2:$C$33=DATE(2009,6,26)))

Can you please suggest how I could add the date criteria?