When I apply COUNTIF to a range that contains calculations, I get an error message. To avoid that, can I also narrow the range only to certain columns?

For example, instead of =COUNTIF($A$1:E;F1) something like =COUNTIF($A$1:A AND $C$1:C AND $E$1:E;F1)

So that certain columns like B and D in my example are not included in the range?

CodePudding user response：

You can join the columns with {} and separating columns with "," like this:

=countif({A1:A;C1:C;E1:E},F1)

CodePudding user response：

Try using COUNTIFS instead of COUNTIF with range and criteria as key-value pairs.

=COUNTIFS(A1:A, "=" & F1, C1:C, "=" & F1, E1:E, "=" & F1)

Make sure to replace commas with semicolons for the formula to work in your locale.