array formula issue

Numbers does not have array formulas. Sometimes they can be replaced by a different formula, sometimes it requires an extra column to do all the "array" stuff that Excel does in one cell.

What is the result supposed to be? It looks like it is referencing the "total" column and returning the minimum value from the "Month Start" column, only looking at the rows where "total" is > 0. I ask because the data you have in the "Month Start" column is text so the result of the MIN function is always going to be zero with the data that is shown. MIN(text) is always zero.

If the "Month Start" column is to have actual dates in it, a formula that should work is

=MINIFS(E5:E20,C5:C20,">0")

Month names will work in column E if the column is formatted as date&time showing only the month, not if formatted as text. Just remember that the "month name" is still a full date, with a year attached.

Hi vonroy33,

Welcome to the charm of Numbers! Numbers uses small tables, each with a purpose.

This table has one Header Row and two Footer Rows. Header Rows and/or Footer Rows allow a formula to refer to whole columns.

Note that the Table Name is above the table, not in Row 1.

This is not an array formula, but it works.

Formula in Footer Cell E12: VLOOKUP(MINIFS(C,C,">0"),C:E,3,0)

MINIFS finds the minimum value in column C that is greater than 0 ($273.00)

VLOOKUP uses that to find the corresponding value in C:E (third column, E)

Regards,

Ian.

Hi vonroy33,

Thanks for the screen shot showing column labels. That certainly helps!

I don't think that Numbers understands "minimum of Month1, Month 2" etc. Those are text strings. Use plain numbers instead.

Formula in Footer Cell E12: MINIFS(E,C,">0")

That finds the minimum Month Start number where Total is greater than 0.

Regards,

Ian.

The screenshot below is the same formula in Excel 16 for Mac and I get a zero, as expected. I believe your spreadsheet has a special format that puts the word "Month" in front of the number. If so, that format should have imported to Numbers and the MIN formula will work.

Take a look at the cell format to see if it is text (or automatic) or is a custom format that appends "Month" to the front of a number.