Solved: sumproduct formula error

Hi all,
pls. i try to search bad characters and count how many there are, but something i do wrong.

I have column with names and sometimes i have bad character in name. I know exacly which they are and try to count them in whole column.
So i need formula, which will count this character in cells. Not whole content of cell, but only concrete characters.
e.g.
i search character "X", "Y", "Z"
column A content

david
johny
xenia
linux
bradZ
x
nany

Results will be 6.

my defined range: aktual_meno, aktual_pocet

I tried this (from google) but it doesnt works.

Code:

---

=SUMPRODUCT(--ISNUMBER(SEARCH("x";aktual_meno))(--ISNUMBER(SEARCH("y";aktual_meno))(--ISNUMBER(SEARCH("z";aktual_meno));aktual_pocet)

---

thx for your help

A simple looping solution
[VBA]
Option Explicit
Option Compare Text
Sub BadChars()
Dim Arr, Cel, Cnt, i
Arr = Array("x", "y", "z")
On Error Resume Next
For Each Cel In Selection.Cells
For i = 1 To Len(Cel)
If Not IsError(Application.Match(Mid(Cel, i, 1), Arr, 0)) Then Cnt = Cnt + 1
Next
Next
MsgBox Cnt
End Sub

[/VBA]

Try

=SUMPRODUCT(--(ISNUMBER(SEARCH({"X","Y","Z"},aktual_meno))))

Quote:

---

Originally Posted by mdmackillop

A simple looping solution
[vba]
Option Explicit
Option Compare Text
Sub BadChars()
Dim Arr, Cel, Cnt, i
Arr = Array("x", "y", "z")
On Error Resume Next
For Each Cel In Selection.Cells
For i = 1 To Len(Cel)
If Not IsError(Application.Match(Mid(Cel, i, 1), Arr, 0)) Then Cnt = Cnt + 1
Next
Next
MsgBox Cnt
End Sub

[/vba]

---

thx for this code, but i need it in formula if it is possible :(
but thx for your effort

Quote:

---

Originally Posted by xld

Try

=SUMPRODUCT(--(ISNUMBER(SEARCH({"X","Y","Z"},aktual_meno))))

---

this works great, but only with one character. If are there X, Y and Z...it return 0.
Maybe it works as AND, but i need count all X, all Y and all Z character.
how can i do this?
thx

sorry i know now :)
[VBA]=+SUM(SUMPRODUCT(--(ISNUMBER(SEARCH({"a"};aktual_meno))))+SUMPRODUCT(--(ISNUMBER(SEARCH({"b"};aktual_meno)))))[/VBA]

No, mine works for all 3 characters, believe me! It returns 6 on your data.

You certainly don't need the leading +, nor SUM when you + each component.

Hi Bob,
Changing the data from bradZ to bradZZZ does not increase the count to 8 as I believe is required.

Danovkos, a case of too simple an example which does not fully demonstrate the problem.

How about a UDF (user defined function)
=BADCHARS(aktual_meno)

Place this code in a standard module

[VBA]
Option Explicit
Option Compare Text
Function BadChars(Data As Range)
Dim Arr, Cel, Cnt, i
Arr = Array("x", "y", "z")
On Error Resume Next
For Each Cel In Data.Cells
For i = 1 To Len(Cel)
If Not IsError(Application.Match(Mid(Cel, i, 1), Arr, 0)) Then Cnt = Cnt + 1
Next
Next
BadChars = Cnt
End Function
[/VBA]

Quote:

---

Originally Posted by mdmackillop

Hi Bob,
Changing the data from bradZ to bradZZZ does not increase the count to 8 as I believe is required.

---

I accept that Malcolm, but the OP didn't specify that as a requirement, AND his amended formula doesn't either. My point is that he says my formula returns 0, it does not, it returns 6 on the data provided.

thx guys,
UDF works also great...
i will use this, because here can i easier define "bad character"

to xld:
i tried it again, but it doesnt works for me, really...i changed "," for ";" because it works in my regional settings only with this, but still doesnt works.

never mind

thx one more time

danovkos,

Can you post a real workbook, so I can see it in action?

BTW, with the formula you can define the 'bad' caharacters in a list, more maintainable than a UDF.

XLD this is confidentional data in many sheets what i have. So i can not to poste it.

But the most important is, that your advises helps me and solve my problem. I am thankfull for that.
So thank you very much for your help.

If you want a list of bad character in the workbook, you could either
put them in a cell as x,y,z, and name the cell , in this case BC
or
Create a range name BC and use a Formula ="x,y,z".

The code would be changed in one line to
[VBA]
Arr = Split(Range("BC"), ",")
[/VBA]

it looks, good,
ill try it
thx