Wildcard replacement when using SUMPRODUCT

[Helen842000]

Hi All,

I'm having an issue with SUMPRODUCT I would normally use a wildcard symbol in this instance but I know that they don't work with SUMPRODUCT. I think I need to use LEFT function in there somewhere but I'm not sure of the syntax.

I want to search a data range from another worksheet for the number of rows that meet 3 pieces of set criteria. I am searching the March worksheet, Column/names - B (System Name), C (Employee ID), D (Action). The data range goes from B4 to D4200.

How can I change my formula so that it finds all the instances of the text typed even if they are parts of longer strings of text and NOT exact/literal matches?

This is what it would be like if I could use wildcard characters:-

=SUMPRODUCT((March!B4:B4200="*RBS*")*(March!C4:C4200="12345A")*(March!D4:D4200="*New*"))

This doesn't work obviously and if I remove the wildcard characters it returns the value of zero as it tries to count the literal strings.

I can see that it works correctly if I put the full system name or full action name as in this example :-

SUMPRODUCT((March!B4:B4200="RBS Read only")*(March!C4:C4200="11655B")*(March!D4:D4200="New user set-up"))

but there are SO many possible systems names/action types in the data range it really needs to be counted by the first 3 or 4 letters of the system name/action type.

If anyone can shed any light on this I'd appreciate it very much!
Thanks!

 

[Mike Blackman]

Hi,

Maybe something like this?

=SUMPRODUCT((ISNUMBER(SEARCH("RBS",March!B4:B4200)))*(March!C4:C4200="12345A")*(ISNUMBER(SEARCH("New",March!D4:D4200))))

or

=SUMPRODUCT(--(ISNUMBER(SEARCH("RBS",March!B4:B4200))),--(March!C4:C4200="12345A"),--(ISNUMBER(SEARCH("New",March!D4:D4200))))

 

[DauntingGecko]

Seems like your data is in a bit of a state.

Add a new column that categorises your data (rather than free type text) and then use sumproduct on your new column...

 

[mikerickson]

This
=SUMPRODUCT(--(LEFT(B1:B7,3)="cat"),--(LEFT(C1:C7,3)="dog"),--ISNUMBER(FIND("fish",D1:D7))

looks for "cat*" , "dog*", "*fish*"

 

[Helen842000]

Hi Mike,

Thank you so much! They both work brilliantly!!

That's such a great help.

You've made my day!

 

[Helen842000]

Yes, the data is in a very big mess! Unfortunately it comes from an auto-generated report that I don't have access to . I tried to add a sub-category column but there are so many exceptions it became a monster formula. The data is just exported by someone else and then I have to make sense of it! I tried to suggest we export the data in a new/improved/filtered format but looks like I've gotta stick with it as it is.

Least I'm getting the numbers that are right now!

Thanks guys. I'm very grateful for your suggestions. Super fast replies too!

 

[FuzzyTom]

This
=SUMPRODUCT(--(LEFT(B1:B7,3)="cat"),--(LEFT(C1:C7,3)="dog"),--ISNUMBER(FIND("fish",D1:D7))

looks for "cat*" , "dog*", "*fish*"

I was wondering if you could give me some direction how to adapt this to my situation in VBA.

I have a variable ClName which is assigned to the tab name and I would like to check if this tab name, or ClName has an extension "ccs". So I did the following:

If TabName.Formula = Evaluate("=Sumproduct(--(Right(TabName,3 "=" ""ccs"")))") = True Then
NewSheet.Tab.Color = 32
Else
NewSheet.Tab.Color = 128
Endif

I get invalid qualifier ... so I tried the same argument without formula and of course it is just ignored.

Any suggestions ... thanks.

 

[mikerickson]

In VBA you have the Like operator

If ClName Like "*.ccs" Then
    MsgBox "ClName ends in .ccs"
End If

 

[FuzzyTom]

Thanks ... this works just great.