how do you total columns that are not adjacent

how do you total columns that are not adjacent but in the same row?

I saw this previous post but cannot figure it out:

"I want to add up, the first is at G4, the next is N4, then U4 etc etc...
until the last cell is BCZ4. So, every interval space of 6 cells. The values
are all in the same row.

reply:  Try this

=SUMPRODUCT((MOD((COLUMN(G4:BCZ4)-COLUMN($F$4)),7)=1)*(G4:BCZ4))"

I would like to:  begin with the amount in D7
                  subtract amount in E7
                  add amount in H7
                  provide answer in I7

is this possible?  if so, how?

really appreciate your help...will be a very long 
spreadsheet and these calculations will be beneficial.

thank you!




EggHeadCafe - Software Developer Portal of Choice 
Transparent NTLM Authentication With ADSI and ASP
http://www.eggheadcafe.com/tutorials/aspnet/d8163a66-4eb0-4c58-be5a-dd1b21b626c3/transparent-ntlm-authenti.aspx
0
Lyn
11/21/2009 1:45:00 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
789 Views

Similar Articles

[PageSpeed] 9

Maybe?

 =SUMPRODUCT((MOD((COLUMN(G4:BCZ4),7)=1)*(G4:BCZ4))-e7+h7

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

"Lyn Kolf" wrote in message news:2009112184447lynkolf@sbcglobal.net...
> how do you total columns that are not adjacent but in the same row?
>
> I saw this previous post but cannot figure it out:
>
> "I want to add up, the first is at G4, the next is N4, then U4 etc etc...
> until the last cell is BCZ4. So, every interval space of 6 cells. The 
> values
> are all in the same row.
>
> reply:  Try this
>
> =SUMPRODUCT((MOD((COLUMN(G4:BCZ4)-COLUMN($F$4)),7)=1)*(G4:BCZ4))"
>
> I would like to:  begin with the amount in D7
>                  subtract amount in E7
>                  add amount in H7
>                  provide answer in I7
>
> is this possible?  if so, how?
>
> really appreciate your help...will be a very long
> spreadsheet and these calculations will be beneficial.
>
> thank you!
>
>
>
>
> EggHeadCafe - Software Developer Portal of Choice
> Transparent NTLM Authentication With ADSI and ASP
> http://www.eggheadcafe.com/tutorials/aspnet/d8163a66-4eb0-4c58-be5a-dd1b21b626c3/transparent-ntlm-authenti.aspx 

0
Don
11/21/2009 2:20:36 PM
Hi,

I don't understand which is the question.

Is it this

=SUM(IF(MOD(COLUMN(G4:BCZ4),7)=0,G4:BCZ4))

or something else

Mike

"Lyn Kolf" wrote:

> how do you total columns that are not adjacent but in the same row?
> 
> I saw this previous post but cannot figure it out:
> 
> "I want to add up, the first is at G4, the next is N4, then U4 etc etc...
> until the last cell is BCZ4. So, every interval space of 6 cells. The values
> are all in the same row.
> 
> reply:  Try this
> 
> =SUMPRODUCT((MOD((COLUMN(G4:BCZ4)-COLUMN($F$4)),7)=1)*(G4:BCZ4))"
> 
> I would like to:  begin with the amount in D7
>                   subtract amount in E7
>                   add amount in H7
>                   provide answer in I7
> 
> is this possible?  if so, how?
> 
> really appreciate your help...will be a very long 
> spreadsheet and these calculations will be beneficial.
> 
> thank you!
> 
> 
> 
> 
> EggHeadCafe - Software Developer Portal of Choice 
> Transparent NTLM Authentication With ADSI and ASP
> http://www.eggheadcafe.com/tutorials/aspnet/d8163a66-4eb0-4c58-be5a-dd1b21b626c3/transparent-ntlm-authenti.aspx
> .
> 
0
Utf
11/21/2009 3:05:01 PM
Reply:

Similar Artilces:

An XML question
I would like to produce the following output based on my XML file: My Album (2005) Elapsed Time (hh:mm:ss): 00:07:00 Song 1: title1 Length (hh:mm:ss): 00:02:30 Song 2: title2 Length (hh:mm:ss): 00:02:15 Song 3: title3 Length (hh:mm:ss): 00:02:15 ===== <album> <general> <title>My Album</title> <year>2005</year> </general> <content> <song> <songTitle>title1</songTitle> <songLengthInSeconds>150</songLengthInSeconds> </song> <song> <...

Moving a value in Column D
I have a spreadsheet with values in a couple of cells in Column E. The rest of the cells are blank. I would like a macro that would move the value of any cell containing data (non empty cells) four cells backwards and two cells downwards to Column A. Is there a macro that would do this. -- Message posted via http://www.officekb.com Sub MoveIt() Dim rCell As Range For Each rCell In Columns("E") _ .SpecialCells(xlCellTypeConstants) rCell.Offset(2, -4).Value = rCell.Value rCell.ClearContents Next rCell End Sub HTH -- AP "mohd21uk via OfficeKB.com" <u20517@u...

Totals on Sales Inquiry native windows
We would like to have the totals on the Sales Inquiries: Transaction by Customer Transaction by Document Sales Documents Sales Items Today we can retrieve this information in separated inquiries and reports, be we would like to heve a easier way to see this totals... The same suggest can be applied to Smartlist. If possible, you can create a tool to sum a field after you make a filter I created a screen where you can see all sales, separated by day of week, and week of year, and totals YTD,MTD and WTD, but I am not able to have the same information grouped by item number, for example.....

Re: fuction to copy Column a and b to comment or colum c?
Please help!!! I would like to know a fuction or a way to just recop what ever is in colum a and c to comment in colum d. Is this eve possible? If not maybe a fuction to put values of colum a and c t colum d. Please keep in mind this would prevent me from typing again. Thanks!!! -- Message posted from http://www.ExcelForum.com You can concatenate the values in A & C in column D with a formula: =A1&C1 or =A1&" "&C1 if you want those values in a comment, you'll need a little code. This is a sub (not a function): Option Explicit Sub MakeComments() Dim...

total cost on report
I have areport with a cost field that I would like to total that collum at the end of the report. How do I do this ? do I need to create another field (total) please help thanks, jettro Jettron wrote: >I have areport with a cost field that I would like to total that collum at >the end of the report. How do I do this ? do I need to create another field >(total) The usual way is to use a text box with an expression like: =Sum([cost field]) -- Marsh MVP [MS Access] I'm in design view of the report, where do I insert this expression? I clicked on page footer to insert thi...

Setting ID# to 1 & Total on bottom of query result
Hi, I have 2 separate questions. First how do I set the ID# to 1 for the first record. I copied my whole database including queries and forms which were 2009 records and changed the titles to 2010. I did a "compact and repair" to the 2010 database. But when I start to enter records for 2010 the ID# is 2550. How can I reset the ID# back to start at 1? On many of my queries I have the results be for example 28 Caucation, 43 African American, 65 Hispanic, 4 Asian, 2 Native American. I need these numbers separate like they are but I would like a total line at the bottom...

macro
I have several columns in an income sheet. One column has a sum at the bottom. I want to be able to run a simple macro that will copy the last row of formatted cells, just above the sum total, and insert it as a new row just above the sum total so another line of data can be input. But I want the sum total to sum also the newly inserted row. Presently I can't figure out how to do it. Example A B C 1 24 Oct Dollars In $100 2 25 Oct Dollars out $25 3 Total ...

How to make chart columns thicker
Hi I am using excel 2007. I am trying to create a chart with 20 weeks in the x axis. The chart type is Column, clustered column. The chart displays two lots of data. The problem is, the columns are displayed as very thin, in fact they're little more than lines, yet there is a lot of room on the chart for them to be much thicker. How do I make the columns thicker? The chart just looks daft at the moment. Thanks for any help Colin Ok, brain in gear now, I've worked out what was wrong. The problem was that the x-axes was recognising the data as a date. I told it that it was text by ...

Printing Long Columns
I have a simple worksheet with order numbers in column A and corresponding completion dates in column B. Since there are over 1,000 orders, it takes many pages to print the sheet, with a great deal of paper wasted on the blank area to the right of the two columns. Other than the tedious process of cutting and pasting, is there a way to arrange the information into multiple multiple columns across the page? Try this example on David's site Charlie http://www.mvps.org/dmcritchie/excel/snakecol.htm -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Cha...

Finding Cells that Total a Value
Hello Friends, I need some assistance in solving a problem. I have a spreadsheet with over five hundred lines of transactions. The sum of these transactions are creating a balance on the account. Is there any formula/macro that will help me find the transactions creating the balance? The sum of the account should be zero. To clarify, if we owe client money, there would be a transaction setting up that postive balance then a payment on the account taking it back to zero. There could be multiple transactions and then one net payment. Or we could be due to receive. So at the end of the day, th...

Stacked Column Chart Data Label Connector Lines
I can't for the life of me figure out how to place the data labels for a stacked column chart (# or %) (1) next to the column, then (2) have lines connecting the data value to the proper block of the column. It does it automatically for pie chart labels, but not columns. Anyone have insight into these two issues? Thanks in advance. Not a solution, but explaination: XL won't let you place the labels by design to the side because its possible to place stacked columns next to each other (gap width = 0). This would then make it look like the values were associated with wrong col...

Total Items
Hi - Is there a way to get a total number of items that are about to be tendered, on the POS screen ? Thanks. You can customize the status.htm file. Not sure exactly what the variable is that you want, but I know it cn be done. "NJS" <NJS@discussions.microsoft.com> wrote in message news:538F3CC7-F23D-43C7-8A5A-4319B8688E5B@microsoft.com... > Hi - > Is there a way to get a total number of items that are about to be > tendered, > on the POS screen ? > Thanks. Jason, Send me your email I have already customized this and will forward you. "Jason&qu...

HOW TO SORT 4 COLUMNS
I HAVE A CUSTOMER LIST THAT I NEED TO SORT IN THIS ORDER, ST (COLUMN G), CITY (F), ZIP (H) & NAME (A). RANGE IS A6:M565. THANKS. VICTOR select data sort and pick the rows you want to sort by, If you want it in VBA then use your macro recorder, but before you record the macro practice on it first, there is nothing more maddenning than having to record he macro 5 or 6 times because of forgetting something. then from the forms button select a command button and assign a macro to it I should explain that I need to sort the criteria in those column and it only allows three. Consequentl...

Formulas sometimes will not work in a Column or cell?
I am perplexed a bit with an annoying problem that is user error I am sure: Most of the time, I can go to a cell and type in =F8+G8 and I get wonderful number that comes out. But other times the output will be "=F8+G8" and the whole column is that way? I think that this is because the column is text format.. so I select the column and format for Numeric- but nothing happens I still get "=F8+G8"- What can I do to format that column so I can do some formulas? Thanks brent You're correct about the cell being formatted as text. But it's not enough to just chang...

Total of Totals
Hey there, I've set up a database consisting of four tables. I run a query which calculates a total for the item (unit price * quantity). Is it possible to calculate the final total of all this for a report? If that's possible, how can I get it to be at the bottom of all the records but only on the last page? Thanks Mathew On Aug 1, 12:48 am, KARL DEWEY <KARLDE...@discussions.microsoft.com> wrote: > Yes. > Open the form in design view, click on menu VIEW - Report Header/Footer a= nd > add a text box in the footer with Control Source =3D[unit price] * > [quantit...

How to change the individual row into columns
In Excel If each row looks like the below given format Michael 20, Eastman Avenue Boston US David 56, George Patel Road Texas US Michael 20, Eastman Avenue Boston US David 56, George Patel Road Texas US Michael 20, Eastman Avenue Boston US David 56, George Patel Road Texas US I want to change it to be the following format (Given below). Is i possible to do that? If so, what are the steps to be followed? I nee your help. Please reply this mail. Thanks in Advance. Michael 20, Eastman Avenue Boston US David 56, George Patel Road, Texas US Michael 20, Eastman Av...

Macro/SUB to merge two columns of text
EXCEL 2003: How do I write a VBA subroutine to: (1) Take each cell in the currently-SELECTED column (2) Append to its (text) contents the text in the corresponding cell of the NEXT column (3) Replace the result in the original cell (4) On completion, delete the whole of the NEXT column? All text values need to have leading and trailing blanks TRIMMED before being concatenated. This is to cope with the situation where downloaded data contains information split over two columns that the worksheet needs to have in one, e.g. company name and branch location. Sub m...

Manipulating totals and columns in Pivot Tables
Hi, I'm constructing a pivot table that has investors as columns, investments as rows, and the general data is values. Trouble is there is a column which I want to only display half of each value. I know that I can make a formula that divides a column "column A/2", and then hide the unwanted columns. But I would also like the hidden columns to not be included in the grand totals. How do I make this happen? Thanks! Hi, So let me understand what you want, something like this in the Pivot Table: A B C Name Jp ...

Pole display Sub Total
Hi All, When a client of mine scans items the amount displaying is the sub total. Is there any way to display the total (ie including tax amount) Regards Amanda ...

Print Visio columns?
...

how do I get a total for all worksheets in my workbook?
I have ten worksheets, each with a total. How do I get a total of all totals? Considering you have 3 sheets, and the totals are in cell A1, then use: =SUM(Sheet1:Sheet3!A1) Mangesh "Aileen Hewat" <AileenHewat@discussions.microsoft.com> wrote in message news:18C80118-642F-4690-8B5E-7D68B63752E3@microsoft.com... > I have ten worksheets, each with a total. How do I get a total of all totals? This file might be a help: http://www.bygsoftware.com/examples/zipfiles/consol.zip It's in the "Accountants" section on page: http://www.bygsoftware.com/examples/exa...

Importing CSV file into single column/multiple rows vs. multiple rows/single column
Hello, I need to know how to import data into a single column with multiple rows. By default, when importing a CSV or TAB txt file, it imorts data across multiple columns in one row but I need it to go into one column and multiple rows. I don't have the option on the import wizard. Thanks, Brian Brian, Open it as usual, then do a copy pastespecial transpose. HTH, Bernie MS Excel MVP <bjorgenson@charter.net> wrote in message news:1119877803.916313.176730@g14g2000cwa.googlegroups.com... > Hello, I need to know how to import data into a single column with > multiple rows. B...

Setting column labels to letters
I just noticed that my worksheet column labels (across the top) are numbers instead of letters. Don't think it's always been that way, but I want to change it now and haven't found anything yet in Tools or Help that references it. Would someone please point the way. thanks, Gene Goldenfeld Gene, Go to the Tools menu, choose Options, then the General tab. There, uncheck the "R1C1 reference style" setting. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Gene Goldenfeld" <genegold@highstream.net>...

Calculate Total Books
I am preparing packing slips for boxes to be sent out. There are different book orders going into the same box. Each book in the order will have its own set of id numbers that are usually, but not always, in a string(101-121 etc). I use a calculated field on the form to display the number of books in the string as I store the start book number and subract it from the end book number and add 1 to get the actual number of books in the string. I use the same calculated field on the report to show the number of books in each detial, but I have multiple strings in 1 box. I need to show a...

Using fill with increasing columns.
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel In one sheet (sheet 2) of my document I have a list that spans multiple columns. In the other sheet (sheet 1) the list is in multiple rows in the same column. <br> I want: <br> a1 of sheet 1 to equal a1 of sheet 2 <br> a2 of sheet 1 to equal b1 of sheet 2 <br> a3 of sheet 1 to equal c1 of sheet 2 <br> etc. <br> I can do this easily enough manually, but I cannot figure out how to do this using the fill function. The current function I am using is: <br> a1='sh...