Getting the max count of unique values

[Natit]

Hi there,
In column A I got all the unique values using =UNIQUE('data1'!A:A)
Then in column B I got the number of total appearance for each unique value using =COUNTIF('data1'!A:A,a2) that I dragged down
And then I got the highest result that I got in the countif formula using =MAX(b2:b100)
Is it a way to do it all in one formula?
TIA
Nati

 

[SunnyAlv]

like this ?

=LET(data,data1!A:A,MAX(COUNTIF(data,UNIQUE(data))))

 

[bobsan42]

=MAX(COUNTIF('data1'!A:A,UNIQUE('data1'!A:A)))

 

[Natit]

like this ?

=LET(data,data1!A:A,MAX(COUNTIF(data,UNIQUE(data))))

My sheet id Data1, what is "data"?

 

[bobsan42]

My sheet id Data1, what is "data"?

a variable which stores the values from data1!A:A
The advantage of using it is you only read the values once.

 

[SunnyAlv]

My sheet id Data1, what is "data"?

it just names a row address, so when you want to use that row address in your formula, you just call its name

 

[Natit]

My sheet id Data1, what is "data"?

=MAX(COUNTIF('data1'!A:A,UNIQUE('data1'!A:A)))

Works perfect
Thanks

 

[Natit]

it just names a row address, so when you want to use that row address in your formula, you just call its name

Got it and OfCourse it works