MINIFS with vertical criteria #VALUE error

[bigmyk2k]

Hello,
I am trying to fill in data to a table that compiles the minimum value for a given month in a given year(eventually MAX, MEAN, and percentile values as well).

The data are in sheets labeled with a 3-letter abbreviation for the month, and arranged in columns by year. Thus, the data for March of 2015 will be in the column with the header "2015" on the sheet labeled "MAR".

The following formula (on sheet titled SUMMARY) executes correctly (per Evaluate Formula) but returns a #VALUE error

=MINIFS(INDIRECT(CONCATENATE(SUMMARY!$A3,"!$B$2:$J$32")),INDIRECT(CONCATENATE(SUMMARY!$A3,"!$B$1:$J$1")),SUMMARY!B$1)

For March 2015, this works out to:

=MINIFS(MAR!$B$2:$J$32,MAR!$B$1:$J$1,2015)

It gets to this point correctly, but he next step returns a #VALUE error, which leads me to believe that MINIFS can't do a vertical validation, but I believe that I have used vertical validation before.

Any thoughts?

 

[Eric W]

The reason that's failing is because your ranges are not the same size. With MINIFS (in fact, all the ..IF.. functions) the value range and all the criteria ranges must be all the same size and shape. I think maybe you want something like this:

=MIN(INDEX(INDIRECT(SUMMARY!$A3&"!$B$2:$J$32"),0,MATCH(SUMMARY!B$1,INDIRECT(SUMMARY!$A3&"!$B$1:$J$1"),0)))

 

[bigmyk2k]

I didn't realize that.
Thank you, that works perfectly.

 

[Eric W]

Glad to help.