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

0 votes

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.

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.

Feb 23, 2023 in Others by narikkadan
• 63,420 points
270 views

1 answer to this question.

0 votes

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

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

answered Feb 23, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Excel: Group rows and add minimum and maximum from two different columns within the group

You can accomplish your goal with Power ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,420 points
641 views
0 votes
1 answer

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

answered Jan 26, 2023 in Others by narikkadan
• 63,420 points
365 views
0 votes
1 answer

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

answered Jan 29, 2023 in Others by narikkadan
• 63,420 points
244 views
0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,410 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,480 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

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

Feb 17, 2022 in Others by Edureka
• 13,670 points
727 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,466 views
0 votes
1 answer

VBA - Build a Two-Column Array By Looping Through one Array with a Specific Criteria and Selecting From Another Array

As already mentioned as comment: Loop over ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,010 points
270 views
0 votes
1 answer

How to automatically assign a color to the maximum and minimum values in a set of selected cells in Excel?

See Conditional Formatting, which may be accessed ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,010 points
304 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP