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

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.

CodePudding user response：

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.