Results 1 to 2 of 2
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Adding only numbers in a row in non-contiguous columns

    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?

  2. #2
    Lounger
    Join Date
    Apr 2012
    Location
    Abergavenny, Wales, UK
    Posts
    25
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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.
    Regards
    Roger Govier
    Microsoft Excel MVP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •