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

Question

On this website, I typically search and get answers to my issues, but I was unable to resolve this issue. I need your assistance.

In a table I have, different prices are shown in various columns. Without adding a new column, I need to multiply the quantity numbers by the lowest values from each of these columns. I need to search for these minimum values using a set of requirements. I have to utilise horizontal formulas because this table will extend below.

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.

Answer 1

We must use SUBTOTAL and OFFSET in Office 2019 because it lacks the dynamic array formula in order to return an array of the MINIMUMS of each row to SUMPRODUCT. Add the following criteria to SUMPRODUCT after that:

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

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))))))