SUMIFS Wildcard Limitation

SUMIFS can use wildcard characters, but the wildcards only work on text-based codes.

Wildcard are the two symbols ? and *. They represent unknown characters.

The ? symbol represent a single unknown character.

The * symbol represents any number of unknown characters, including no unknown characters.

So ?A will find WA and SA but not TAS.

And ?A? will find TAS but not WA and SA.

And *A* will find WA, SA, TAS and ACT.

BUT these symbols do not work with numeric based codes.

In the image below you can see the results for the Dept and Account based codes.

The text-based Dept characters work, but the numeric Account codes don’t work with wildcards.

If you add a letter in front of the numeric code it works. See image below.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

4 thoughts on “SUMIFS Wildcard Limitation

  1. There is always the “old school” way…


    I cannot test this as I only have XL365, but I think that for versions of Excel without dynamic arrays, you may be able to use SUMPRODUCT instead of SUM or, possibly, leave the SUM function and array-enter the formula (using CTRL+SHIFT+ENTER) instead.

    • Hi Rick
      Yes it is doable with other text techniques and SUMPRODUCT in the old versions and SUM in the subscription. You would think wildcards would work on numbers as well as text.

      • Actually, I have wondered why in the newer dynamic array versions of Excel, they did not make the first argument be able to be an array or a range instead of the old requirement of a range only.