• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • 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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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