Forum Discussion

Lauri_Perico's avatar
Lauri_Perico
Copper Contributor
Feb 23, 2019

Using a specific cell to reference table name in a minifs or maxifs funtion

Good afternoon all, I have developed a spreadsheet to be used for several users with different excel versions and not being able to update to latest one. In this spreadsheet I have several MINIFS, MA...
Formulas and Functions
Lauri_Perico's avatar
Lauri_Perico
Copper Contributor
Feb 24, 2019
Good Night, thanks for the answers. I´ve tried both and both of them give me #VALUE responses...
Any other suggestion? I´ve read about a combination of index and match but haven´t found the proper syntax to obtain my results.

Thanks again!!!

Laura
Twifoo's avatar
Twifoo
Silver Contributor
Feb 24, 2019
Hi Laura,
I tested the 2 formulas I suggested and both worked. I can’t believe your claim that both returned errors. Perhaps something is wrong with your data in Column C. Please check.
  • Lauri_Perico's avatar
    Lauri_Perico
    Copper Contributor
    Feb 24, 2019

    Hello again!!! Thanks for the quick reply.

     

    My column C in the sheet SS has Date data, each row is being calculated from previous rows. I found the problem, C column formula not only returns a number but also "", since the column has both numbers and "" results, that´s where the #VALUE appears.

     

    I eliminated that "" conditional in the formulas in column C and your workaround is OK to replace MAXIFS, but not for MINIFS.

     

    For MINIFS I used:

    =INDEX(SS!$C$18:$C$3001,MATCH(I5,SS!$A$18:$A$3001,0))

     

    It worked, eliminating the "" values both formulas worked fine.

     

    Thanks again!

     

    Laura

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 24, 2019

      I'd prefer for MAX

      =AGGREGATE(14,6,1/(SS!$A$18:$A$3001=I5)*SS!$C$18:$C$3001,1)

      and for MIN

      =AGGREGATE(15,6,1/(SS!$A$18:$A$3001=I5)*SS!$C$18:$C$3001,1)
    • Twifoo's avatar
      Twifoo
      Silver Contributor
      Feb 24, 2019
      Hello again Laura,
      Instead of MINIFS, you can use this formula:
      =LOOKUP(PI(),2/(1/SS!C18:C3001=MAX(INDEX(1/SS!C18:C3001*(SS!A18:A3001=15),0))),SS!C18:C3001)
      I should have used 3 as the lookup_value argument but I chose PI() just to make Detlef_Lewin smile at my formula again!

Resources