which sum or function to use?

[Belinda65]

Hello,

I am trying to put together an order form for a customer. I am selling him artwork for design. There are two variables in pricing. One is where he uses my factory to make, therefore the price is cheaper and the other where he gets the artwork outright and can go off and do whatever he wishes with the artwork.

Its probably very easy, but in terms of excel I'm a little incompetent (although I did manage a floor sum the other day!!)

So, please can you take a look at the attached (it is an example version of what I am trying to do). There may be an easier way to set it out as well and I welcome any feedback.

The designs are in column A. Columns B & D represent the two variations of price that I am offering. The customer will either choose one or the other and not both. I want to be able to subtotal and grand total in Column F. I have never used an IF function before and I don't fully understand it. However if (pardon the pun) this is the function to use, then I'd really appreciate an example and possibly a link where I can go off and try to do it myself.

Thank you and kind regards
Belinda

[Speshul]

Would this work?

=(B3*C3)+(D3*E3)

Or, to make sure there isn't a value in both Factory AND order,

=IF(AND(C3<>0,E3<>0),"Select only one",(B3*C3)+(D3*E3))

Grand Total formula would be
=SUM(F3:F17)



The IF function works as follows: (also this is the color code I'm using for the rest of this post)
=IF(logical_test,value_if_true,value_if_false)

Logical Test = Any test that returns a TRUE or FALSE value.

So =if(100>50,"true","false")
Since 100 is greater than 50, excel views that formula as
=if(TRUE,"true","false")
Since it is a TRUE, the Value_if_true option is selected, so the cell will display the text "true" (without quotes!)

[ChemistB]

You could simply use something like

=B3*C3 + D3*E3
Would that work for you?

[Belinda65]

Thank you Jacc,

Not all of us are marvellous mathematicians, some of us are fabulous designers that aesthetically enhance your world. Thank you for you help. I appreciate it.

[Speshul]

Actually I am Speshul, Jacc is quoted in my signature and it shows on every post I make :p


No problem, and welcome to the forum!

[Belinda65]

Actually I have gone back to my sheet and realised that it's not what I wanted. In the subtotal, I just want the values to show up on the cells in the example F8, F15 and the grand total F17. So for The Arctic Story and Floral garden which are two design groups, I just want the total to be for the whole design group not each individual item as that is not how the customer will be ordering and it looks very messy to have all of the values there.

Please see this next example which is attached. Is there any formula that doesn't show the values F21-F25, but just the sum in F26 is calculated (shown in Red)?

If not, I can have them all, but it looks a little ugly

Kind regards
Belinda

[Belinda65]

sorry I forgot to attach, pls see attached now

[Speshul]

The easiest solution would be to just make the font white

In F26
=SUM(SUMPRODUCT(B21:B25,C21:C25),SUMPRODUCT(D21:D25,E21:E25))

In F33
=SUM(SUMPRODUCT(B28:B32,C28:C32),SUMPRODUCT(D28:D32,E28:E32))

In F35
=SUM(F26+F33)

OR
You can skip F26 and F33 and just put this in F35
=SUM(SUMPRODUCT(B21:B25,C21:C25),SUMPRODUCT(D21:D25,E21:E25),SUMPRODUCT(B28:B32,C28:C32),SUMPRODUCT(D28:D32,E28:E32))

[Belinda65]

Thank you Speshul, that was exactly what I was looking for and it worked perfectly

Kind regards
Belinda