I have a table where my headers are dynamically generated from another source and can show up with any header values, in any order.

In the example below I've got the header values:

AA - BB - CC - ABBA - KK - VV

but it could just have well been:

CC - AA - QQ - YY

I don't know in advance which headers I will get, or how many.

My issue is that I want to sum all product values, using an array formula, where the column headers include for instance "BB".

So basically =arrayformula(sumifs(C4:H1000,$C$3:$H$3,"*BB*")), if that had been a thing.

My closest guess is that I need to use a VLOOKUP of some sort, but since the index of the columns to get the data from isn't static I can't quite figure out how to solve it.

Desperate for help.

enter image description here

1 Answer 1


=SUM(INDIRECT(ADDRESS(4, MATCH("BB", 3:3, 0), 4)&":"&
   SUBSTITUTE(ADDRESS(4, MATCH("BB", 3:3, 0), 4), "4", "")))


  • first 4 is the start of the range for sum
  • and the 3:3 is the row of headers

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.