Wildcard replacement when using SUMPRODUCT

Helen842000

New Member
Joined
Mar 28, 2011
Messages
34
Hi All,

I'm having an issue with SUMPRODUCT :confused: 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!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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))))
 
Upvote 0
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...
 
Upvote 0
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! :biggrin:

Thanks guys. I'm very grateful for your suggestions. Super fast replies too!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top