Having trouble creating an Excel function

Factual Questions
1

I’ve been googling around trying to get the answer I seek, but I’m not sure how to ask the question so that I can find the correct answer!

I have a row of hours it takes to get a project done. Each column represents a different work function for that project, and each work function has a different cost per hour. I need for the cell values stay as hours, but I need to add up all the corresponding costs in the row and show that value at the end.

So it would look something like this:

(3 hours [at $80/per]) + (5 hours [at $120/per]) + (2.5 hours [at $75/per]) = $Total Cost

What function will get me that?

2

I think you need a separate entry for each data point.

So:

Job1 - Hours - Cost/Hr - Total - Job 2 - Hours - Cost/Hr - Total - Grand Total

You could skip totaling each subjob and just write a formula for the Grand Total but I think it is more clear this way.

Alternatively if you want the main page cleaner you could put the data on a different sheet and reference that in your calculation.

3 

              
Drafting	  Build          Proofing		
$80.00	          $120.00	 $75.00		
				
	         Drafting	  Build  Proofing	Total cost
Project A	     3	            5	     2.5	 $1,027.50
Project B	     2	            9	      3	         $1,465.00

The costs are in A2, B2, and C2 respectively while the hours for Project A are in B5,C5, and D5. Total cost for that row is the formula “=(B5*$A$2)+(C5*$C$2)+(D5*$C$2)”

Now, just drag the total cost formula down as many projects as you have and it will grab the hours from the new row and multiply them by the cost and add the results. The dollar signs in front of the second cell reference will keep excel from updating them so dragging won’t accidentally result in referencing an empty cell.

4

I think just a formula in the last (total cost) column. You could enter the rates for each column (task) at the bottom of the sheet, perhaps out of view, and just reference the rates row with an absolute reference ($) in the formula.

5

Try the sumproduct() function Microsoft Support

More generally, you can get a lot of mileage out of Excel’s Control-Shift-Enter functions (officially called “Array functions”). Here’s a good explanation: Use a CSE Formula (Array Formula) to Perform Super-Calculations on Data - Excel Tips - MrExcel Publishing

6

Ah! I added a row of cost per function, did the formula as you described and success! Thanks! How do I copy this formula to each subsequent row and have it apply to the values within? I thought I could click/drag to pull the formula down through the sum column, but unfortunately something’s not adding up right- I’m getting a different total for each row, but it’s not a correct total. ?

(I need to do some serious learning about Excel- I know very basic stuff- I’ll read through the other suggestions here and see if there are other ways to approach this formula, but any additional Excel learning suggestions would be most welcome!)

7

Without seeing it, the first guess is that you didn’t make the reference to the cost per function absolute (using the dollar sign). Under the formula tab you can click on “trace precedents” and it will draw an arrow to where the formula is pointing.

8

An alternative is to create a corresponding set of columns - with formulas referencing the original cells “=(hours)x$(cost-per-hour)”
To make things pretty, hide these columns, even stick them off to the right.
This prevents excessive growth of the formulas if you have too many columns.



Drafting	  Build          Proofing		
$80.00	          $120.00	 $75.00		
				
	         Drafting	  Build  Proofing	Total cost
Project A	     3	            5	     2.5	 $1,027.50                =B5*$A$2    =c5*$B$2     =D5*$C$2
Project B	     2	            9	      3	         $1,465.00        =B6**$A$2   =c6*$B$2     =D6*$C$2

Your total cost is the sum of those hidden columns. (right-click on column top, “Hide”)
By breaking it up like this it’s easier to build, fix, and find problems.

9

Below is a link to a screenshot. Some of my cells don’t have values (because a work function is not required for that specific project). I click/dragged the formula down column R, but obviously something’s wrong.

Link to pic.

Here is my formula: =(D2D3)+(E2E3)+(F2F3)+(G2G3)+(H2H3)+(I2I3)+(J2J3)+(K2K3)+(L2L3)+(M2M3)+(N2*N3)+O3

Note: The formula does not include column P, purposefully.

Not sure what’s going on!

10

Think I just figured out what’s going on- when I cleared the formula from the cells with the incorrect totals, it showed me a “picture” of which cells the formula was pulling from, and the formula was pulling from the row and the row immediately above it, not the row and the cost row at top! So… how do I solve?

11

If you want the cell entries in hours and the total at the bottom in dollars can’t you just use =(sum a1:a20)*hourly rate
Do this for each column and then at the far right total the row.
Or am I misunderstanding what you are trying to do?

12

As someone else mentioned, the sumproduct function is probably easier. And as someone else mentioned, you need those $s in the formula to lock the reference to the first row.
=O3+sumproduct(D$2:N$2, D3:N3)

When you fill the formula down, the $ before the 2 will keep that part of the formula locked on row 2.

13

Ah-ha! The $ locked in those cells and I was able to pull down the formula. I think really what I have learned here is that I require some intensive Excel training, STAT! Reading through some of these replies was like trying to read another language. It was hard to make sense of some of it with my limited understanding of what some of this lingo means!

So what do I need? Excel for dummies?