I need everyone's help on how I can sum all values by skipping one column in between.

Example: I have raw data with numbers from A1 till Z1 I want to add/sum all numbers but skipping one column. Only add A1, A3, A5, and so on (further than Z1).

Thank you for the help.

3 Answers

Or you also try this：
Sum of odd columns:

=SUMPRODUCT((MOD(COLUMN($A$1:$H$1),2)=1)*$A$1:$H$1)

Sum of even columns:

=SUMPRODUCT((MOD(COLUMN($A$1:$H$1),2)=0)*$A$1:$H$1)

If you want to sum odd/even rows later, you could change COLUMN to the ROW function and apply it to the corresponding cells.

Use this formula to sum all odd-numbered columns from A1 to Z1:

 =SUM(IF(MOD(COLUMN($A$1:$Z$1),2)=1,$A$1:$Z$1,0))

1

Could take a slightly different approach:

=SUM(INDEX(A1:Z1,1,SEQUENCE(1,ROUNDUP(COLUMNS(A1:Z1)/2,0),1,2)))

INDEX() will let you choose all the odd columns, if you can figure out how to. Making a SEQUENCE() that starts with 1, goes up 2 at each step, and knows how many steps to take does that.

How many steps? Well, say you have nine columns. So columns 1,3,5,7,and 9. Five in total. One way would be to count the columns and add one if odd, or none if even, then divide by 2. That seems... hard... in that you'd need a bit of complication rather than just dividing the number of columns by 2 right away and rounding up the number. If it was an even number of columns (26 of them, say), the division would be an integer (.0 for the decimal part) and stay the same when rounded up. If odd, it'd have ".5" for the decimal part and round up. So in the example I gave with 9 columns, it would give 4.5 and round up to the desired 5.

If you use LET(), it could look like this:

=LET(Range,A1:Z1, SUM(INDEX(Range,1,SEQUENCE(1,ROUNDUP(COLUMNS(Range)/2,0),1,2))))

And if you had to change the cells it summed, say to enlarge the number of columns, you just need to do that in one place, not two, and the one place is right at the start of it.

One way to avoid even having to do that would be to use a range that goes way past column Z so you can keep adding columns and the formula still works. It would be OK because the extra columns would have 0's and not affect the sum.

A better way would be to make the formula out for one extra column and use Insert to add any new columns. Excel would adjust the formula. That won't work though if you have users and they wouldn't Insert but rather would just type into the exta columns without any further thought. Then the former way would be better.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy