Sum A Named Range Across Multiple Columns

Here's another fun one that's sucked up days of my life with no
progress.

I have groups of columns representing the days of the week where
numbers representing amount of work done (just simple whole number
quantities) go. I have every other column named as Budget for the
budget of work to be done, and every other column beside those named
Actual, for actual work done.

So basically on this worksheet, columns A, C, E, G, I, K, etc... all
represent the named range Budget
(=Sheet2!$A:$A,Sheet2!$C:$C,Sheet2!$E:$E, etc. �), and columns B, D, F,
H, J represent the range Actual
(=Sheet2!$B:$B,Sheet2!$D:$D,Sheet2!$F:$F, etc. �). I want a separate
cell located elsewhere to sum up all the quantities in the Actual
columns, and another cell to separately sum up all the quantities for
the Budget columns. The trick to this, though, is I need only the
quantities summed up to be for the specific row of that sum
calculation.

That might not make the most sense so I�ll break it down again. I need
to sum all the quantities only in the columns labeled Actual for each
single row. It probably needs to a dynamic range/reference that
continues expanding to the right as long as there is a number value,
since the length of the sum calculations (i.e., number of days
involved) is open-ended. 

I�ve tried combining a dynamic named range using the OFFSET command
with variations of SUMIF and SUMPRODUCT formulas and cannot get the
concept to work. I�ve also tried using a standard formula to sum up
every other column [ SUMPRODUCT(MOD(COLUMN(1:1),2)*1:1) ], which is
more what I need since the range of columns is indefinite, but the
problem there is I can�t get that formula to start calculating from the
middle of the sheet. It starts at the first column, summing them all,
and in actuality I need to start this formula around Column O or P,
because of preceding data and calculations. And the SUMIF formula
attempts I�ve made always sum up everything in the entire column and I
need to keep it contained to values just along the same row.

Any suggestions and assistance are always greatly appreciated! Thanks
in advance to all helping out!


-- 
centerNegative
------------------------------------------------------------------------
centerNegative's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24921
View this thread: http://www.excelforum.com/showthread.php?threadid=500898

0
1/12/2006 11:51:32 PM
excel 39879 articles. 2 followers. Follow

3 Replies
912 Views

Similar Articles

[PageSpeed] 49

The sum of budget for row 1 would be

=SUMPRODUCT(--(MOD(COLUMN(1:1),2)=1),1:1)

the actuals would be

=SUMPRODUCT(--(MOD(COLUMN(1:1),2)=0),1:1)

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"centerNegative"
<centerNegative.21jq5m_1137110101.1651@excelforum-nospam.com> wrote in
message news:centerNegative.21jq5m_1137110101.1651@excelforum-nospam.com...
>
> Here's another fun one that's sucked up days of my life with no
> progress.
>
> I have groups of columns representing the days of the week where
> numbers representing amount of work done (just simple whole number
> quantities) go. I have every other column named as Budget for the
> budget of work to be done, and every other column beside those named
> Actual, for actual work done.
>
> So basically on this worksheet, columns A, C, E, G, I, K, etc... all
> represent the named range Budget
> (=Sheet2!$A:$A,Sheet2!$C:$C,Sheet2!$E:$E, etc. .), and columns B, D, F,
> H, J represent the range Actual
> (=Sheet2!$B:$B,Sheet2!$D:$D,Sheet2!$F:$F, etc. .). I want a separate
> cell located elsewhere to sum up all the quantities in the Actual
> columns, and another cell to separately sum up all the quantities for
> the Budget columns. The trick to this, though, is I need only the
> quantities summed up to be for the specific row of that sum
> calculation.
>
> That might not make the most sense so I'll break it down again. I need
> to sum all the quantities only in the columns labeled Actual for each
> single row. It probably needs to a dynamic range/reference that
> continues expanding to the right as long as there is a number value,
> since the length of the sum calculations (i.e., number of days
> involved) is open-ended.
>
> I've tried combining a dynamic named range using the OFFSET command
> with variations of SUMIF and SUMPRODUCT formulas and cannot get the
> concept to work. I've also tried using a standard formula to sum up
> every other column [ SUMPRODUCT(MOD(COLUMN(1:1),2)*1:1) ], which is
> more what I need since the range of columns is indefinite, but the
> problem there is I can't get that formula to start calculating from the
> middle of the sheet. It starts at the first column, summing them all,
> and in actuality I need to start this formula around Column O or P,
> because of preceding data and calculations. And the SUMIF formula
> attempts I've made always sum up everything in the entire column and I
> need to keep it contained to values just along the same row.
>
> Any suggestions and assistance are always greatly appreciated! Thanks
> in advance to all helping out!
>
>
> -- 
> centerNegative
> ------------------------------------------------------------------------
> centerNegative's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=24921
> View this thread: http://www.excelforum.com/showthread.php?threadid=500898
>


0
bob.phillips1 (6510)
1/13/2006 9:51:17 AM
Bob, that works perfectly. Thanks very much for the help

--
centerNegativ
-----------------------------------------------------------------------
centerNegative's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2492
View this thread: http://www.excelforum.com/showthread.php?threadid=50089

0
1/13/2006 3:11:13 PM
Since you said that you want to start at Column O or P, just include those
in your formula:

=SUMPRODUCT((MOD(COLUMN(O1:AM1),2)=1)*O1:AM1)

=SUMPRODUCT((MOD(COLUMN(P1:AL1),2)=0)*P1:AL1)

And of course, you can make these formulas as 'open-ended' as you wish, by
just expanding the ranges:

=SUMPRODUCT((MOD(COLUMN(P1:ES1),2)=1)*P1:ES1)

=SUMPRODUCT((MOD(COLUMN(P1:ET1),2)=0)*P1:ET1)


-- 

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"centerNegative"
<centerNegative.21jq5m_1137110101.1651@excelforum-nospam.com> wrote in
message news:centerNegative.21jq5m_1137110101.1651@excelforum-nospam.com...

Here's another fun one that's sucked up days of my life with no
progress.

I have groups of columns representing the days of the week where
numbers representing amount of work done (just simple whole number
quantities) go. I have every other column named as Budget for the
budget of work to be done, and every other column beside those named
Actual, for actual work done.

So basically on this worksheet, columns A, C, E, G, I, K, etc... all
represent the named range Budget
(=Sheet2!$A:$A,Sheet2!$C:$C,Sheet2!$E:$E, etc. �), and columns B, D, F,
H, J represent the range Actual
(=Sheet2!$B:$B,Sheet2!$D:$D,Sheet2!$F:$F, etc. �). I want a separate
cell located elsewhere to sum up all the quantities in the Actual
columns, and another cell to separately sum up all the quantities for
the Budget columns. The trick to this, though, is I need only the
quantities summed up to be for the specific row of that sum
calculation.

That might not make the most sense so I�ll break it down again. I need
to sum all the quantities only in the columns labeled Actual for each
single row. It probably needs to a dynamic range/reference that
continues expanding to the right as long as there is a number value,
since the length of the sum calculations (i.e., number of days
involved) is open-ended.

I�ve tried combining a dynamic named range using the OFFSET command
with variations of SUMIF and SUMPRODUCT formulas and cannot get the
concept to work. I�ve also tried using a standard formula to sum up
every other column [ SUMPRODUCT(MOD(COLUMN(1:1),2)*1:1) ], which is
more what I need since the range of columns is indefinite, but the
problem there is I can�t get that formula to start calculating from the
middle of the sheet. It starts at the first column, summing them all,
and in actuality I need to start this formula around Column O or P,
because of preceding data and calculations. And the SUMIF formula
attempts I�ve made always sum up everything in the entire column and I
need to keep it contained to values just along the same row.

Any suggestions and assistance are always greatly appreciated! Thanks
in advance to all helping out!


-- 
centerNegative
------------------------------------------------------------------------
centerNegative's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=24921
View this thread: http://www.excelforum.com/showthread.php?threadid=500898


0
ragdyer1 (4060)
1/13/2006 3:31:21 PM
Reply:

Similar Artilces:

Unhide columns through Excel Viewer
I have an Excel document that I am viewing with Excel Viewer. It is a document of weekly totals, where the Author has hidden all of the prior weeks totals. Author said to just "unhide" the columns, but have been unable to locate where to do that through Viewer. Is it even possible? A Viewer is a viewer. To change an Excel file you need Excel best wishes Bernard "hummels" <hummels@tce.com> wrote in message news:97152248.0411170921.2eaea5d@posting.google.com... >I have an Excel document that I am viewing with Excel Viewer. It is a > document of weekly total...

Counting number of dates in a column earlier than today
Hi, Please can someone advise me on the formula I require for this problem. I would like to calculate the number of dates in a specified column, that equal to the current date or the date has passed. Any suggestion will be greatly appreciated Hi, Try =COUNTIF(G:G,"<="&TODAY()) Will return the number of dates in column G that are =< today "dnmnas" wrote: > Hi, > Please can someone advise me on the formula I require for this problem. > > I would like to calculate the number of dates in a specified column, that > equal to the current date or th...

How do I create a Range Name?
I want to create different ranges of cells to capture the content and duplicate on another sheet or workbook using a macro. Just select the range of cells, hit Ctrl-F3, and add a name. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Curious" <Curious@discussions.microsoft.com> wrote in message news:08E2FBF4-5142-4844-B680-41E43AFA8EFD@microsoft.com... >I want to create different ranges of cells to capture the content and > duplicate on another sheet or workbook using a macro. or, select the cells and name in t...

Multiple If True Values?
Does anyone know of a way to check for a value in two different cells, and if a certain combination of values exists, change the format of another cell? Any help would be much appreciated. Thank You. Start by experimenting with Format|Conditional Formatting Then come back if more help is needed best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Lee" <Lee@discussions.microsoft.com> wrote in message news:07DC594F-6045-4A81-B8B9-19F295A2AAA5@microsoft.com... > Does anyone know of a way to check for a value in two different cells, and &g...

Time and Sum if
I have a data logger that creates a spreadsheet. In one of the fields is the date and time in 10 min increments, the other field is the pump output. I want to be able to do two things. First is add all of the pump output from say 9am to 9pm on a given day Second, I want to be able to add all of the pump output for a given day. I need to do this calcuation hundreds of time, so I am hoping there is a sumif that will work, I just cannot seem to make it work. thanks You will most likely be using a SUMPRODUCT() formula for these tasks. Read through the complete primer found he...

Importing with Multiple delimiters
I am trying to import some very large flat files which use |~| as the delimter. Does anyone know how to import this data into excel or access? Or have VB code which will model importing and parsing this large file. Starting with 30,000 to 600,000 lines in each text file--only need 3-10 items in the whole.... ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ This is very easy to do, not in EXCEL, but ACCESS. Create a new database. When you see the new database windo...

Outlook 2003: Email address in 'From' column of Inbox.
Anyone know how to get the full email address to display in the 'from' column of the inbox in Outlook 2003? I have about half a dozen "Dave"s that all have different email address but they're indistinguishable in the inbox. Plus, I'm sick of just seeing someone's name when I really need to know the company name more than anything else. At least with a proper email address, I can easily work out if it's a legit email from a known company or spam. I don't want to have to read the post to see if it's valid. ------------------------------------------...

Append Query
I have a table "Rates" with the following data: StartDate: 5/15/08 End Date: 6/23/08 Rate: $53 StartDate: 6/24/08 End Date: 7/15/08 Rate: $86 StartDate: 7/16/08 EndDate: 9/19/08 Rate: $99 I have another table "Transactions". I want to create an append query such that if I supply the StartDate and EndDate, the query will populate the "Transactons" table with date and rate data from the "Rates" table for each and every day within the date range. Example: If I supply the StartDate of 6/23/08 and the EndDate of 6/25/08, the query would pop...

Traspose Column data into Row
Hi, I want to transpose column data in to row. I have seen many posts (using INDEX or OFFSET functions) but all these solution assume a fixed block of data to be transposed, My problem is that the data i have not only has variable blocks to be transposed but also has some duplicate headings(headings are duplicate but the data in front of each heading has different value. Below is the example of data Col A Col B Col C PRODUCT COST COMPONENT COST$ A Raw Mat 10 A D L ...

Dynamic Named Ranges
I have a chart which displays 30 days worth of data at a time, linked to another workbook. I am charting a dynamic named range in the source file however my chart data disappears every time source workbook is closed (not cool!) need a way around this without using macros.. Apprceiate any help ...

Integration Manager--log file name?
Is there any way to know the name of the log file (we're writing our logs to text file) from within one of the "After" integration events? If we're successful, we need to send the user an email telling them that the event is successful. We're currently cycling through the whole folder to find the latest one, but if there were a variable in the Integration Manager VBScripts that I could use, that would be much better. Thanks. PSC: If I recall correctly, IM has a Document Error and Integration Error event in the integration properties. Rather than looking at the...

Multiple emails sent, stays in Outbox
We are using a DSL line with Outlook 2002. We have McAfee virus protection and a router for our small office. The account is a POP account. When sending emails with pictures attached or a large number of recipients, the emails sits in the Outbox and shows that is has not been sent. It does not show as having been sent at all, but the recipient receives numerous copies of the one email. I can't figure out if it is Microsoft Outlook or a DSL problem. kathryn wrote: > *We are using a DSL line with Outlook 2002. We have > McAfee virus protection and a router for our small ...

Set up multiple email accounts on Outlook 2002, so that the mail .
I would like multiple email accounts, in separate folders, so my family, business and consumer mail do not comingle, whether sending or recieving. At present,, they are comingled, and I cannot find the right way to correct this. The easiest method is to use profiles, one for each purpose. Otherwise, you can use rules to move mail when receiving and sending. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching...

Multiple mfc activex controls in one dll
In VS6, there was a possibility to define up to 4 controls, when creating a new activex project. No such possibility in VS2005. Is there any way in 2005 to add an addtional one to an existing MFC ActiveX project? Any pointers will be appreciated! ...

How to configure replies when using multiple email addresses?
Good morning, Here is my problem. There are several people in our organization that receive email from two different email domains, for the sake of argument, lets call them user1@abc.com and user1@xyz.com. Email from both email domains are routed through our Exchange server and then the clients receive their email through their Outlook clients, (Exchange 2000 and Outlook 2002). Abc.com is the primary email domain, and we have setup a partnership with another company, hence the arrival of the xyz.com domain. When they reply to an email, regardless if it arrives to their abc.com or xyz....

Dynamic Range Charts Across Worksheets?
Hi, I'm trying to create a graph of a dynamic range of cells. In order t do this, I defined two named functions (X and Y) to graph. Everythin works great, except that I need to do this for a lot of different set of data on different worksheets. The parameters for each worksheet ar the same, but I'm trying to find a way to get around defining a ne named function for every single worksheet. Is there some way to set the named function to refer to the workshee that the graph is located in, instead of a specific worksheet? Thanks -- Message posted from http://www.ExcelForum.com A few ...

Data Entry to a Cell Range
Can I set up a data entry form, so if every time I enter a value in a cell, it updates the next empty cell in a range? Thanks Lets assume that the form is used to update cell A1. We require that everytime A1 is updated the new value will be recorded in column B. Put the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Dim A1 As Range, t As Range Set A1 = Range("A1") Set t = Target If Intersect(A1, t) Is Nothing Then Exit Sub Application.EnableEvents = False n = Cells(Rows.Count, "B").End(xlUp).Row...

Linking columns for auto fill
Can I link two columns to auto fill Column A when corresponding data is entered in Column B? In A1 enter =IF(B1="","",B1) Copy down column A as far as you wish. Gord Dibben MS Excel MVP On Thu, 25 Mar 2010 09:11:01 -0700, Daisy <Daisy@discussions.microsoft.com> wrote: >Can I link two columns to auto fill Column A when corresponding data is >entered in Column B? ...

Lists
I have a user who is importing an excel spreadsheet into a Sharepoint List, but some of the column types that SP is selecting is incorrect. I can't find any place to change the type of these columns (like from text to text with link, etc). Is this possible? Yes, you can change the column type after importing. Just go to the list settings page and choose the column you would like to update. The very first section of the column settings page will be for the data type of the column. -RH "J. Belcher" wrote: > I have a user who is importing an excel sprea...

Multiple Attachment issue
We are having problems with multiple attachments to messages in Outlook. Here is our environemnt: Windows 2003 AD Exch 2003 SP2 Outlook 2003 The problem is that occasionally when users send multiple attachments the attachments will have the wrong name or the content of one of more attachments will be "scrambled" when the message is received. This does not happen every time but only occasionally. We do not have information store corruption (which is where we looked first). We have a case open with Microsoft but we are not really making any progress. I know this issue is happening ...

Use list options to hide columns
Excel 97 Hi, I was wondering if the following is possible in excel: <big deep breath> I have 4 separate worksheets for data entry and results calculation for radiation detection, each is a separate type of test. These worksheets/tests share a lot of common Fields for data input and calculations, what I want to do is combine the worksheets and show/hide only the columns relevant to each test. In column A, I have added a drop down list of 4 different types of test. Depending on the type of test I select; call them A, B, C & D Can I use a List to control which columns are sho...

"Find and Replace" in Sheet names
Is there a way (with code perhaps) to search and replace sheet names? For example, I have 20 sheet names with "Direct (2)" in the name, and I'ld like to replace teh "Direct (2)" in the sheet name to "Net" in each instance. Thanks, Jim One way: Public Sub Direct2ToNet() Const sRepl As String = "Direct (2)" Dim ws As Worksheet Dim nPos On Error Resume Next For Each ws In ActiveWorkbook.Worksheets With ws If .Name Like "*" & sRepl & "*" Then _ ...

Found a way to edit vCard name?
Here's a mildly unusual one: I've got Outlook 2000 running on Windows XP Pro. I've created myself as a contact so that I can send my contact info automatically as a vCard. The problem is, when I do that, Outlook includes my email address as part of the VCF file's name. The SMTP service we use filters that (actually just the "at" sign included in a filename) as spam. Not acceptable. I know I have the option to manually save the info as a VCF file and then manually attach it. That lets me avoid the spam filter. But I'd love to do this manually....

How do I eliminate duplicate addresses from multiple lists?
Just moved to Exchange from GroupWise. I maintain seperate address lists in excel and access and some of the addresses are in both lists. In GroupWise I could copy an entire column of addresses from an Excel spreadsheet into the To: box and blanks would be eliminated, then I could copy a column of addresses from an access database into the To: box and both blanks and duplicate email addresses are eliminated. How can I configure Outlook to do this? Nichol1947 <Nichol1947@discussions.microsoft.com> wrote: > Just moved to Exchange from GroupWise. I maintain seperate address ...

*Keeping* multiple CToolbars docked in a single row during window resize
I have an MDI app with several toolbars docked in a single row (using DockControlBarLeftOf etc.) I am trying to prevent the rightmost toolbars from repositioning beneath the other toolbars in the same row as the main window frame is resized. Currently each toolbar is automatically repositioned on the left of the screen in order to prevent it going out of view as the window width shrinks. I would like to keep the toolbars as they are initially set; in a single row, during any window sizing operations. I still need to make the toolbars *manually* draggable, floatable and moveable by user mou...