count string in cell

  • Thread starter Thread starter moonhkt
  • Start date Start date
M

moonhkt

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 ?
 
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.
 
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










- Åã¥Ü³Q¤Þ¥Î¤å¦r -
Click to expand...

Hi All

I am using Excel 2003.

What is meaning "--" ?

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

=SUMPRODUCT(--(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
 
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
 
Back
Top