Jump to content


Summing and multiplying individuals cells in an array

formulas hide column structured table references

  • Please log in to reply
2 replies to this topic

#1 phoenixrizing


    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 01 July 2016 - 03:40 PM

I have two columns: one for cost, the other hours.


I want to first multiply the two numbers in a row and do the same thing for each row. Then I want to add all of that together.


In the uploaded file, you'll see in cell I31 that I've referenced each cell to multiply them and then added them to the next pair of cells down the columns.


I'm thinking there's got to be an easier way to do this instead of hand picking them the way I have it now.


Attached File  column example.xlsx   10.7KB   195 downloads

#2 Jonathan


    Forums Administrator

  • Administrators
  • 887 posts

Posted 01 July 2016 - 10:16 PM

Hi phoenixrizing,


The simplest solution I can think of is to add a new column that multiplies your cost and hours and then simply sum that column.

Simple multiplication and sum formulas are explained in the video tutorial: Essential Skills Lesson 2-12 Create your own formulas.


If you don't want this column to be visible to the user, you can easily hide it using the skills taught in the video lesson: Expert Skills Lesson 7-7 Hide and unhide worksheets, columns and rows.


I have attached a copy of your workbook with the new column added:


Attached File  column example.xlsx   11.03KB   164 downloads

column example.png


If you expect to be adding more data to this workbook in the future, you could make this even more elegant by using a Table along with Structured Table Reference formulas. This would allow your totals to remain accurate even if rows were added to or removed from the table.


For more on structured table references, see the video lesson: Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

#3 phoenixrizing


    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 05 July 2016 - 07:03 PM

Thank you, Jonathan. I like the solution.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users