Forum Discussion

Yury Tokarev's avatar
Yury Tokarev
Steel Contributor
Aug 01, 2017

Hi Stephen,

 

you can use a combination of the INDEX funciton with a negative approximate MATCH. For example, your formula in G6 would be '=INDEX('Daikin Data'!B$2:B$7,MATCH($F6,'Daikin Data'!$C$2:$C$7,-1))'. For the formula to work the data in the 'Daikin Data' sheet must be sorted by 'Cooling (kw)' column in a descending order.

 

Please find attached the file with a solution

 

Regards

Yury

Stephen Coff's avatar
Stephen Coff
Copper Contributor
Aug 01, 2017

Yury,
Likely stretching the appreciation some.

Please find attached:

Is there a way to calculate the total "# of Cond" per "Condenser Model" and display in the top cells filled red?

  • Yury Tokarev's avatar
    Yury Tokarev
    Steel Contributor
    Aug 01, 2017

    Hi Stephen,

     

    you can use SUMIFS function. An example in cell L5 would be '=SUMIFS($C$9:$C$76,$G$9:$G$76,J5)'. Please note that I have removed call merge from the 'Design Criteria' and 'Selection Data' headers and replaced it with 'Centre Accross Selection' (Format Cells ->Alignment->Horizontal). This is better, because it allows to select columns as desired (e.g. '# of Cond" and 'Condenser Model') and avoids confusion in formulas when selecting ranges.

     

    Please see the updated file attached.

     

    If you find my answer userful and like it, I would appreciate you clicking the 'Like' button :)

     

    Thank you

    Yury

    • Stephen Coff's avatar
      Stephen Coff
      Copper Contributor
      Aug 01, 2017

      Yury,
      Perfect ! Thank you.

      I bet it took the best part of a few mins, i had played with it for a couple of hours and gave up, lol