Adding only numbers in a row in non-contiguous columns

Topic

New Reply

I have 12 columns with either a number or N/A. Each of the columns is separated by 6 other columns with data I am not concerned with. What I would like to do is add all of the numbers in the 12 columns in a particular row. Any help?

Reply | Quote

Replies

Hi

Supposing your data is in row 2, with the first value you are interested in is in in column B, the next in I and so on
then the following array entered formula will do what you want.

{=SUMPRODUCT(–(MOD(COLUMN(B2:CG2),7)=2),–(IF(ISNUMBER(B2:CG2),B2:CG2,0)))}

To array enter a formula use Control+Shift+Enter (CSE) not just Enter.
When you use CSE, Excel will insert the curly braces { } around your formula.
Do not type them yourself.

Reply | Quote