Forum Discussion
How to count occurrences of different multiples in one list
I have a list of 1430 rows, and 4 columns. It's a shirt designs inventory. One of the columns is the folder name where the design is located. For example, this column is structured like this.
Folder A
Folder A
Folder A
Folder A
Folder B
Folder B
Folder C
Folder C
Folder C
Folder C
Folder C
Folder C
Folder D
Folder D
Folder D
I need to count how many times each different folder name occurs. The output I desire is a vertical, two-column list of numbers, and their corresponding folder name, as follows.
Folder A 4
Folder B 2
Folder C 6
Folder D 3
There are 77 or 78 folder names among the 1430 rows. Given the concise output I want, I'm assuming this will be the spill ed result of an array function.
One option is to create a Pivot Table based on the data.
Add the folder column/field to both the Rows area and to the Values area.
Another option:
In one empty cell, let's say in K2, enter the following formula:
=SORT(UNIQUE(folder_name_range))
In the cell next to it, i.e. in L2, enter the formula
=COUNTIF(folder_name_range, K2#)
4 Replies
I see there's already a solution. In the future (later in 2024 maybe) this formula could be used:
=GROUPBY(folders,folders,COUNTA,,0)indyhighlander You can use the following formula to make it spill array-
=HSTACK(UNIQUE(A1:A15),COUNTIFS(A1:A15,UNIQUE(A1:A15)))One option is to create a Pivot Table based on the data.
Add the folder column/field to both the Rows area and to the Values area.
Another option:
In one empty cell, let's say in K2, enter the following formula:
=SORT(UNIQUE(folder_name_range))
In the cell next to it, i.e. in L2, enter the formula
=COUNTIF(folder_name_range, K2#)
- Thank you so much! The UNIQUE function with the COUNTIF function was the 100% correct solution! That was too simple to believe. I talked myself out of the UNIQUE solution because I thought it would only return items that occurred exactly once in the list. This new information helps me see the difference between my shirt design source files and my design images, which are in two different structures. Now I'm able to reconcile the two, and make sure I have included all of my designs in the Publisher files as PNGs to upload to my shop! You're the best.
