2

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.

2

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.