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

[Lauri_Perico]

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

[#2]

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

[#3]

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

[#4]

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.

[#5]

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

[#6]

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!

[#7]

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)