PDA

View Full Version : Solved: sumproduct formula error



danovkos
08-12-2009, 02:21 AM
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
08-12-2009, 04:00 AM
A simple looping solution

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

Bob Phillips
08-12-2009, 04:27 AM
Try

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

danovkos
08-12-2009, 06:57 AM
A simple looping solution

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



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

danovkos
08-12-2009, 07:01 AM
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
08-12-2009, 07:03 AM
sorry i know now :)
=+SUM(SUMPRODUCT(--(ISNUMBER(SEARCH({"a"};aktual_meno))))+SUMPRODUCT(--(ISNUMBER(SEARCH({"b"};aktual_meno)))))

Bob Phillips
08-12-2009, 07:23 AM
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
08-12-2009, 11:48 PM
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


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

Bob Phillips
08-13-2009, 01:05 AM
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
08-13-2009, 11:32 PM
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
08-14-2009, 01:57 AM
danovkos,

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

Bob Phillips
08-14-2009, 01:57 AM
BTW, with the formula you can define the 'bad' caharacters in a list, more maintainable than a UDF.

danovkos
08-14-2009, 02:27 AM
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
08-14-2009, 05:13 AM
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

Arr = Split(Range("BC"), ",")

danovkos
08-14-2009, 05:26 AM
it looks, good,
ill try it
thx