0

I would like to populate a cell which gives me the date where sales was the highest for that period. eg. if I have 2 months, I would like to have a cell saying 14/01/2023 if that was the day with the highest sales. Q - sales values R - Year I can get the highest sales value for the year:

=MAXIFS(Pivot!Q2:Q1000, Pivot!R2:R1000, "2023")

but struggle to get the highest date using similar as it just brings back the max date.

I have another column P which is the date. Anyone have any thoughts as to how to do this? To recap I want the date of the most sales - this year.

Thanks

vimuth
  • 5,064
  • 33
  • 79
  • 116
c492681
  • 1

1 Answers1

0

Can you try:

=filter(Pivot!P2:P,Pivot!R2:R=2023,Pivot!Q2:Q=max(Pivot!Q2:Q))
rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
  • thanks for the reposnse - I got this error message: Error FILTER has mismatched range sizes. Expected row count: 999. column count: 1. Actual row count: 1000, column count: 1. – c492681 Mar 07 '23 at 08:23
  • can you try the updated one... – rockinfreakshow Mar 07 '23 at 08:25
  • Thanks - I now get no matches are found in filter evaluation: PivotDate(P) Portfolio Total(Q) Year(R) 20230101 $1,081 2023 20230113 $1,091 2023 20230114 $1,127 2023 This is an example of the typical data by the way. – c492681 Mar 07 '23 at 08:34
  • https://i.imgur.com/JIcJ1v2.png Here's how it generally works based on your sample data. – rockinfreakshow Mar 07 '23 at 08:38