Count if for every other row

[Guest]

I have this formula that adds every other column in my sheet.

=SUM(AH16,AJ16,AL16,AN16,AP16,AR16,AT16,AV16,AV17,AX16,AZ16)

I'm trying to countif the columns next to these that are 1 or greater. the
ways I've tried the formula return errors.

Any insight would be appreciated!

Thanks!

 

[RagDyer]

Try this to add your columns:

=SUMPRODUCT((MOD(COLUMN(AH16:AZ16),2)=0)*AH16:AZ16)

And try this to count the adjoining columns that are >=1:

=SUMPRODUCT((MOD(COLUMN(AI16:BA16),2)=1)*(AI16:BA16>=1))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Ron Rosenfeld]

I have this formula that adds every other column in my sheet.

=SUM(AH16,AJ16,AL16,AN16,AP16,AR16,AT16,AV16,AV17,AX16,AZ16)

I'm trying to countif the columns next to these that are 1 or greater. the
ways I've tried the formula return errors.

Any insight would be appreciated!

Thanks!

Something like:

=SUMPRODUCT(--((AI16:BA16)*(MOD(COLUMN(AI16:BA16),2)=1)>=1))

and for consistency, you could rewrite your first, SUM formula as:

=SUMPRODUCT(AH16:AZ16*(MOD(COLUMN(AH16:AZ16),2)=0))

Discussion:

The columns you enumerated in your first formula are all even numbered columns;
hence the MOD function to isolate them.

I assumed when you said the "columns next to these that are 1 or greater", you
meant to look at the contents of every other column from AI16:BA16 and see if
the contents were one or greater. These, of course, would be odd numbered
columns.


--ron