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.
There is always the “old school” way…
=SUM(IF(LEFT(B2:B12)=”1″,C2:C12,))
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.
Thanks
Neale
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.
Yes I have seen that frustration mentioned before. SUM is now more flexible than SUMIFS.