Basic array formula only working in first cell

user_11667177 0
Viewed 320

I have a huge data sheet of tons of repeats of names (Column F Sheet 1) with a column of dates (Column C Sheet 1) of when they ordered. I want to find the minimum and maximum dates of the orders to find out when the customer first ordered and last ordered. This formula works only for the first row.

=MIN(IF(Sheet1!$F:$F=$A2,dates)) =MAX(IF(names=$A2,dates))

This is calculated on sheet 2 which has a list of unique names in the A column.

(they are two variations of the same thing to see if one worked when dragging down but both do work on the first row)

I'm on mac and people have suggested control + U and then cmd + return but this just skews up all the formula. I've also tried blank cells etc. I can't see anything wrong with the formula

excel-formula excel
1 Answers

If you have Office 365 you can take advantage of MAXIFS() function:

=MAXIFS(Sheet1!$C:$C,Sheet1!$F:$F,"="&A2)

For older excel versions, you have to use an Array Formula. Press CTRL+SHIFT+ENTER instead of ENTER will convert the formula in an Array Formula:

=MAX(IF(Sheet1!$F:$F=A2,Sheet1!$C:$C))

=MIN(IF(Sheet1!$F:$F=A2,Sheet1!$C:$C))

user_38138460
Related