How to use arrayformula to find and get data from only a certain, dynamic set of columns?

Question

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.

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