Sumif wildcard

[Renevatia]

Hi, new here. Been scratching my head around this for while.

I am currently using a =sum(sumif(A1:A10, {"*text1*","*text2*","*text3*"}, B1:B10)) formula to replace a really messy =sum(vlookup^n)
=sumif(A1:A10, C1, B1:B10) would work with a reference but I cannot throw in multiple references.
I am wondering if there is any way I can use cell reference for the criteria array for the sumif function. Everything I've tried so far seems to throw syntax error. Only thing that works is explicit text.
Since array is throwing wildcard kind of like a query is there a way for me to toss an OR statement in there to concatenate the entire criteria?

Are there any syntax that would support the following structures:

=sumif(A1:A10, C1, B1:B10)
where C1.text = *text1*|*text2*|*text3*

or

=sum(sumif(A1:A10, {C1, C2, C3}, B1:B10))
where C#.text = *text#*

I know I could simply hard code the criteria in or use multiple sumif or sumifs with cell reference, but that may still end up too messy and I would not be able to use dynamic range / range sizes for this method.
The only other solution I can think of is to bandage this with is a macro to populate the formulas.

plssendhelp

 

[Aladin Akyurek]

Re: Sumif wildcard help

I did not use the UDF from post #17 . I started building it after I failed to run the formula on post #13 . I had to go to work before I could check forum/attempted again. I figured if I just apply the logic in UDF it would be easier to manage as well as the benefit of having regex since I had to knock it out this morning, or apply the old solution of a crap ton of vlookups. To be honest I forgot about applying UDF until MMULT was mentioned. Sorry, I don't mean to gloss over any solutions available on the thread if that is what I seem to come across as.

Hier is the set up (described in post #16 ) in an xls workbook:

https://www.dropbox.com/s/jdorkmke69v4ruk/Renevatia%202010%20set%20up.xls?dl=0

I would like to know whether it works with your 2010 system. Thanks.

 

[Renevatia]

Re: Sumif wildcard help

Hier is the set up (described in post #16 ) in an xls workbook:

https://www.dropbox.com/s/jdorkmke69v4ruk/Renevatia 2010 set up.xls?dl=0

I would like to know whether it works with your 2010 system. Thanks.

I am afraid I don't understand how to use advance criteria filters it seems

I tried to feed the criteria into the the cell as ="=..." pcriteria is saying that "#" part is erroring. Though I don't really even understand how the advance criteria work at a basic level.

 

[Aladin Akyurek]

Re: Sumif wildcard help

I am afraid I don't understand how to use advance criteria filters it seems

I tried to feed the criteria into the the cell as ="=..." pcriteria is saying that "#" part is erroring. Though I don't really even understand how the advance criteria work at a basic level.

Can you open the file on your system and see the the results of the formulas in H6:H8?

 

[Renevatia]

Re: Sumif wildcard help

Can you open the file on your system and see the the results of the formulas in H6:H8?

Yes.
Here are some results
*one = 10, 10, 0
<>Art, *one = 55, 14, 41
* = 55, 55, 0

 

[Aladin Akyurek]

Re: Sumif wildcard help

Thanks for coming back.

When H1 is *one and H2:H4 empty, we should get:


H6 >> 55

H7 >> 10

H8 >> 45, which is the end result.


When H1 is <>Art, H2 *one, and H3:H4 empty, we should get:

H6 >> 55

H7 >> 4

H8 >> 51, which is the end result.


When H1 is * and H2:H4 empty, we should get:

H6 >> 55

H7 >> 55

H8 >> 0, which is the end result.


The last result is of course incorrect. I guess the formula of H8 should probably be:


=IF(H6=H7,H6,H6-H7)

instead of just

=H6-H7


But, the results that you get for the first two cases makes me think there is still a nesting levels problem on the 2010 system.