Sumif wildcard

Renevatia · May 26, 2018

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

Renevatia · May 27, 2018

Re: Sumif wildcard help

Domenic said:
Can you please elaborate, and include some sample data along with the expected result?

With the same sample data
=SUM(IF(MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(H1:H4),A2:A14)),1,0),ROW(H1:H4)^0)>0,D2:D14))

When H1 = "Labels", H2 = "Label*", H3 = "Null" it should and does return a value of 2
When H1 = "*one" H2:H4 = "Null" it should return 0 but it returns 10
When H1 = "<>*label*", H2:H4 = "Null" it should return 53, but the formula returns 0
When H1 = "<>*one", H2:H4 = "Null" it should return 45, but it returns 0
When H1 = "rt", H2 = "one", H3:H4 = "Null" it should return 0, but it returns 2

Aladin Akyurek · May 28, 2018

Re: Sumif wildcard help

Renevatia said:
With the same sample data
=SUM(IF(MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(H1:H4),A2:A14)),1,0),ROW(H1:H4)^0)>0,D2:D14))

When H1 = "Labels", H2 = "Label*", H3 = "Null" it should and does return a value of 2
When H1 = "*one" H2:H4 = "Null" it should return 0 but it returns 10
When H1 = "<>*label*", H2:H4 = "Null" it should return 53, but the formula returns 0
When H1 = "<>*one", H2:H4 = "Null" it should return 45, but it returns 0
When H1 = "rt", H2 = "one", H3:H4 = "Null" it should return 0, but it returns 2

Given the sample of post #5:

For H1 = Labels, H2 = Label*, H3:H4 empty, we should expect: 2

For H1 = *one, H2:H4 empty, we should expect: 10

For H1 = <>*label, H2:H4 empty, we should expect: 53

For H1 = <>*one, H2:H4 empty, we should expect: 45

For H1 = rt, H2 = one, H3:H4, we should expect: 14

The formula Domenic suggests, tweaked regarding the criteria range [1]...

{=SUM(IF(MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(IF(H1:H4="","#",IF(LEFT(H1:H4,2)="<>","?*",H1:H4))),A2:A14)),1,0),ROW(H1:H4)^0)>0,D2:D14))}

will correctly sum if the criteria range does not contain any negation, i.e. <>.

We can calculate the sum corresponding to the negations [2]...

{=SUM(SUMIFS(D2:D14,A2:A14,IF(LEFT(H1:H4,2)="<>","*"&REPLACE(H1:H4,1,2,"")&"*",H1:H4)))}

The foregoing sum can then be substracted from [1]…

{=SUM(IF(MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(IF(H1:H4="","#",IF(LEFT(H1:H4,2)="<>","?*",H1:H4))),A2:A14)),1,0),ROW(H1:H4)^0)>0,D2:D14))-SUM(SUMIFS(D2:D14,A2:A14,IF(LEFT(H1:H4,2)="<>","*"&REPLACE(H1:H4,1,2,"")&"*",H1:H4)))}

Hope this helps.

Renevatia · May 28, 2018

Re: Sumif wildcard help

Aladin Akyurek said:
Given the sample of post #5 :

For H1 = Labels, H2 = Label*, H3:H4 empty, we should expect: 2

For H1 = *one, H2:H4 empty, we should expect: 10

For H1 = <>*label, H2:H4 empty, we should expect: 53

For H1 = <>*one, H2:H4 empty, we should expect: 45

For H1 = rt, H2 = one, H3:H4, we should expect: 14

The formula Domenic suggests, tweaked regarding the criteria range [1]...

{=SUM(IF(MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(IF(H1:H4="","#",IF(LEFT(H1:H4,2)="<>","?*",H1:H4))),A2:A14)),1,0),ROW(H1:H4)^0)>0,D2:D14))}

will correctly sum if the criteria range does not contain any negation, i.e. <>.

We can calculate the sum corresponding to the negations [2]...

{=SUM(SUMIFS(D2:D14,A2:A14,IF(LEFT(H1:H4,2)="<>","*"&REPLACE(H1:H4,1,2,"")&"*",H1:H4)))}

The foregoing sum can then be substracted from [1]…

{=SUM(IF(MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(IF(H1:H4="","#",IF(LEFT(H1:H4,2)="<>","?*",H1:H4))),A2:A14)),1,0),ROW(H1:H4)^0)>0,D2:D14))-SUM(SUMIFS(D2:D14,A2:A14,IF(LEFT(H1:H4,2)="<>","*"&REPLACE(H1:H4,1,2,"")&"*",H1:H4)))}

Hope this helps.

I've ran into nesting level limit with that last one, but if I am reading this correctly the sumifs to forego the negation would end up double counting the unions of 2 negations, effectively double subtracting things that matches an entry twice.

Aladin Akyurek · May 28, 2018

Re: Sumif wildcard help

Renevatia said:
I've ran into nesting level limit with that last one, but if I am reading this correctly the sumifs to forego the negation would end up double counting the unions of 2 negations, effectively double subtracting things that matches an entry twice.

What version of Excel are you running?

Renevatia · May 28, 2018

Re: Sumif wildcard help

Aladin Akyurek said:
What version of Excel are you running?

running excel 2010 saving as xls /xlsm

Aladin Akyurek · May 29, 2018

Re: Sumif wildcard help

Renevatia said:
I've ran into nesting level limit with that last one, but if I am reading this correctly the sumifs to forego the negation would end up double counting the unions of 2 negations, effectively double subtracting things that matches an entry twice.

The SUMIFS can be replaced with a similar MMULT bit...

Renevatia said:
running excel 2010 saving as xls /xlsm

Let's define Pcriteria as referring to:

=TRANSPOSE(IF(Sheet1!$H$1:$H$4="","#",IF(LEFT(Sheet1!$H$1:$H$4,2)="<>","?*",Sheet1!$H$1:$H$4)))

and define Ncriteria as referring to:

=TRANSPOSE(IF(LEFT(Sheet1!$H$1:$H$4,2)="<>","*"&REPLACE(Sheet1!$H$1:$H$4,1,2,"")&"*",Sheet1!$H$1:$H$4))

Adjust the name of the sheet where the criteria range H1:H4 is located.

In H6 control+shift+enter, not just enter:

=SUM(IF(MMULT(IF(ISNUMBER(SEARCH(Pcriteria,A2:A14)),1,0),ROW(H1:H4)^0)>0,D2:D14))

In H7 control+shift+enter, not just enter:

=SUM(IF(MMULT(IF(ISNUMBER(SEARCH(Ncriteria,A2:A14)),1,0),ROW(H1:H4)^0)>0,D2:D14))

In H8 just enter, which is the cell for the final result...

=H6-H7

Hope the 2010 version now does not run into the nesting levels limit.

Eric W · May 29, 2018

Re: Sumif wildcard help

You may wish to consider a UDF.

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu select Insert > Module. In the window that opens paste this:

Code:

Public Function MagicSumIf(CritRange As Range, Criteria As Variant, SumRange As Range)
Dim cr As Variant, sr As Variant, r As Long, c As Long, cc As Variant

    Application.Volatile
    cr = CritRange.Value
    sr = SumRange.Value
    
    If UBound(cr) <> UBound(sr) Or UBound(cr, 2) <> UBound(sr, 2) Then
        MagicSumIf = "Inconsistent range sizes"
    End If
    
    For r = 1 To UBound(cr)
        For c = 1 To UBound(cr, 2)
            For Each cc In Criteria
                If cr(r, c) Like cc Then
                    MagicSumIf = MagicSumIf + sr(r, c)
                    Exit For
                End If
            Next cc
        Next c
    Next r
    
End Function

Press Alt-Q to close the editor. Now from your worksheet, you can enter:

=MagicSumif(A2:A14,H1:H4,D2:D14)

or

=MagicSumif(A2:A14,{"*one*","*irs*"},D2:D14)

It will only count an item once, even if it matches more than one criteria. Also, "Table" won't match "Tables", but "Table*" will match "Tables". Case matters ("table" won't match "Table"), but a minor change can fix that if desired.

Renevatia · May 29, 2018

Re: Sumif wildcard help

Eric W said:
You may wish to consider a UDF.

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu select Insert > Module. In the window that opens paste this:

Code:

Public Function MagicSumIf(CritRange As Range, Criteria As Variant, SumRange As Range) Dim cr As Variant, sr As Variant, r As Long, c As Long, cc As Variant Application.Volatile cr = CritRange.Value sr = SumRange.Value If UBound(cr) <> UBound(sr) Or UBound(cr, 2) <> UBound(sr, 2) Then MagicSumIf = "Inconsistent range sizes" End If For r = 1 To UBound(cr) For c = 1 To UBound(cr, 2) For Each cc In Criteria If cr(r, c) Like cc Then MagicSumIf = MagicSumIf + sr(r, c) Exit For End If Next cc Next c Next r End Function

Press Alt-Q to close the editor. Now from your worksheet, you can enter:

=MagicSumif(A2:A14,H1:H4,D2:D14)

or

=MagicSumif(A2:A14,{"*one*","*irs*"},D2:D14)

It will only count an item once, even if it matches more than one criteria. Also, "Table" won't match "Tables", but "Table*" will match "Tables". Case matters ("table" won't match "Table"), but a minor change can fix that if desired.

Thank you excel wizards for all the help. Without these these examples I wouldn't have come up with a viable solution. I did end up using a custom function that behaves similarly to the Sum MMULT function but with regex so I can take just one fully loaded expression instead of passing them through loops. This prevented any noticeable lag, which was my initial concern with a long formula/ vba. I just finished implementing it earlier today and everything is working as expected! Now I can move on to finishing the rest of the project.

Aladin Akyurek · May 30, 2018

Re: Sumif wildcard help

So you have somehow used the suggested udf, not (anything from) post #16 ?

Renevatia · May 30, 2018

Re: Sumif wildcard help

Aladin Akyurek said:
So you have somehow used the suggested udf, not (anything from) post #16 ?

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.

Sumif wildcard

Renevatia

New Member

Renevatia

New Member

Excel Facts

Aladin Akyurek

MrExcel MVP

Renevatia

New Member

Aladin Akyurek

MrExcel MVP

Renevatia

New Member

Aladin Akyurek

MrExcel MVP

Eric W

MrExcel MVP

Renevatia

New Member

Aladin Akyurek

MrExcel MVP

Renevatia

New Member

Similar threads

Forum statistics

Share this page

Sumif wildcard

New Member

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock