# Using Wildcards with SUMIFS and an ARRAY

1. ## Using Wildcards with SUMIFS and an ARRAY

I am trying to create a SUMIFS formula that checks multiple criteria with a wildcard. If the cell contains and of these cases, "½" , "½ₓ", "½ ₓ", at the beginning of the string, I want the value column AN added to the sum.

When I enter this formula below, I get the "There's a problem with this formula" error message.

=SUM(SUMIFS(\$AN\$4:\$AN\$487,\$AP\$4:\$AP\$487,\$BN\$3,\$AO\$4:\$AO\$487,{"½" & "*","½ₓ" & "*","½ ₓ" & "*"}))

When I enter =SUM(SUMIFS(\$AN\$4:\$AN\$487,\$AP\$4:\$AP\$487,\$BN\$3,\$AO\$4:\$AO\$487,{"½","½ₓ","½ ₓ"})), I don't get the same error, but the formula doesn't work as intended and does not collect all the values I am looking for.

Does anyone have any suggestions? Thank you in advance!

2. ## Re: Using Wildcards with SUMIFS and an ARRAY

Welcome to the forum.

Which 'newest' version are you using? There are more than one and there are differences between them, so please update your profile with the specifics. Thanks.

Welcome to the forum.

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

3. ## Re: Using Wildcards with SUMIFS and an ARRAY

Did you try adding the wildcard?

=SUM(SUMIFS(\$AN\$4:\$AN\$487,\$AP\$4:\$AP\$487,\$BN\$3,\$AO\$4:\$AO\$487,{"½","½ₓ","½ ₓ"}&"*"))

4. ## Re: Using Wildcards with SUMIFS and an ARRAY

That worked! Thank you! I am also trying to utilize this same formula with cell references in the place of the "½" inputs, but it seems that cell references cannot be used with array formulas, and I cannot concatenate the "ₓ" onto the end of the cell reference. Are cell references just not possible to use in this case?

5. ## Re: Using Wildcards with SUMIFS and an ARRAY

If those are separate characters to the 1/2, surely you can just use:

=SUMIFS(\$AN\$4:\$AN\$487,\$AP\$4:\$AP\$487,\$BN\$3,\$AO\$4:\$AO\$487,"½*")

since they all begin with 1/2.

There are currently 1 users browsing this thread. (0 members and 1 guests)