Countif in non adjacent columns
 

Is it possible to use countif to count the number of times a certain number
appears in column A, C, E...etc through to W, but ignoring columns B, D, F,
etc through to X

I can only get the countif to work if I use the full range (A1:X1)

Countif in non adjacent columns
 

Just add them up:

=COUNTIF(A:A,13)+COUNTIF(C:C,13)


If you have many columns, it is better to use SUMPRODUCT()
--
Gary''s Student - gsnu200806


"mickjjuk" wrote:

Is it possible to use countif to count the number of times a certain number
appears in column A, C, E...etc through to W, but ignoring columns B, D, F,
etc through to X

I can only get the countif to work if I use the full range (A1:X1)

Countif in non adjacent columns
 

Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=SUM(IF(MOD(COLUMN(A1:X1)-COLUMN(A1),2)=0,IF(A1:X1=A5,1)))

....where A5 contains the number of interest.

Hope this helps!

In article ,
mickjjuk wrote:

Is it possible to use countif to count the number of times a certain number
appears in column A, C, E...etc through to W, but ignoring columns B, D, F,
etc through to X

I can only get the countif to work if I use the full range (A1:X1)

Countif in non adjacent columns
 

Thanks for the help, it works fine. This seems like the 'tidier' option of
the 2 suggestions.

"Domenic" wrote:

Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=SUM(IF(MOD(COLUMN(A1:X1)-COLUMN(A1),2)=0,IF(A1:X1=A5,1)))

....where A5 contains the number of interest.

Hope this helps!

In article ,
mickjjuk wrote:

Is it possible to use countif to count the number of times a certain number
appears in column A, C, E...etc through to W, but ignoring columns B, D, F,
etc through to X

I can only get the countif to work if I use the full range (A1:X1)