Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Using * with SUM and %

0

Hi

I have 7 columns for days of the week (F through to L).  there are three other columns:

B = %

C = %

D = $

There are five rows of importance here 4 - 8 with different $ amounts

I want to put a number in say, F4 (F = Monday; 4 = $155) and then multiply that by D and then find the % of that from C and then the % of that from B.

And then...

I want to do the same for other amounts from F G H I J K L...so that there are likely to be different amounts in, for example,  F4, F5  G4, G7, H8 and so on.

I then want to SUM each column F through to L in row 10 (estimated income).

Sorry if this is confusing...I'm new to excel!  I've attached a workbook to follow as to the above...the formula I worked out seems to work for two days, H and I but not for the other days #Value! error... and I'm sure there must be a more eloquent formula anyway...

Answer
Discuss

Answers

0
Selected Answer

I'm afraid I couldn't figure out why you have the #VALUE error in some cells and not in others. Finally, I copied H4:H8 to F4:G8. That cured the problem, even after I deleted the values in F4:G8.

Please try this formula in F10.

=SUMPRODUCT(F$4:F$8,$D$4:$D$8,$C$4:$C$8,$B$4:$B$8)

The SUMPRODUCT function multiplies every number in a range with every number in another range. Observe that F$4:F$8 has no dollar sign before the Fs. This enables copying of the formula from F10 to G10:L10. Note that Excel increments rows and columns in formulas as you copy them to other cells. B4:B8 should remain B4:B8 regardless of where the formula is copied. That is achieved by placing dollar signs before both column and row references, i.e $B$4:$B$8.

Discuss

Discussion

thanks so much for this...much appreciated.  I was doing some reading about sumproduct and came up with the following:
=SUMPRODUCT(F4:F8,D4:D8,C4:C8,B4:B8)
but then had to change this for every column.  Your formula solved this! 
thanks again 
stevejn (rep: 2) Sep 20, '18 at 8:56 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login