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.

Feb 23, 2023 in Others 363 views

## 1 answer to this question.

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

• 38,010 points

## Sum the total of a column in excel and paste the sum to a different workbook

Get Column Sum From Closed Workbook Option Explicit Sub ...READ MORE

## How to expend the code to transfer data from one spreadsheet to another based on multiple criteria

The progress bar is unnecessary. Option Explicit Sub VTest2() ...READ MORE

## How to build a custom column formula in Power query that will retrieve Image and load it to Excel to display that image from the specified column

Make sure ignore privacy is selected in ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE