Excel 2007 : Wildcards in criteria for Sumif function

Dear All,

my first post. I am revamping an excel spreadsheet that I inherited in my new position.

I want to use Sumif to find account codes then sum all occurences of expenses under that given account code. The catch is that the account codes are four digits: e.g. 5314. I typically have groups of account codes that I would like to sum together: e.g. 5311, 5312, 5313, 5314.

I created this function:

=SUMIF('sheet1'!K14:K449;"531?";'sheet1'!S14:S449)

using the question mark character ? as a wildcard for any of my codes that begin with 531.

This does not work. It does not find and sum the occurences of the 531 codes. In my current data set there are two occurences of 5314 totalling 19,000Euros. If I replace 531? with 5314 for the criteria then I get the correct result.

I guess my question is how to use a wildcard in this case and if it is possible.

Thanks!

Try an asterisk instead of a question mark.

The wildcards don't work on numericals here....

Try:

=SUMPRODUCT(--(LEFT('sheet1'!K14:K449,3)+0=531);'sheet1'!S14:S449)

Maybe in Italian version:

=MATR.SOMMA.PRODOTTO(--(SINISTRA('sheet1'!K14:K449;3)+0=531);'sheet1'!S14:S449)

I would think:

=SUMIFS(Data!S14:S449;Data!K14:K449; ">=5311";Data!K14:K449; "<=5314")

Although romperstomper's solution would be for sure the best for this kind of query, especially if you have XL2007 or later... I just had missed replacing one of my commas with a semicolon in my original formula.. so I think this should work too (if you desire the "wildcard" approach).

=SUMPRODUCT(--(LEFT('sheet1'!K14:K449;3)+0=531);'sheet1'!S14:S449)