Sum.product with text conditionals

[Jacek Zagaja]

Combobox1 stores unique list of items from data sheet. User clicks on
command button and VB macro prints selected item to the cell. Now with
SUM.PRODUCT I'd like to find selected item in "Item" column and sum
all its values marked KK stored in another column (offset):

=SUM.PRODUCT((Item="selected item")*(Value))

It works nicely but I need to subtract values marked MM that is a
substring of e.g.. 00/11/22/MM/333 II so I added third conditional
(multiplication) that search in KKMM column for MM substring:

TextSearch("MM";KKMM)=True()

It doesn't work so that I'm getting an empty cell. Any ideas?

--
Function TextSearch(ByVal strSearchFor, ByVal Target As Variant) As
Boolean
Dim OneCell As Range

TextSearch = False
If TypeName(Target) = "Range" Then
For Each OneCell In Target
If InStr(1, OneCell.Text, strSearchFor, vbTextCompare) > 0 Then
TextSearch = True
Exit For
End If
Next OneCell
ElseIf TypeName(Target) = "String" Then
If InStr(1, Target, strSearchFor, vbTextCompare) > 0 Then
TextSearch = True
End If

End Function

 

[Jacek Zagaja]

Empty cell appears regardless of "Text Search" routine so it looks
like a major error.

=SUMPRODUCT((ISNUMBER(SEARCH({"MM","mm"}, KKMM)))*Value)

 

[Shane Devenshire]

Hi,

Show us an example of some data and the result you expect. Right now if I
modify your formula to

=SUMPRODUCT(ISNUMBER(SEARCH("mm", G9:G11))*H9:H11)

and H9:H11 contains values and G9:G11 contains 00/11/22/MM/333 II this
formula works. However, {"mm","MM"} will double count!

If this helps, please click the Yes button,

Cheers,
Shane Devenshire

 

[Jacek Zagaja]

Shane,

Thanks a lot. I know what was wrong. I'm using regionalized Excel and
have used wrong 'Search' function. Next task is to built Combobox with
a list of conditional unique values. Conditions e.g. units are stored
in an offset columns:

http://groups.google.pl/group/excel-macros/browse_thread/thread/54a35db508b0171d?hl=pl

BR,
Jack

 

[Jacek Zagaja]

Shane,

Could you help me with conditionals?

I'd like to execute <code> if Array2 and Array3 contains specific text
string. In Excel it would be And Array2= {"KK","MM"}. Now placing Or
operator makes Array1 condition untrue.

If a=b And Array1(i) = "text1" And Array2(i) = "KK" Or Array3(i) =
"MM" Then <code>

Regards,
Jack