Order formula with variable random fields

[DockerRiver]

Hi I want to try and "total" the purchase price of an order form based on the following example. Each week there will be a different number in the "Your Order" column but the price per item is a constant.
Also I may NOT order every product - every week so only a few of the boxes in "Your Order" may have a value in there.

Any help much appreciated

order6.png

TOTAL PURCHAE PRICE $17.95

[Palmetto]

Welcome to the Forum.

Is there some reason why a simple SUM(G2:G14) won't do?
Excel only sums cells that have numbers, so if a cell in the Your Order columns is empty to counts as zero.

[Ron Coderre]

I *think* you need to use the SUMPRODUCT function.
This regular formula returns the sum of order qty x Unit Price:
=SUMPRODUCT(price_range,your_order_range)

Is that something you can work with?

[DockerRiver]

@Palmetto,

Thank you for taking the time to reply, The sum function does not do the job for me as it only "sums" the items in the column I need it to pull the cost price from the adjacent column (multiply) and ONLY when there is a value in the "Your Order" column.

Again thank you for taking the time to help.

Warm Regards

Mal

[DockerRiver]

Hi Ron,

Thank you for your reply, I was not aware of the "=Sumproduct" function and will certainly try that and let you know if it worked.

Thank you for taking your time to reply

Regards

Mal

[DockerRiver]

Hi again Ron,

I tried the "=sumproduct" function with this =SUMPRODUCT(F14*G14:G15:G16:G17 It works on G14 and G15 but after that if I keep typing G:16 etc.. I throws an error. It is also tedious typing each line eg.. G17, G18, G19 and so on... The resulting total is 200 lines down the page - do I need to type each of the 200 lines in the formula?

Regards

[Ron Coderre]

You may need to attach a workbook...Pictures are rarely adequate for us to get enough information from.

Here's an example of how SUMPRODUCT can work:
With
A1:A5 containing prices
10
20
30
40
50

and
B1:B5 containing quantities
0
1
0
10
0

This formula calculates the extended total
=SUMPRODUCT(A1:A5,B1:B5)

In the above example, that formula returns: 420
which is the sum of...

Please Login or Register  to view this content.

I hope that helps.

[DockerRiver]

You may need to attach a workbook...Pictures are rarely adequate for us to get enough information from.

Here's an example of how SUMPRODUCT can work:
With
A1:A5 containing prices
10
20
30
40
50

and
B1:B5 containing quantities
0
1
0
10
0

This formula calculates the extended total
=SUMPRODUCT(A1:A5,B1:B5)

In the above example, that formula returns: 420
which is the sum of...

Please Login or Register  to view this content.

I hope that helps.

Hi Ron that worked perfectly thank you so much for your help.

Not sure how to set the thread as resolved and award you the accolades you have earned - yet! but I will read the rules and do same.

Thank you once again

Regards

Mal

[DockerRiver]

Thank You to both contributors

**RESOLVED**