excelexcel-formulaminsumproduct

Finding the minimum values from different columns according to the criteria and multiplying by another column

I normally search and find the answers to my questions on this site, but I could not solve this problem. I ask for your help.

I have a table and there are different prices in different columns in this table. I need to find the lowest values from these columns and multiply by the quantity numbers without creating a new column. While finding these minimum values, I need to find them according to a criteria. Since this table will stretch downwards, I must use horizontal formulas.

Table 1 Image

Quantity Criteria Column 1 Price Column 2 Price Column 3 Price Column 4 Price
1 YES Material 1 $100,00 Material 1 $90,00 Material 1 $75,00 Material 1 $100,00
2 YES Material 2 $120,00 Material 2 $150,00 Material 2 $220,00 Material 2 $210,00
1 YES Material 3 $140,00 Material 3 $140,00 Material 3 $145,00 Material 3 $130,00
4 NO Material 4 $150,00 Material 4 $90,00 Material 4 $80,00 Material 4 $80,00
2 NO Material 5 $90,00 Material 5 $60,00 Material 5 $55,00 Material 5 $56,00
1 NO Material 6 $15,00 Material 6 $15,00 Material 6 $20,00 Material 6 $10,00
3 YES Material 7 $150,00 Material 7 $200,00 Material 7 $180,00 Material 7 $90,00

The results should be: (1*75) + (2*120) + (1*130) + 0 + 0 + 0 + (3*90)

I've tried =SUBTOTAL(5,OFFSET(A3,SEQUENCE(ROWS formulas but no luck. excel doesn't support sequence. I can't write an "if" formula next to each column because I shouldn't increase the columns of the table.

Solution

  • With Office 2019 which does not have the dynamic array formula we need to use SUBTOTAL and OFFSET to return an array of the MINIMUMS of each row to SUMPRODUCT. Then add the other criteria to SUMPRODUCT:

    =SUMPRODUCT((B2:B8="YES")*A2:A8,SUBTOTAL(5,OFFSET(C1:J1,ROW(C2:J8)-MIN(ROW(C2:J8))+1,0)))

    enter image description here

    One note, for me the numbers transferred as text not numbers. Make sure they are true numbers or this will not work.

    For those with the Dynamic Array formula we can ditch the Volatile Offset:

    =SUM(BYROW(ROW(A2:A8),LAMBDA(z,INDEX(A:A,z)*(INDEX(B:B,z)="YES")*MIN(INDEX(C:J,z,SEQUENCE(,COLUMNS(C:J),1))))))

    enter image description here