How to select first number within a cell for the "criteria1" of SUMIFS in Excel?

Question

I'm using SUMIFS(), and I want the criteria1 to be the first number of a series of comma separated numbers within a cell. For example,

A1=1111, 1112  SUMIFS(B:B, C:C, A1)

So basically, the formula should add all the cells in B:B that are adjacent to each cell in C:C that has the value "1111", but I don't know how to specify to the function to use 1111, but ignore 1112. Its one of those syntax rules that only expert Excel users know about. :)

Answer 1

You will need to parse the data:

=SUMIFS(B:B, C:C, TRIM(LEFT(A1,FIND(",",A1)-1)))