#### Multiply values in one column by values in another column

```Hi,

I'm sure this must have been posted before but I can't seem to get my
search terms accurate enough to find it. I have two columns: "Number
of containers" and "Volume of container". I want to multiply these to
give me the total volume of all containers: (A1*B1)+(A2*B2)+(A3*B3)...
I could create this long formula manually, but I'm sure there must be
a better way. Naturally, I could do this by creating a new column,
multiplying each row and then adding the values in the third column
but, again, I'm sure there must be a more elegant solution.

Any suggestions?

Many thanks,

Paul
```
 0
paulmjkaye (66)
7/7/2008 9:19:32 AM
excel 39879 articles. 2 followers.

2 Replies
529 Views

Similar Articles

[PageSpeed] 22

```Try this...

=SUMPRODUCT(A1:A3*B1:B3)

where you would change the A3 and B3 to the last row you expect to have data
on.

Rick

"Paul Kaye" <paulmjkaye@gmail.com> wrote in message
> Hi,
>
> I'm sure this must have been posted before but I can't seem to get my
> search terms accurate enough to find it. I have two columns: "Number
> of containers" and "Volume of container". I want to multiply these to
> give me the total volume of all containers: (A1*B1)+(A2*B2)+(A3*B3)...
> I could create this long formula manually, but I'm sure there must be
> a better way. Naturally, I could do this by creating a new column,
> multiplying each row and then adding the values in the third column
> but, again, I'm sure there must be a more elegant solution.
>
> Any suggestions?
>
> Many thanks,
>
> Paul

```
 0
7/7/2008 9:27:12 AM
```On Jul 7, 11:27=A0am, "Rick Rothstein \(MVP - VB\)"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> Try this...
>
> =3DSUMPRODUCT(A1:A3*B1:B3)
>
> where you would change the A3 and B3 to the last row you expect to have d=
ata
> on.
>
> Rick
>
> "Paul Kaye" <paulmjk...@gmail.com> wrote in message
>
>
> > Hi,
>
> > I'm sure this must have been posted before but I can't seem to get my
> > search terms accurate enough to find it. I have two columns: "Number
> > of containers" and "Volume of container". I want to multiply these to
> > give me the total volume of all containers: (A1*B1)+(A2*B2)+(A3*B3)...
> > I could create this long formula manually, but I'm sure there must be
> > a better way. Naturally, I could do this by creating a new column,
> > multiplying each row and then adding the values in the third column
> > but, again, I'm sure there must be a more elegant solution.
>
> > Any suggestions?
>
> > Many thanks,
>
> > Paul

Perfect - thanks!
```
 0
paulmjkaye (66)
7/7/2008 9:48:08 AM

Similar Artilces:

how to run onhand value report
I get the message enter parameter when entering the zoom feature On Sat, 6 Mar 2010 17:36:01 -0800, junebugg <junebugg@discussions.microsoft.com> wrote: >I get the message enter parameter when entering the zoom feature You'll have to give us some more context than that, junebugg. What's the "onhand value report"? What's the "zoom feature"? You can see your database; we cannot! -- John W. Vinson [MVP] ...

Compare the "varchar" values
I need to compare the student's score with the requirement score. The problem is the score is either a varchar value, i.e., "2" OR is combined by two varchar values, such as "2" and "+" or "-" like "2+". If a student's score is lower then the req score then the student is not qualified. How to compare them in VBA? Thank you in advance for your help. I would force the values to increase or decrease based on the presence of the + and - signs and then compare the numeric values. Using Replace and Eval you could use an exp...

Multiplying...
Help! I have some data that I am importing into Excel. I have a range of cells that is 11 rows x 2 columns. What I need to do is simple. I need to multiply each cell in this range by 60 without going in to each one and typing =PRODUCT(60*........). Anybody got any suggestions? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Put 60 in an empty cell, copy it, select the 11 x 2 range and do edit>paste special and select multiply -- Regards, Peo Sjoblom &quo...

Ignor zero values
This is a form of a common query, but I haven't been able to nail down an answer... I have a simple graph, the data series are formulae calculating data from another source. The catch is, I don't want the graph to plot zero values! Tools>Options>Graphs>Plot Empty Cells does not work because there are formulae in the cells. Suggestions to utilise error symbols don't work because I subsequently reference the data series for further calculations. Any thoughts anyone? (Running Excel 2002 SP3) You can use array formulas (array entered with Ctrl-Shift-Enter) to stri...

Pasting a Formula into a new column or worksheet, but so that it doesn't change
Hello - When I copy and paste a formula into a new column, Excel assumes that I wanted to use references to different columns. That is, each time that I want to copy and paste a formula into a new column, or sheet, I have to either first make it an absolute reference---or I have to paste it into Word, and then paste it into Excel to keep it as the same formula. Does anyone know if there is an easier way to paste a formula so that the reference doesn't change, and the formula thus stays the same? Thanks!! One way is to copy it *from the formula bar*! Click in the cell containing the fo...

Cann't update the value in table
I use the following SQL to update the QtyOnOrder in table SpareParts, but it cann't. the QtyOnOrder is single type. n=8 tbCSN='8210401002' MySQL = "UPDATE SpareParts SET QtyOnOrder=" & n & " WHERE CSN='" & tbCSN & "';" MyDB.Execute (MySQL) Don't have any error message, How to solve this problem? Thanks Just to make sure: Is [CSN] of Text type or Numeric data type? The SQL syntax looks correct for Text data type ... -- HTH Van T. Dinh MVP (Access) "Dou" <szdouch@163.com> w...

Combining multiple cell values with "hard" carriage return separating each
Hello, I have several values in separate cells that I would like to combine into a single cell with a carriage return (Alt-Enter) separating each value. I basically want to take a column of values and turn it into a text file with the values each on their own line. I learned that Chr(10) is a line feed. (A1 =) 'line one text (A2 =) 'line two text What I want is for A3 to contain: (A3 =) line one text line two ext That is, I want the values of A1 and A2 combined as if I typed them in and separated them with Alt-Enter. I tried a direct formula in the destination cell: ...

Journaling for one SMTP domain
Dear All, We have 3 SMTP domain within our exchange environment. - E2K7 I have enabled the journaling using journal rules but is there any way where we can enable journaling only for the one SMTP domain and not all 3. Regards, KT Journaling is set at the storage group so I do not think that you can by SMTP domain, however if you find a way would you kindly pass that information on? Thanks On Nov 17, 10:30=A0am, "KedarT" <kt_082...@yahoo.co.in> wrote: > Dear All, > > We have 3 SMTP domain within our exchange environment. - E2K7 > > ...

how do i get 4 postcards on one page?
I have created a postcard in publisher 2007. I cannot get it to print 4 per page. I have publisher 2003 at home and can get it to work fine. Help! Use one of the built-in Avery page setups, they work. Select postcard, Scroll down to Avery US letter, expand, 3263 landscape is a good start. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "LiteracyLady" <LiteracyLady@discussions.microsoft.com> wrote in message news:6A296F77-95DB-42ED-A471-1FE14BA8EC58@microsoft.com... >I have created a postcard in publisher 2007....

does a window or dialog have a return value when closed
I have a dialog that does a search and needs to return the search results to the calling function. Especially if the seach is cancelled or produces no result. When a result is produced, I can handle that by updating a control on the calling form, but unless I have hidden controls on the calling form, I can't return a cancelled or no find result. A return value would be handy if it exists, or can be created Thanks -- Alan Heiser On Mon, 19 Apr 2010 22:40:01 -0700, Alan <Alan@discussions.microsoft.com> wrote: >I have a dialog that does a search and needs t...

Not plotting zero values
I have created a chart with the date as the x axis, running to the end of the year. Each week, as new statistics arrive, I put them into the appropriate cells. The graph plots a subsequent cell that applies a formula to the inserted data. So that others can use this with minimum fuss, I have copied the formula through to the end of the year, and applied an "if" statement so that if the data entry cells are blank, then the formula returns a blank. Nevertheless, the chart applies a zero value to the blank results, meaning the line graph drops to the x axis until the end of th...

Multiplying two columns
How do I multiply column A (length) by column B (width)? All I want is a new column C (area) where the cells correspond to what I just multiplied if one were to look from left to right. A x B = C. I can do it for two numbers but not whole columns.... In (eg) C1 =A1*B1 Select C1 Point to bottom right corner until you see the plus Double click To get the sum of all A*B you can use: =SUMPRODUCT(A1:A50,B1:B50) -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. ...

Autobudget values way out
Money 2003 I've been trying to use Autobudget to help create a budget, but the values it's returned are way out and result in a budget that show I'm spending much more than I have been. A closer look at the transactions it's based its figures on shows that it appears to take occasional expenses that have occurred within a given month and create a monthly figure of a similar amount without taking into account the months where there is no spending. This seems unbelievably crude way to calculate budget figures and I'm sure there must be something I'm not taking into acc...

Copy from one DB to another
Hi, I created the following attached to a button to copy the record my form is currently displaying to another DB that is exact duplicate, structure only. I get an error saying that "mdb\NewReport" table can't be found. Private Sub CopyToImm_Click() OldSQL = "INSERT INTO " & _ "U:\CIS\DAR\MyName\AccessDB\Jims_Immediate_DB.mdb\NewReport " & _ "SELECT * FROM NewReport " & _ "WHERE NewReport.ReportId = [ReportId];" DoCmd.RunSQL OldSQL End Sub -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.as...

pivot table question
My data table has a 'costs' and 'date' column. I'm interested in getting data from the last 7 days and having it in the field list so that I can use it as part of a formula. Does anyone know how I can do this? Excel 2007 PivotTable Filter last 7 days. http://c0444202.cdn.cloudfiles.rackspacecloud.com/12_03_09a.xlsx Great. Thanks so much! ...

File Modified by Another User #2
I have recently upgraded my PC to Windows XP SP2 and am having a curious problem. When I attempt to save the file, I get a dialog box stating the file might have been modified by another user - do I wish to save a copy or overwrite changes. The workbook is not shared - however, it does reside on a Linux Samba share. But then again, it has always resided on the share. Only since the SP2 update, has this started happening: These are the steps I followed: 1. Opened Excel 2. Opened the file on the Samba share 3. Clicked SAVE 4. Got the dialog box asking to overwrite changes or s...

How to multiply time?
I'm trying to do a quick and dirty time sheet where all I enter is hours worked and hourly rate. If the hours were whole numbers this would be simple but if someone works 40 hours 28 minutes That would be 40.4666666... I guess one way would be to have the whole numbers in one column and the partials in another and then add them both once I convert the partials from 60th's to 100ths Try this... A1 = total time in [h]:mm format as a true Excel time value B1 = hourly rate =A1*24*B1 You will probably want to round the result to 2 decimal places: =ROUND(A...

UDP Broadcast message when system has more than one network card
I have an application that broadcast a UDP pack to all devices for reply. When a system has more than one network card it seems to only broadcast it only on the first card that was installed in the machine. How do I get it to broadcast on all network devices on my system? I am using, Create(0, SOCK_DGRAM); BOOL bMultipleApps = TRUE; SetSockOpt(SO_REUSEADDR, (void*)&bMultipleApps, sizeof(BOOL), SOL_SOCKET); SetSockOpt(SO_BROADCAST, (void*)&bMultipleApps, sizeof(BOOL), SOL_SOCKET); SendTo(pBuffer, nSize, nPort, NULL, MSG_DONTROUTE); "amccombs" <amccombs@discussions....

How can I initialize the value?
template < class elemType > class MyArray { public: explicit MyArray( int size = DefaultArraySize ); MyArray( elemType *array, int array_size ); MyArray( const MyArray &rhs ); virtual ~MyArray() { delete [] ia; } bool operator==( const MyArray& ) const; bool operator!=( const MyArray& ) const; MyArray& operator=( const MyArray& ); int size() const { return _size; } virtual elemType& operator[](int index) { return ia[index]; } virtual void sort(); virtual elemType min() cons...

Sum values over range of dates
Need Help!!!! Here is the example... If I have a huge table of dates and values for those dates and need to sum based on a given range, how do I sum it up? date 1 date 2 date 3 date 4 date 5 Sate 1 12 7 8 1 1 State 2 10 4 6 2 8 State 3 5 4 2 3 7 start date end date Sum would be??? Sate 1 2 5 17 State 2 1 3 20 State 3 2 4 9 But what is the formula I can use for this??? Please help! You first use a union query to normalize your table/spreadsheet. SELECT State, 1 as TheDate, [Date 1] as TheValue FROM tblOfDates UNION ALL SELECT State, 2, [Date 2] FROM tblOfDates UNION ALL SELECT Sta...

Can I do more than one Anytime Upgrade?
Is it OK to do more than one Anytime Upgrade (like Starter to Home Premium and Home Premium to Ultimate)? ...

Active Column Ref in a Formula
The formula in a cell sums accross a row from a fixed column to the column containing the formula How do I have a variable which represents the active column? Thank you ?? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Glynn Taylor" <GlynnTaylor@discussions.microsoft.com> wrote in message news:23D7C71A-5C93-47A1-9991-962742515C41@microsoft.com... > The formula in a cell sums accross a row from a fixed column to the column > containing the formula > > How do I have a variable which represents the active column? > > Thank ...

Why does it multiply my 'sum' by 17?
Ok. I am working with a select queury and all I am trying to do is sum two numbers in a column together that share the same 'queue'. For some reason it's adding the two numbers and then multiplying by 17 for every 'group'. Any ideas? Thanks for your help. Post your SQL. -- KARL DEWEY Build a little - Test a little "Love Buzz" wrote: > Ok. I am working with a select queury and all I am trying to do is sum two > numbers in a column together that share the same 'queue'. > > For some reason it's adding the two numbers and then mul...

Hiding #VALUE!
I have a column with formulas, and when there is no data to calculate I get a #VALUE! I would like to hide #VALUE! Can anyone point me in the right direction? Thanks Digital2k You might try writing your formulas like =IF(ISERROR(your_formula),"",your_formula) The disadvantage of such an approach it that your_formula will often be calculated twice, resulting in a performance hit. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Digital2k" <digital2k@adelphia.net> wrote in message news:W5OdnenfQ6bvCCfZnZ2...

Retrieve Picklist Values from caseOrigin
Hi all, Im attempting to retrieve lookup values for "caseorigincode" contained in the incident entity. If someone has a sample of how to do this in Vb.net, it would greatly be appreciated! Thanks, Larry B. ...