Basic array formula question

[Norman8088]

I would like to write a formula that counts the number of times any one of multiple conditions appear in a column cell.

I know this notation is incorrect, but what I am trying to do is basically this:

=countif(a:a "ABC", "DEF", "GHI", "JKL")

...to count the number of times either ABC, DEF, GHI, or JKL appear in column A. What am I doing wrong in my notation, and how could I use an array formula to count these conditions?

Thank you very much, in advance, to anyone who can offer any help with this!

[zbor]

Hi Norman,

maybe this would help (normal enter for formula):

=SUMPRODUCT(COUNTIF(A1:A10, {"ABC", "DEF"}))

Or this if you have reference instead of ABC....

=SUMPRODUCT(COUNTIF(A1:A10, C1:C5))

Note: Even if you can use whole range (As A:A) with SUMPRODUCT formula (or any other array formula) it's better to keep range at minimum point.

[scottylad2]

=COUNTIF(A:A,"abc")+COUNTIF(A:A,"DEF")+COUNTIF(A:A,"GHI")

[Colin Legg]

This formula can be entered normally:

Please Login or Register  to view this content.