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, MA...
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)))
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
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.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
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)
