Forum Discussion

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

Silver Contributor

Feb 24, 2019

Hello Lauri,
You may use any of these formulas:
1. =MAX(INDEX(SS!C18:C3001*(SS!A18:A3001=15),0))
2. =SUMPRODUCT(MAX(SS!C18:C3001*(SS!A18:A3001=15)))

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

Resources