advertisement
Forums

The Forum is sponsored by 
 

AAPL stock: Click Here

You are currently viewing the Tips and Deals forum
Google Sheets data counting question
Posted by: gabester
Date: January 13, 2022 10:16AM
I have a list of data, and I need to get not a count of the unique values, but the count of each unique value.

That is, if my data were:

Dog
Dog
Dog
Cat
Cat
Bird
Mac
Mac
Mac
Mac

I'd want to see
Dog 3
Cat 2
Bird 1
Mac 4

In Excel I'd perform a subtotal, but I never really understood how this worked and I always had to do something hinky like create an additional column and fill it with "1" so that those would get counted for the text value I actually wanted to accumulate.

I think PivotTables can also be used for this purpose...?



g=
Options:  Reply • Quote
Re: Google Sheets data counting question
Posted by: MikeF
Date: January 13, 2022 11:24AM
In Excel, "Countif", "Concatenate". Not sure about Google sheets.

If cells containing dog/cat/bird/mac are a1:a10, then formula would be "=COUNTIF(A1:A10,"Dog")". That will get you 3.

If you want that count in another cell with "Dog " in front, use CONCATENATE("Dog ",xxx) where xxx is the cell of the count. If you don't want the count in another cell,
"=CONCATENATE("Dog ",COUNTIF(A1:A10,"Dog"))"



Edited 1 time(s). Last edit at 01/13/2022 11:26AM by MikeF.
Options:  Reply • Quote
Re: Google Sheets data counting question
Posted by: bhaveshp
Date: January 13, 2022 11:26AM
This should do it if data is starting in A2:

={UNIQUE(A2:A101),ARRAYFORMULA(COUNTIF(A2:A101,UNIQUE(A2:A101)))}

source: [www.modernschoolbus.com]



Edited 1 time(s). Last edit at 01/13/2022 11:28AM by bhaveshp.
Options:  Reply • Quote
Sorry, only registered users may post in this forum.

Click here to login

Online Users

Guests: 100
Record Number of Users: 186 on February 20, 2020
Record Number of Guests: 5122 on October 03, 2020