Summing values with a specific header and other criteria

Question

I have the following formula:

SUMPRODUCT((Chart!$J$2:$BE$1000=H$2)*(Chart!$A$2:$A$1000=$A3)*(Chart!$C$2:$C$1000="FE")*(Chart!$H$2:$H$1000="YES")*(Chart!$G$2:$G$1000))

In which:

• (Chart!$J$2:$BE$1000=H$2): Looks for a specific month in a range of columns.
• (Chart!$A$2:$A$1000=$A3): Looks for a specific monicker for clients in one column.
• (Chart!$C$2:$C$1000="FE"): Looks for a specific monicker for transactions in one column.
• (Chart!$H$2:$H$1000="YES"): Looks for transactions that are going to be renewed, in one column.
• (Chart!$G$2:$G$1000): These are the ammounts to be summed.

The formula works rather well, but I need to be able to sum a range of columns, not just G2:G1000. Instead of G2:G1000 I need to sum values in the column range $J$2:$EBE$1000, in columns that in their header have a specific name (namely MONTHLY_VOLUME).

I've tried these two formulae:

SUMPRODUCT((Chart!$J$2:$BE$1000=H$2)*(Chart!$A$2:$A$1000=$A3)*(Chart!$C$2:$C$1000="FE")*(Chart!$H$2:$H$1000="YES")*SUMIF(Chart!$J$1:$BE$1," * MONTHLY_VOLUME * ",Chart!$J$2:$BE$1000))

and

SUMPRODUCT((Chart!$J$2:$BE$1000=H$2)*(Chart!$A$2:$A$1000=$A3)*(Chart!$C$2:$C$1000="FE")*(Chart!$HD$2:$H$1000="YES")*INDEX(Chart!$J$2:$BE$1000,,MATCH(" * MONTHLY_VOLUME * ",Chart!$J$1:$BE$1,0)))

Both formulae give me the same results, but the results are wrong. The values are far too high, and I'm guessing it happens because the last part of both formuale, be it the SUMIF or the INDEX lack some parameter to make it so that they work with the previous criteria, rather than just grabbing everything.

So, is this actually doable? If it is, how can I change these formulae to make it work?

Here's the worksheet: https://drive.google.com/file/d/1cMZaKJIMam7NZOr-6LL8OdLwXJTZ2bco/view?usp=sharing

Answer 1

So in the first part of your problem (which you solved already) you generated a mask column vector e.g. (0, 0, 1, 0, 1) which you multiplied cell-by-cell with the amounts column vector e.g. (10, 20, 30, 40, 50) and then used sumproduct to add up the vector elements (= 80 in this example).

Two expand your problem, you now want to identify one or more monthly_volume (MV) columns and add those together based on the mask vector above. e.g. SUMPRODUCT((0, 0, 1, 0, 1) * ((10, 20, 30, 40, 50) + (60, 70, 80, 90, 100))) = 260

Lets use some matrix multiplication. Lets say you have n rows of data and m columns that contain 1 or more MV columns. First get a column vector mask for your MV columns: ([1 x m]' = [m x 1])

TRANSPOSE((Chart!$J$1:$BE$1 = " * MONTHLY_VOLUME * ")*1)

And matrix multiply that by your data matrix to get a single column vector of summed MV columns ([n x m] * [m x 1] = [n x 1])

MMULTI(IFERROR(VALUE($J$2:$EBE$1000),0), TRANSPOSE((Chart!$J$1:$BE$1 = " * MONTHLY_VOLUME * ")*1)

Now use that in your original formula:

SUMPRODUCT((Chart!$J$2:$BE$1000=H$2)*(Chart!$A$2:$A$1000=$A3)*(Chart!$C$2:$C$1000="FE")*(Chart!$H$2:$H$1000="YES")*(MMULTI(IFERROR(VALUE($J$2:$EBE$1000),0), TRANSPOSE((Chart!$J$1:$BE$1 = " * MONTHLY_VOLUME * ")*1)))

Remember to enter the formula with CTRL+SHIFT+ENTER (as an array formula)