Google-sheets – Using ARRAYFORMULA to do two functions

google sheetsgoogle-sheets-arrayformula

I have a spreadsheet like this:

4.5
5.0
4.5
5.1
5.1
4.5

And I want to achieve a range like this:

4.5 | 3
5.0 | 1
5.1 | 2

I can achieve this with two formulas UNIQUE and COUNTIF, but – as far as I know – that requires me to have something like this:

=UNIQUE | =COUNTIF
        | =COUNTIF
        | =COUNTIF
        | =COUNTIF
        | ...

How could I condense this down? I know ARRAYFORMULA is probably in the right ballpark, but I'm wondering if there's even a single-celled formula way to achieve this (eg something like ={UNIQUE,ARRAYFORMULA(COUNTIF)} or some sort of notation to do two different formulas at once)

EDIT:

After a bit of messing around, I was able to condense it down to something along the lines of

=UNIQUE | =ARRAYFORMULA(COUNTIF(range,FILTER(unique_range,unique_range<>"")))

But still wondering if there's a better way to do this.

Best Answer

You can use a QUERY formula for that

=QUERY(J20:J,"select J, count(J) where J is not null
                group by J label count(J) '' ",0)

enter image description here

Functions used:

Related Topic