Need A Fuzzy SUMIF

[djmyers]

I'm trying to SUMIF numbers in one column if text strings in an adjoining column contain specific text. For instance, if Column A contains a series of text string like this:


COLUMN A
A2: some text etc. other text SEARCH-VALUE more text, even more text, etc.
A3: another string SEARCH-VALUE more string
A4: yet another string SEARCH-VALUE yet more string
[etc...]


COLUMN B
B2: 42
B3: 24
B4: 87
[etc...]


And cell C2 contains this:
C2: SEARCHING-VALUE


What I need is a formula that refers to C2 but ignores the "ING" in SEARCHING-VALUE. (The "ING" is just an example. In my real-world problem the reference cell contains a hyphenated number, e.g.: 12-345678. The value in the text strings is a truncated version of that number, e.g.: 12-5678.)


If the reference cell (C2) contained the exact value I need to search for in the text strings, I could just use a formula like =SUMIF(A:A,"*"&C2&"*",B:B). What I need is a formula that ignores the "ING" (or the "34" in the number example) found in the reference cell.

 

[DanteAmor]

If in your real world, "ING" are always 2 digits and they are in the same position, then try this formula:

=SUMIFS(B2:B4,A2:A4,"*" & LEFT(C2,3) & "*",A2:A4,"*" & MID(C2,6,4) & "*")

 

[djmyers]

Thank you, DanteAmor! This solution works very well!

In the number example (12-345678 and 12-5678) your formula seems to work as long as the hyphenated numbers in the text strings contain the "5678" somewhere on the right side of the hyphen. For instance, if "12-12300056789999" shows up in a text string in Column A, the formula will return a count from Col. B, whereas a "1234-5678" in a text string will not result in the number from Col. B being counted.

Would you be kind enough to guide me through how this formula works? Also, how would I write it so that a value in Col. B is counted only if the text string in Col. A contains a matching two digits and a hyphen (e.g. "12-") and the number on the right side of the hyphen is either an exact match immediately following the hyphen (e.g. "-345678") or the 4-digit match immediately following the hyphen (e.g. "-5678")?

Thank you!

 

[DanteAmor]

If this is not true: "If in your real world," ING "are always 2 digits and they are in the same position" so you could put other real examples. Maybe the solution is with a macro.

 

[djmyers]

DanteAmor: I was tired when I wrote the original question last night. In the "real world" situation I would need the "12-" to show up first, and the "5678" or the "345678" to immediately follow the first number pair and hyphen (e.g. the "12-" would immediately precede either the "5678" or the "345678"). I apologize I didn't make this clearer in the original post.


A formula would work out much better for me than a macro as my other colleagues who also work with this Workbook might balk at converting it to a macro-enabled workbook.


Any ideas for rewriting the formula with the above considerations in mind?


Thanks!

 

[DanteAmor]

So if the string exists 12- and 5678 must count.
Try the following

=SUMPRODUCT((ISNUMBER(SEARCH( LEFT(C2,3),$A$2:$A$10)))*(ISNUMBER(SEARCH(MID(C2,6,99),$A$2:$A$10)))*($B$2:$B$10))

 

[djmyers]

Thank you, DanteAmor.

This formula may be closer to what I'm looking for:

=SUMIFS(B2:B4,A2:A4,"*"&LEFT(C2,3)&"*",A2:A4,"*"&MID(C2,3,1)&RIGHT(C2,4)&"*")

Searching the table below, the above formula yields: 87

The two formulas you suggested -- which are both very helpful -- still give me the total count of column B (153). In my real world scenario, this could result in false positives.

What would really be great is if I could also adjust the formula so that it counts the number in column B if an exact match exists (like the one shown in A3).

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]STRING[/TD]
[TD="align: center"]SUMTHIS[/TD]
[TD="align: center"]REFERENCE[/TD]
[/TR]
[TR]
[TD]some text etc. 5678 other text 12- more text 12-3456 more text, even more text, etc.[/TD]
[TD]42[/TD]
[TD]12-345678[/TD]
[/TR]
[TR]
[TD]another string 12-345678 more string[/TD]
[TD]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]yet another string 12-5678 yet more string[/TD]
[TD]87[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Kind regards,

djmyers

 

[DanteAmor]

Thank you, DanteAmor.

This formula may be closer to what I'm looking for:

=SUMIFS(B2:B4,A2:A4,"*"&LEFT(C2,3)&"*",A2:A4,"*"&MID(C2,3,1)&RIGHT(C2,4)&"*")

Searching the table below, the above formula yields: 87

The two formulas you suggested -- which are both very helpful -- still give me the total count of column B (153). In my real world scenario, this could result in false positives.

What would really be great is if I could also adjust the formula so that it counts the number in column B if an exact match exists (like the one shown in A3).

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]STRING[/TD]
[TD="align: center"]SUMTHIS[/TD]
[TD="align: center"]REFERENCE[/TD]
[/TR]
[TR]
[TD]some text etc. 5678 other text 12- more text 12-3456 more text, even more text, etc.[/TD]
[TD]42[/TD]
[TD]12-345678[/TD]
[/TR]
[TR]
[TD]another string 12-345678 more string[/TD]
[TD]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]yet another string 12-5678 yet more string[/TD]
[TD]87[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Kind regards,

djmyers

This formula contemplates the coincidence


=SUMPRODUCT((ISNUMBER(SEARCH( LEFT(C2,3),$A$2:$A$10)))*(ISNUMBER(SEARCH(MID(C2,6,99),$A$2:$A$10)))*($B$2:$B$10))