CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheet
Earthlien
Earthlien

Reputation: 13

How to count all items in the cells of a column in Google Sheets?

Can someone please help me with my Google Sheet? Basically, I need it to count all the items INCLUDING THE REPEATED ONES IN THE CELLS. As of right now, it's not taking them into account. I've marked the wrong results red.

I'm using COUNTIF and COUNTIFS functions. Perhaps there are some other better options. Any help is appreciated.

https://docs.google.com/spreadsheets/d/1lDB0eSt7J5fUFnDm0QuBv-ampXN8NTWN0ofBVqj9TQQ/edit?usp=sharing

Upvotes: 0

Views: 1807

Answers (3)

marikamitsos
marikamitsos

Reputation: 10573

The very nice formula in the answer given by JvdV, needs to be dragged down for the rest of the list.

Expanding the formula

You could further expand by trying the following in cell G1

=QUERY(TRANSPOSE(INDEX(TRIM(REGEXREPLACE(SPLIT(TEXTJOIN("+",TRUE,B:B),"+"),"(?:Purple|Blue|Pink|Green)","")))),
            "select Col1, count(Col1) where Col1<>''
             group by Col1 label count(Col1) 'QTY' ",1)

enter image description here

PROS

  1. A single self-expanding formula for all present and all future products
  2. All products become alphabetised
  3. There is no need for the extra column D where to manually write all products. This way you avoid human errors.

CONS

  1. The fact that all products are accounted for will be a problem if you want just some of them -like just the notebooks-

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60494

You need to create an array that has just one line in each element. Then you can use the Filter function.

To create the array:

split(textjoin(char(10),true,B:B),char(10))

Then, to filter and count:

=counta(filter(split(textjoin(char(10),true,B:B),char(10)),isnumber(find("Mini Notebook",split(textjoin(char(10),true,B:B),char(10)))),not(isnumber(find("cover",split(textjoin(char(10),true,B:B),char(10)))))))

You can use similar logic for the other rows.

Note that Find is case sensitive. If you don't want that behavior, use Search.

Upvotes: 0

JvdV
JvdV

Reputation: 76000

You could try:

enter image description here

Formula in E2:

=ARRAYFORMULA(COUNTIF(TRIM(REGEXREPLACE(SPLIT(TEXTJOIN("+",TRUE,B$2:B$20),"+"),"(?:Purple|Blue|Pink|Green)","")),D2))

Note that I altered the D-columns values a bit so they would actually match those values in B-column.

Upvotes: 1

Related Questions