Search code examples
excelexcel-formulasumifs

Excel sumifs formula with condition where first character of criteria_range is letter (a-z)

I am facing an challenge where I need to make a SUMIFS formula referencing data in another sheet where I need to sum values if first character of string in one of criteria_range columns is any letter (a-z).

Now, I could bruteforce it with something like this:

=SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"A*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"B*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"C*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"D*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"E*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"F*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"G*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"H*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"I*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"J*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"K*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"L*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"M*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"N*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"O*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"P*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"Q*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"R*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"S*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"T*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"U*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"V*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"W*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"X*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"Y*")+
  SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,"Z*")

But I want to believe that there is more elegant solution possible within excel.

Solution

  • Use SEQUENCE to return an array of numbers that convert to the letters. Then sum the results of the arrays:

    =SUM(SUMIFS(B:B,A:A,CHAR(SEQUENCE(26,,65))&"*"))

    enter image description here

    So your formula will be:

    =SUM(SUMIFS(Sheet2!B:B,Sheet2!C:C,A2,Sheet2!J:J,CHAR(SEQUENCE(26,,65))&"*"))