Ask Your Question

max with condition [closed]

asked 2016-06-05 22:33:29 +0100

-rob- gravatar image

I have a spreadsheet which has an ID (in this case of a musical piece) in column C of tab "Lijst".

Now I want to know when this is last performed by our choir and put that in a separate column. The list of music performed is in tabs 2016, 2015, etc. where the ID is in column E en the performance date in column B.

I tried to put in: {=MAX(IF(C2='2016'.$E$2:$E$150,'2016'.$B$2:$B$150))}

That seems to work, but when I pull down this formula all cells report Err:508

How can I make this work? Or is there an easier option to accomplish this?

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-09-06 20:14:08.049161

2 Answers

Sort by » oldest newest most voted

answered 2016-06-06 15:02:09 +0100

-rob- gravatar image

Lupp, thanks for your answer. Since I am on a Mac Ctrl doesn't work to fill the arry formula down. Also the function key and the Applekey don't do the trick. I also tried cut/paste, but that works only if pasted to a single cell. Any idea's?

edit flag offensive delete link more


First select the source cell and all the target cells, then, depending on the version (I' not near to my computer; haven't a LibO at hand at the moment):

Edit > .. or Sheet > ...
Find a > 'Fill' > 'Down' menu item.

Lupp gravatar imageLupp ( 2016-06-06 21:32:13 +0100 )edit

answered 2016-06-06 00:39:16 +0100

Lupp gravatar image

updated 2016-06-06 00:40:28 +0100

Keeping data of same structure in more than one sheets is a bad idea.
All the performce data in one sheet would allow to use the formula


entered for arry evaluation wit Ctrl+Shift+Enter.

I cannot see how your formula should return a "ltest" date" if vnot in 206.

Filling an array formula down with the mouse reuires modifiuction with the Ctrl key.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2016-06-05 22:33:29 +0100

Seen: 1,196 times

Last updated: Jun 06 '16