Search code examples
excelsumifs

Sumif row values from one column that are referenced from another

Looked a bit for this info but I'm stuck.

let's say I have two columns. I have to find top largest values from one column and sum the corresponding row values from the other column.

To find the values and average the result, I'm using the following formula (where c19 is the number of items to retrieve):

AVERAGE(LARGE('3.Tabela_DC_Marca'!B1:B1500;ROW(INDIRECT("1:"&C19))))

What I want now, is to retrieve and sum the matching row values from the second column. I can't seem to find a way to nest that formula.

Something like this?

Sumif Range -

AVERAGE(LARGE('3.Tabela_DC_Marca'!B1:B1500;ROW(INDIRECT("1:"&C19))))

Criteria - ????? Sum_range -

 '3.Tabela_DC_Marca'!C1:C1500;ROW(INDIRECT("1:"&C19)

Can anyone help?

Thank you

Solution

  • Following on from BigBen's comment, something like this?

    The formula in D4 is

    =SUMIF($B$2:$B$11,">="&LARGE($B$2:$B$11,2),$A$2:$A$11)

    and sums the values in A corresponding to the two largest values in B. From this you can easily average them.

    enter image description here

    It's also possible to use this with INDIRECT, viz enter image description here