 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / March 2008Adding cells in every other column
Hello, I am using Excel 2007 with Win XP. I am trying to create a current inventory count in column B of each row of items. The count as inventory comes in or goes out will be located on the same row, in every other column starting in column H. In other words the count will be in cells H7, J7, L7, N7 and so on. There is no ending column - no definate place the count will stop. It will be hundreds of columns, so I don't want to do a formula such as =sum(H7+J7+L7 .....etc) I would like this in a formula format so that I can drag the formula down in the spreadsheet to each row for each item that I am tracking. Is there a way to sum specific columns other than the formula above? Thanks again for everyone's help! Mark One way: =SUMPRODUCT(--(MOD(COLUMN(7:7),2)=1),7:7) > Hello, > [quoted text clipped - 17 lines] > > Mark It comes up with 0 (zero); and a circular reference error. > One way: > [quoted text clipped - 21 lines] > > > > Mark =SUMPRODUCT(--(MOD(COLUMN(H7:IV7),2)=1),H7:IV7) You could use the column in which your data ends instead of IV, too. > It comes up with 0 (zero); and a circular reference error. > [quoted text clipped - 23 lines] > > > > > > Mark  SignatureDave Peterson Awesome Dave, that worked! I appreciate it very much. After seven years of working with excel you would think I would know/remember these things.... Thanks again. Mark > =SUMPRODUCT(--(MOD(COLUMN(H7:IV7),2)=1),H7:IV7) > [quoted text clipped - 27 lines] > > > > > > > > Mark This kind of formula that depends on the column number that contains the value always scares me. If columns are inserted/deleted, it could break the formula--or even worse, the formula may work--but just show a value that isn't what you really want. I think I'd add a row (and hide it later). Then use an indicator in that row. =sumif($h$1:$iv$1,"$",$h7:$iv7) > Awesome Dave, that worked! > [quoted text clipped - 40 lines] > > > > Dave Peterson SignatureDave Peterson Dave, one more quick question. How would I change your formula to do the same thing, but have it add up columns G,I,k etc, in other words one column before the h column - as in your formula provided. I tried to change your formula from H7 to G7 but I get the same result? Your formula starts with the seventh column and adds ever other column. I also need to have a formula that starts in the sixth column and adds every other column thereafter. I'm confused (still). Thanks, Mark > =SUMPRODUCT(--(MOD(COLUMN(H7:IV7),2)=1),H7:IV7) > [quoted text clipped - 27 lines] > > > > > > > > Mark =SUMPRODUCT(--(MOD(COLUMN(g7:IV7),2)=0),g7:IV7) The =mod(..., 2) will return a 0 or 1. 1 means you're in an odd numbered column. 0 means you're in an even numbered column. But I'm confused about what you want. Column G is column 7. That's an odd number. You could add the odd numbered columns starting with G: =SUMPRODUCT(--(MOD(COLUMN(g7:IV7),2)=1),g7:IV7) You could add the even numbered columns starting with G: =SUMPRODUCT(--(MOD(COLUMN(g7:IV7),2)=0),g7:IV7) ==== I still like the (hidden) row of indicators and =sumif(). > Dave, one more quick question. How would I change your formula to do the > same thing, but have it add up columns G,I,k etc, in other words one column [quoted text clipped - 43 lines] > > > > Dave Peterson SignatureDave Peterson Sorry for the confusion, my fault, I changed my spreadsheet around a bit inbetween postings, I moved some columns around. I always try to learn something when I get stuck, that's why I appreicate everyone's response to my various questions. What would happen if I wanted to count every third column? Is this possible since with the formula result being true or false? True being even, false being odd? As far as the hidden row, I understand your concern, but I have hundres of rows of inventory items, so I would have to have hundreds of rows of indicators. It's shouldn't be a problem with adding rows, that side of the spreasheet is fairly basic and I don't see any changes ever being made. Whenever I make a change to a spreasheet like that, I usually start over from scratch and do things differently, more efficiently, etc. In any event, again, thanks very much for your help I appreicate it very much. > =SUMPRODUCT(--(MOD(COLUMN(g7:IV7),2)=0),g7:IV7) > [quoted text clipped - 60 lines] > > > > > > Dave Peterson You wouldn't need hundreds of rows of indicators. You'd just check the single row in the formula. Notice the $ signs around $h$1:$iv$1 in this formula: =sumif($h$1:$iv$1,"$",$h7:$iv7) That means that this range won't change when you copy the formula to other locations. =mod(...,2) will return 0 or 1 =mod(...,3) will return 0, 1, or 2 =mod(...,18) will return whole numbers between 0 and 17. So you can change this portion: MOD(COLUMN(g7:IV7),2)=0 to MOD(COLUMN(g7:IV7),3)=0 (or 1 or 2--depending on what column you want) > Sorry for the confusion, my fault, I changed my spreadsheet around a bit > inbetween postings, I moved some columns around. [quoted text clipped - 84 lines] > > > > Dave Peterson SignatureDave Peterson Thanks Dave, That makes perfect sense to me know. I assumed each inventory row would need an indicator row since there would be multiple entries on each row from various columns used. The MOD function is a nice little tool; I will be able to use that in another project that I will be working on soon. Thanks again! Mark > You wouldn't need hundreds of rows of indicators. You'd just check the single > row in the formula. [quoted text clipped - 105 lines] > > > > > > Dave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.