count string in cell

Hi All
In my excel one of column is Status in detail worksheet
This column have multi status. e.g. Color Error,Size Error,Data
Matched.

I want check how many error by type. In other sheet call Statistics,
check how many status in detail worksheet.
e.g.

Color Error
Size Error
Data Matched

I am using =COUNTIF('Item List'!T:T,A4) where T is Status Column.
Funciton Countif just handle one value in status.

Do you know which function can  handle multi status ?



0
moonhkt
11/25/2009 4:36:23 AM
excel 39879 articles. 2 followers. Follow

3 Replies
1023 Views

Similar Articles

[PageSpeed] 7

If you want one cell to count all 3 of those criteria...

One way...

=SUMPRODUCT(--(ISNUMBER(MATCH('Item List'!T1:T100,A4:A6,0))))

Note that unless you're using Excel 2007 you can't use entire columns as 
range references with SUMPRODUCT.

-- 
Biff
Microsoft Excel MVP


"moonhkt" <moonhkt@gmail.com> wrote in message 
news:ddcfc242-71b6-4dec-ad37-3016038423e7@h14g2000pri.googlegroups.com...
> Hi All
> In my excel one of column is Status in detail worksheet
> This column have multi status. e.g. Color Error,Size Error,Data
> Matched.
>
> I want check how many error by type. In other sheet call Statistics,
> check how many status in detail worksheet.
> e.g.
>
> Color Error
> Size Error
> Data Matched
>
> I am using =COUNTIF('Item List'!T:T,A4) where T is Status Column.
> Funciton Countif just handle one value in status.
>
> Do you know which function can  handle multi status ?
>
>
> 


0
T
11/25/2009 6:03:12 AM
On 11=A4=EB25=A4=E9, =A4U=A4=C82=AE=C903=A4=C0, "T. Valko" <biffinp...@comc=
ast.net> wrote:
> If you want one cell to count all 3 of those criteria...
>
> One way...
>
> =3DSUMPRODUCT(--(ISNUMBER(MATCH('Item List'!T1:T100,A4:A6,0))))
>
> Note that unless you're using Excel 2007 you can't use entire columns as
> range references with SUMPRODUCT.
>
> --
> Biff
> Microsoft Excel MVP
>
> "moonhkt" <moon...@gmail.com> wrote in message
>
> news:ddcfc242-71b6-4dec-ad37-3016038423e7@h14g2000pri.googlegroups.com...
>
>
>
> > Hi All
> > In my excel one of column is Status in detail worksheet
> > This column have multi status. e.g. Color Error,Size Error,Data
> > Matched.
>
> > I want check how many error by type. In other sheet call Statistics,
> > check how many status in detail worksheet.
> > e.g.
>
> > Color Error
> > Size Error
> > Data Matched
>
> > I am using =3DCOUNTIF('Item List'!T:T,A4) where T is Status Column.
> > Funciton Countif just handle one value in status.
>
> > Do you know which function can  handle multi status ?- =C1=F4=C2=C3=B3Q=
=A4=DE=A5=CE=A4=E5=A6r -
>
> - =C5=E3=A5=DC=B3Q=A4=DE=A5=CE=A4=E5=A6r -

Hi All

I am using Excel 2003.

What is meaning "--" ?

The sum of Other Problem , Test Error and Color Error should be 6

=3DSUMPRODUCT(--(ISNUMBER(MATCH($A$2:$A$7,$B$2:$B$4,0))))

Error	                             Summary
Color Error	             Other Problem	4
Test Error 	             Test Error
Other Problem	             Color Error
Other Problem,Test Error
Test Error
X



0
moonhkt
11/25/2009 7:44:35 AM
The double minus -- simply forces Excel to take a text value and treat 
it as a real number (one minus makes it think "hmmm... a sum to do, I'll 
treat this like a number and return a number", the second one just 
reverses the negative back to a positive or vice versa).

One of those 'standard tricks' once you know it. There are other ways 
but this seems to be preferred by many
0
AdamV
11/25/2009 9:37:42 AM
Reply:

Similar Artilces: