Finding the max and sum of certain columns.

In the attachment i am trying to return the sum of only the max of each of the rows of the two columns being search for in the second table.

eg. for the first one the columns being searched are "a" and "b". I want it to look at both of the columns and for each row pick only the max and in the end sum them all together.

Also in the end I would like to set a value for a, b, c and d and before it works out the max it would multiply the values set for the columns with each of the values under those columns.

g'day mate and welcome to the forum


Could you provide a few sample answers?

You may try this code. For detail see the attached sheet.

Please Login or Register  to view this content.

Hi,

For the Sum of the Maximums then in I2 copied down

Formula:

Please Login or Register  to view this content.

A bit long-winded but assuming your "multipliers" in row 11 you can use this array formula in I2 [revised]

Formula:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER

Of course you will get the same results for a and b as for b and a

See attached where I set the multipliers to 1 so that currently works as if they don't exist

Hi Skalv an welcome to the forum,

I have a helper formula that spans columns and then does a sum at the end. It is a little easier to understand that the above examples (maybe). See if this answer is more to your liking.

there are three parts to your question - #1) find maximum across rows; #2) total maxima; #3) multiply each column with a certain value.

i think you should clearly think through what you need. if you want to find the maxima, add them and then apply the multiplier, that is just a bit complicated. but, if you want to do #1, then #3, then #2, it becomes more confusing / complicated.

for e.g., in cell A3 and B3, you have 3 and 3. the maximum of those is 3. which multiplier are you going to apply - the one for column A or B?

or, do you want to weed out rows where values are equal?

also, do you mean to apply the multiplier before comparing? i don't think that is what you want because that will probably give you incorrect results.

for e.g., in cell A2 and B2, you have 1 and 2. if the multiplier for column A were 2 and column B were 1 (or, 3 and 1), the results will be awry.

anyway, if you just want to do #1 and #2, this is another way:

Please Login or Register  to view this content.