Forum Discussion
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, MAXIFS to substract info from a large set of data. The MINIFS, MAXIFS formulas are located in other sheets that summarize the values in the first data set. In some of the users computers these formulas don´t work, I´m trying to find a workaround to develop same MINIFS, MAXIFS results without being necessary to use those formulas, but haven´t been able to.
This is my original formula:
MAXIFS(SS!$C$18:$C$3001,SS!$A$18:$A$3001,I5)
I´ve tried this workaround:
MAX(IF(SS!A18:A3001=I5,SS!C18:C3001,""))
And it returns a #VALUE error, can you please help me solve this? I believe if I find the way to make it work with MAXIFS, I´ll be able to make it work with MINIFS. If this helps, I have a cell format DATE in C column data.
I would really appreciate your help,
Regards,
Laura
6 Replies
- 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)))- 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- 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.
