Stumped by MAXIFS Returning Zero

[NorthbyNorthwest]

Hi, everyone. I have an Excel table from which I want to extract min and max values from based on multiple criteria. In table below I have criteria in cells H1 and H2. I want to return the fiscal month in column E from table. Cell H4 has MINIFS formula. Cell I4 contains same references for MAXIFS formula. The Fiscal Month column is formatted as a number. But formula does not work. I've tried numerous things over last few hours. I literally cannot understand why formula won't work. It seems so straightforward. Could someone tell me what I'm doing wrong or supply a formula alternative to get the result I want?

 

[Cubist]

In a blank cell, test

=ISNUMBER(E4)

 

[NorthbyNorthwest]

In a blank cell, test

=ISNUMBER(E4)

Cubist, it returned FALSE.

 

[dermie_72]

When I use the following code it returns the max. See if that works.

=MAXIFS(tblTest[[#All],[Fiscal Month]],tblTest[[#All],[Employee]],$H$1,tblTest[[#All],[Position]],$H$2)

If it's not a number though it wont be able to return anything.

 

[Cubist]

Cubist, it returned FALSE.

It means that column E is text. Formatting won't effect how Excel store the values. Try this:

• Highlight the column
• Data tab -> Text to Columns
• Delimited -> Next
• Uncheck all delimiters -> Next
• General -> Finish

 

[NorthbyNorthwest]

Cubist, it returned FALSE.

Cubist, thank you so much. I've been an Excel user for a lot of years. And, yet I forget just because something is formatted as a number doesn't mean Excel recognizes it as number. Almost never is it a problem. I placed the VALUE function around the fiscal month numbers, and the MINIFS and MAXIFS formulas worked. Thanks for quick response. I honestly don't know if I would've figured it out. Or if I did, I don't want to imagine how much time it would've taken. I was sure the fiscal months were actually numbers. Thanks, again!