Re: Count if text meets given criteria


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 Data>Validation
Just click Finish

Should be:

Goto the menu Data>Text to Columns
Just click Finish


--
Biff
Microsoft Excel MVP


"T. Valko" <biffinpitt@xxxxxxxxxxx> wrote in message
news:eK5y0hAAKHA.3556@xxxxxxxxxxxxxxxxxxxxxxx
Try this. Sometimes it will convert TEXT dates to true Excel dates...

Select the range that contains the dates
Goto the menu Data>Validation
Just click Finish

--
Biff
Microsoft Excel MVP


"NMT" <NMT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EB4D8E9B-D05F-489B-9B12-3750ECD59DF8@xxxxxxxxxxxxxxxx
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" <NMT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E6B3435E-2700-4EE4-A08E-04A400C4A1F0@xxxxxxxxxxxxxxxx
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" <NMT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BAB76375-2EEC-42D5-89DC-5C537F2760FE@xxxxxxxxxxxxxxxx
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.












.

Loading