0
316
ViewsIs there any way to use Countif() without overcounting?
Introduction
I'm using an array formula (ctrl+shift+enter) =Sum(countif()) to count in a range how many times some keywords appear. My problem is that if in one cell of that range there is more than one keyword, that cell is counted multiple times (once per keyword) and I don't want that.
Example
I have a table. From A1 to A7 I have text in each cell. Each cell contains colors. It can be one color such as "Red", or many such as "Red, Green, Blue". I want to calculate how many times "Red", "Green" or "Blue" appear in the range. I tried using the formula =Sum(countif(A1:A7;{"*"Red*";"*Green*";"*Blue*"})), but if a cell contains more than one of these colors, the cell is counted multiple times, and that is not the idea.
Question
Which formula should I use? In case there is no function or combination of functions to do this, do you know how to create a macro to create the needed formula? I have created some formulas in macros, but all of them simply combined formulas that already existed in excel so that I didn't need to write all the long formulas each time I want to use them and instead I could simply import my shorter formula.
Notes
- I use "," for decimal punctuation, that is why I separate arrays and functions with ";".
- Feel free to change the title to something more appropriate. I'm not sure how to summarize my problem in a question for the title. If you do so, then remove this note.
2 answers
Answer question0
All Versions:
=SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH({"Red","Green","Blue"},A1:A6)),ROW($ZZ$1:INDEX($ZZ:$ZZ,COLUMNS({"Red","Green","Blue"})))^0)>0))
Realize that for me {"Red","Green","Blue"} creates a horizontal array. Change the , to the delimiter to make it a horizontal array in your local settings.
And, not sure about this one, but in older version this may require the use of Ctrl-Shift-Enter instead of Enter when exiting edit mode.
0
You can try below formulas
=SUM(--(MMULT(--(--ISNUMBER(SEARCH({"Red","Green","Blue"},A1:A6))=1),SEQUENCE(3))>0))
In here in the sequence 3 because you have 3 values(Red, Green, Blue) if you have more or less just increase or decrease the sequence
you can also use this one
=COUNT(VALUE(IFERROR(SEARCH({"red"},A1:A6),"")&IFERROR(SEARCH({"blue"},A1:A6),"")&IFERROR(SEARCH({"green"},A1:A6),"")))
