Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

How do I calculate the max from two arrays with a year value?

Hello

I have asked a similar question before and need some additional help:

As you can see, there are dates in column A and this time, I'd like to display the favourite shop for, lets say, 2011. What do I need to change to the original formula of

=INDEX(Shop,MODE(IF(Customer=F2,IF(Shop<>"",MATCH(Shop,Shop,0)*{1,1}))))

Open in new window


Thanks
Massimo

screenshotmode-by-year.xlsx
mode-by-year.PNG
0
Massimo Scola
Asked:
Massimo Scola
  • 2
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
If you enter the year number in I1 then you can try this formula

=IFERROR(INDEX(Shop,MODE(IF((Customer=F2)*(YEAR(DateVisited)=$I$1),IF(Shop<>"",MATCH(Shop,Shop,0)*{1,1})))),"")

Open in new window

0
 
Massimo ScolaAuthor Commented:
Hi

I've entered the formula but I get empty cells .. I've attached the spreadsheet.
Did I miss something?

Massimo
mode-by-year-problem.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
Maybe you are not entering this as an array formula. When you enter the formula in the cell press F2 then press shift-ctrl-enter
See attached
Copy-of-mode-by-year-problem.xlsx
0
 
Massimo ScolaAuthor Commented:
Hello and sorry for my late reply<br />Thanks a lot, it works now.<br /><br />Massimo
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now