hey I finally got it
=SUMPRODUCT((ISNUMBER(SEARCH("Geocode",Sheet1!$A$2 :$A$33)))*(Sheet1!$B$2:$B$33=D$4)*(Sheet1!$C$2:$C$ 33=$A5))

It was the format of the reference data ... it didn't match completly
earlier ...

Thanks alot for your help :)

"T. Valko" wrote:

Ooops! Typo:

Goto the menu DataValidation
Just click Finish

Should be:

Goto the menu DataText to Columns
Just click Finish


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this. Sometimes it will convert TEXT dates to true Excel dates...

Select the range that contains the dates
Goto the menu DataValidation
Just click Finish

--
Biff
Microsoft Excel MVP


"NMT" wrote in message
...
tried that too earlier ... changed the format as well ... but it still
doesnt
work ... the dates are currently in the mm/dd/yyyy format

"T. Valko" wrote:

Then there's a problem with your dates.

They might be TEXT entries that look like dates.

Dates are really just numbers formatted to look like dates. For example,
today is 7/8/2009. That is what is *displayed* in a cell but to Excel
that
dates true value is the number 40002.

To see this enter the date in a cell then change the cells format to
General.

So, if your range of dates is C2:C22 and every cell contains a true
Excel
date then this formula will return a result of 21:

=COUNT(C2:C22)

--
Biff
Microsoft Excel MVP


"NMT" wrote in message
...
Hi,

thanks for the input ... tried the complete cell referencing ... but
the
date criteria doesn't work ..the count calculated is incorrect

"T. Valko" wrote:

Better to use cells to hold the criteria:

E1 = ABC
F1 = Nicky
G1 = 1/2/2009

=SUMPRODUCT(--(ISNUMBER(SEARCH(E1,A2:A22))),--(B2:B22=F1),--(C2:C22=G1))

--
Biff
Microsoft Excel MVP


"NMT" wrote in message
...
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.