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.

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:B 22="Nicky")*(C2:C22=--"1/2/2009"))

or

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



"NMT" wrote:

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.

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.

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$3 3=Sheet2!A5))

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

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

"Teethless mama" wrote:

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:B 22="Nicky")*(C2:C22=--"1/2/2009"))

or

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



"NMT" wrote:

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.

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.

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.

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.

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.

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.

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.

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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