🤷🏼‍♂️ Help Please With MAXIFS Function

Ask for Help
1

Hi all

Anybody able to help with this one please:

  • I have a sheet of users (name, email, photo etc)
  • A user can submit a request and these are stored in a sheet of requests
  • Each request includes a column for the user email and a date when they submitted it

I want to add a column to my users sheet that shows the most recent date that the user submitted a request. I had thought that the MAXIFS function (used as an ARRAYFUNCTION) might achieve this for me but I can’t get it to work.

I am using it like this:

={"MostRecentRequest";ARRAYFORMULA(IF(ISBLANK(C2:C),"",MAXIFS('UserRequests'!C2:C,'UserRequests'!A2:A,"="&B2:B)))}

where:

  • 'UserRequests'!C2:C is the column in the requests sheet containing the date
  • 'UserRequests'!A2:A is the column in the requests sheet containing the user email
  • B2:B is the column in the users sheet containing the user email

So I’m saying, in the users sheet, go find the largest value in the date column from the requests sheet where the user email in there matches the user email in here.

I think the problem lies with the final “match” part of the MAXIFS function i.e. “=”&B2:B and I’m wondering if a range is not supported here? Difficult for me to provide the actual sheet as it has client data in it. Apologies.

Any help greatly appreciated.

2

You can use a vlookup combined with sort on the date column.

Else you can keep it in the Editor, make a multiple relation then use a rollup on the date, taking the latest value.

1 Like
3

Hey @ThinhDinh thanks for the rapid response. I should have said that I need it in the sheet because I have an Integromat scenario that uses it, otherwise the editor would’ve been perfect for me. I will certainly take a look at SORT which I guess nests inside the VLOOKUP somehow? I’m new to Google Sheets so trying to rapidly build up my function knowledge. Cheers.

1 Like
4

If you need help with that I will make an example sheet when I get to the computer tomorrow. Midnight here :blush:

1 Like
5

That would be fantastic. Many thanks. I have about 30 mins left before I pack in here, so will give it a go and see what damage I can do anyway … :muscle:

6

Yo! @ThinhDinh I got it working (with zero collateral damage) - many thanks for your help

1 Like