Wildcard condition with * for SUMIFS formula on a column with number values

Question

I have a huge excel sheet that looks like this:

╔══════╦══════╦═════╗
║  A   ║  B   ║  C  ║
╠══════╬══════╬═════╣
║ Jack ║ 2001 ║ 1,5 ║
║ Jack ║ 2002 ║ 2,0 ║
║ Jack ║ 2003 ║ 1,0 ║
║ Jack ║ 3001 ║ 3,5 ║
║ Jack ║ 3002 ║ 4,0 ║
║ Jack ║ 3003 ║ 1,0 ║
║ Jill ║ 2001 ║ 3,0 ║
║ Jill ║ 2002 ║ 5,0 ║
║ Jill ║ 2003 ║ 2,0 ║
║ Jill ║ 3001 ║ 0,5 ║
║ Jill ║ 3002 ║ 6,0 ║
║ Jill ║ 3003 ║ 2,5 ║
╚══════╩══════╩═════╝

Column B contains many different numbers, but they all begin with digits 2, 3 or 8. The numbers in column B are always be 4 digits long; I'm only interested in checking the first digit.

I need to add together the values of column C, where the first digit of the corresponding cell in column B is either 2*, 3* or 8*. What I need is to create a formula that does this (Ruby-esque pseudocode):

sum = 0
spreadsheet_rows.each do |row|
  if row.a == "Jack" and row.b == "2*" # Note the second wildcard condition.
    sum += row.c
  end
end

puts sum # Should print 4,5 in this example.

I'm trying to use the following formula in Excel to accomplish this:

=SUMIFS($C:$C; $A:$A; "Jack"; $B:$B; "=2*")

I know that Excel does not support wildcard conditions for numbers, however, I have formatted column B as type "Text" in Excel, so I thought it would be treated as such, but it appears that it is still treated as an int.

Is there a different way of applying a wildcard condition in =SUMIFS for number values in Excel? Perhaps there's a way to somehow "cast" the integers to strings in the formula? I haven't found a way to do it (yet).

I'm using Excel for Mac 2011.

Answer 1

I'd go for the less readable, but more powerful SUMPRODUCT:

=SUMPRODUCT(($A:$A="Jack") * (LEFT($B:$B;1)="2") * ($C:$C))

which will generate boolean arrays for each of the conditions (first and second brace part) which it will multiply with the third one (your numbers).

EDIT: As noted in comments, #VALUE errors can appear if any value in column C cannot be converted to a number. To avoid that, you could use the syntax suggested by barry houdini

=SUMPRODUCT(($A:$A="Jack") * (LEFT($B:$B;1)="2"); $C:$C)

and let SUMPRODUCT skip over non-numbers.