Solved: sumproduct formula error

[danovkos]

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.

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

thx for your help

[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]

[Bob Phillips]

Try

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

[danovkos]

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

[danovkos]

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

[danovkos]

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

[Bob Phillips]

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.

[mdmackillop]

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]

[Bob Phillips]

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.

[danovkos]

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

[Bob Phillips]

danovkos,

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

[Bob Phillips]

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

[danovkos]

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.

[mdmackillop]

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]

[danovkos]

it looks, good,
ill try it
thx