How can I 'CountIf' alternate cells?

  • Thread starter Thread starter John Blackwell
  • Start date Start date
J

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?
 
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
 
Spectacular Tom, a great result. I can't profess to actually understand the
formula but it works a treat.

Thank you,

John
 
Back
Top