Search code examples
excelexcel-formulaexcel-2011vba

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

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. :)

Solution

  • You will need to parse the data:

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