Reputation: 13
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
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)
PROS
D
where to manually write all products. This way you avoid human errors.CONS
Upvotes: 1
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
Reputation: 76000
You could try:
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