Excel Magic Trick 1082: SUM Every Other Column: Four Formula Methods (Add Every Other Column)
HTML-код
- Опубликовано: 12 мар 2014
- Download Excel Start File: people.highline.edu/mgirvin/Y...
Download Excel File: people.highline.edu/mgirvin/Ex...
See how to add every other column using 1 or 4 formulas:
1. (00:08 min) Manual method using SUM. See how to highlight non-contiguous columns using a comma or the Ctrl key. This formula will work if you insert new columns before data set. This formula will work if there are text values in data set. Formula 1: =SUM(A2:A12,C2:C12,E2:E12,G2:G12)
2. (00:35 min) Formula to automatically add every other column using SUMPRODUCT, COLUMN, COLUMN with array operation, MOD functions and the math operator: *. This formula will work if you insert new columns before data set. This formula will NOT work if there are text values in data set. Formula 2: =SUMPRODUCT(A2:G12*MOD(COLUMN(A2:G2)-COLUMN(A2)+1,2))
3. (03:19 min) Formula to automatically add every other column using SUMPRODUCT, COLUMN, MOD functions and the math operator: *. This formula will NOT work if you insert new columns before data set. This formula will NOT work if there are text values in data set. Formula 3: =SUMPRODUCT(A2:G12*MOD(COLUMN(A2:G2),2))
4. (04:08 min) Formula to automatically add every other column using SUMPRODUCT, COLUMN, COLUMN with array operation, MOD, ROW, Array operation with exponent zero, and MMULT function. This formula will work if you insert new columns before data set. This formula will work if there are text values in data set. Formula 4: =SUMPRODUCT(A2:G12,MMULT(ROW(A2:A12)^0,MOD(COLUMN(A2:G2)-COLUMN(A2)+1,2)))
All are Array Formulas that does not require Ctrl + Shift + Enter because we use SUMPRODUCT.
Excel Magic Trick 1082: SUM Every Other Column: Four Formula Methods
(Add Every Other Column)
4 formulas:
=SUM(A2:A12,C2:C12,E2:E12,G2:G12)
=SUMPRODUCT(A2:G12*MOD(COLUMN(A2:G2)-COLUMN(A2)+1,2))
=SUMPRODUCT(A2:G12*MOD(COLUMN(A2:G2),2))
=SUMPRODUCT(A2:G12,MMULT(ROW(A2:A12)^0,MOD(COLUMN(A2:G2)-COLUMN(A2)+1,2)))
Nice Trick......,
Glad it helps!
Mike, you have a solution and answer to every problem I've ever had. just...thanks!
Dude, you rock!!!. I've subscribed and will be using this everyday. Thanks :)
Absolutely incredible.
Thanks for sharing your knowledge with us, Mike.
Maybe you don't know, but you have many fans in Brazil, 'cause I spread your knowledge and your RUclips Channel to my friends. :)
Almost perfect
I'm watching a lot of videos on RUclips when I need help with a new formula. It's the first time that they are so many people who explain this combination of formulas and it's not quite clear.
Can you please explain the part +1,2 after de second column formula please?
Thanks
This is sheer genius. I've been watching your videos for years and you still seem to be able to pull rabbits out of your hat and surprise the heck out of me. Raise to zero power? MMULT to secure arrays for SUMPRODUCT? Genius. I love it !
Glad you liked it!
I have used this trick a few other times, but with over 200 videos, it can be easy to miss it. I originally learned the MMULT to skip text from Aladin at the Mr Excel Message Board and the ROWS^0 from Domenic at the Mr Excel Message Board. It is great to be able to learn from all the cool Excel people online!!!
--excel "not so much a genius and more just a guy having fun with Excel" isfun
Great trick and great expalnation.When you do it everything is clear:)))
I have another one solution (only for table on video)
=SUM(IF(MOD(RIGHT(A1:G1),2),(A2:G12)))
CTRL+SHIFT+ENTER
Greetings :)))
Cool formula!
Hi I want to know the formula for say D4 and f4 and H4 and j4 without doing =SUM(D4+F4+H4 etc) When I try to highlight the second box the link is severed but if I was highlighting an entire row or column the formula would build seamlessly.
Hi, Can we also do every other sheet? I want to know if there is a way to Summing Across Multiple Sheets (same cell) excluding hidden sheets in between. Thank you
Hello. I've been trying to add D4 and f4 and H4 and j4 without doing =SUM(D4+F4+H4 etc). I hope you can help.
I have a row with different information including dates, text, and currency. Is there anyway that I can add all the currency up and not include the text and dates. This video shows columns, I'm interested in just adding the sum of the row and exclude the dates and text. The currency is every other cell if that helps. Thank you
I have two worksheets, the first one includes rows of text. I'm trying to copy the text of each cell in every other column to another column of adjacent rows. How can I do that?
Hi, can we add value of one column to another but erasing value in first column shouldn't effect in 2nd column, but adding value in first column shouldn't stopped effecting on 2nd column, i.e we want to add daily sales to the total sale achievement of other column till moth/year closing, thanx
hello there, i just wanna ask, how am I gonna put a data in first column that will sync in the second column without compromising the alphabetical order?
for example i have list of names(first name,last name in alphabetical order) then I want to add another name first column is the first name, second column last name. How am I gonna add it without compromising my data? ahhhrrrggg sorry
Namaskarams, I', Pavan, in the case of Subtotal?
i like it when you say COLUM lol
Got a question and I hope you can help me. I have a date column that has many dates in it.
ex:
14-mar
15-mar
5 jan
6 jan so on..
is there a way i can get the sum of the days for each month? I hope I explained itt correctly. Thanks
Try:
mrexcel.com/forum
Hi Mike,
I know it would be a longer formula, but could you use ISODD with COLUMN(A2:G2) to identify the odd numbers and incorporate it into INDIRECT somehow?
Like I said it would be a monster of a formula, but i was trying it this way and got lost.
Thanks for all the videos. I've learned a lot from you.
=SUMPRODUCT(A2:G12*ISODD(COLUMN(A2:G2)-COLUMN(A2)+1))
or
=SUMPRODUCT(A2:G12*ISEVEN(COLUMN(A2:G2)-COLUMN(A2)+1))
I'll make a video and post it...
Thanks Mike, I appreciate it.
Here is your video:
Excel Magic Trick 1084: ISODD or ISEVEN Functions to Help Add Every Other Column
How do you sum up all numbers without including the last number in a range?
How to add p2 ,p4.. like this
How do you skip 2 columns instead of 1?
Mike, have both your books and have found them very useful, brilliant reference material. Something still troubles me and i was hoping you could help. I want to match negatives numbers with positive numbers in over 1000 lines (ie -100 with 100), there are duplicate amounts, but essentially to leave me with any unmatched amounts.
Maybe Mr Excel and I can do a Duel on this for next week.
Maybe ABS function with ISNA and MATCH...
That would great if you guys could do that.
A formula like this would match -100 withh 100 in a second list:
=MATCH(ABS(B9),$A$9:$A$17,0)
-100 in B9 and 100 in A10.
Can you tell me more about your data? Can there be more than one occurrence of 100 or -100? What sort of data is this? Why do you need to delete it?
It's for an account analysis ie bank rec, don't want to delete the data just match up and filter on the unmatched items.
sweet mother of lord what did I just watch.
Why did the manual calculation change from 257 to 253?
I am also thinking the same
:) thnx... nthr one... =SUM(IF(MOD(COLUMN(C5:G21),2)0,C5:G21,0)) (CSE) .. .. gets if text but will change the value when column inserted...
Cool formula!
ExcelIsFun hey i need some help ..Check this IMAGE prntscr.com/7qpojl
.. i want to create a excel book which one row data is fixed and i want to copy to another row in a fixed cell. Thanks In Advance....