How can I 'CountIf' alternate cells?

[John Blackwell]

We're using large excel 2007 workbooks and I'd like to apply the CountIf
function to alternate cells in a row. The rows are D22:KC22 and contain
numeric entries (from 1 to 5).

I want to count the number of "1" entries in each alternate cell.

What's the best way of achieving this?

 

[Tom Hutchins]

Try

=SUMPRODUCT(--(MOD(COLUMN(D22:KC22),2)=0),--(D22:KC22=1))

for alternate columns with even numbers (D, F, H, etc.) For alternate
columns with odd numbers (E, G, I, etc.) use

=SUMPRODUCT(--(MOD(COLUMN(D22:KC22),2)=1),--(D22:KC22=1))

Hope this helps,

Hutch

 

[John Blackwell]

Spectacular Tom, a great result. I can't profess to actually understand the
formula but it works a treat.

Thank you,

John