# Formula for worksheet that's tallying and pricing

A

I hope someone can help me, and I hope I can explain this fully.

I have a worksheet to tally sizes and prices. Larger sizes have a higher
price. Currently, it looks like this:

B3:E3 are all the same price, which is tallied in H3 (or 4, 5 ,6 and so on)
F3:G3 are the same, but different than the others.

I would like H3 to end with the quantity from B3:G3, at the correct price.

Example--
B3 is a small, priced at 17.50. Whereas F3 is a 2XL priced at 19.50.
If B3 has a quantity of 2, I would like H3 to calculate \$35.00. Or if the
quantity is in another row/column, then it will calculate the proper price,
based on the size. If there is a quantity of 2 in F3, then H3 would return
39.00.

I have this in 2 different sections in the worksheet. I also have a total
row, and total column at the bottom of the worksheet. Ideally, I want to be
able to total the amount of small, med, lrg, etc, as well as total price, etc.

working on this formula for days. I have the rest of the worksheet done, but
this.

Angie

R

#### RidgeView

I hope I am understanding you properly.
In this example, the prices are above the quantities (they could be anywhere).
B C D E F G H
Row 2 17.50 17.50 17.50 17.50 19.50 19.50
Row 3 2 35.00
In H3 the following formula:
=IF(B3>0,B3*B2,IF(C3>0,C3*C2,IF(D3>0,D3*D2,IF(E3>0,E3*E2,IF(F3>0,F3*F2,IF(G3>0,G3*G2,""))))))

Hope this is what you need.......
Regards...

A

Here's how it is setup....

Column B C D E F G (The same for J K L M N)
S M L XL 2XL 3XL

I would then place a quantity in whichever size(s) needs ordered. However,
the price for F&G and M&N differ from the rest. So I would like the formula
to recognize the quantity in whichever column, grab the correct price and
calculate in column H, as well as column O. (probably 2 different formulas,
for 2 different cells, but same concept)

Is this even possible? Or am I asking for too much?

TYVM

R

#### Ragdyer

You're confusing!

Columns B to G = 6 sizes
Columns J to N = 5 sizes ???

AND ... *where* are your prices stored?
Are there different prices for each row depending on a style in another
column?

A

I'm sorry I'm not explaining it better.

B2:G2 are the sizes. B2, C2, D2, E2 are all priced at 17.50. F2 & G2 are 19.50
J2:O2 are also sizes. All priced at \$15.00

For B2:G2, I want to pull the qty in each column, and assign the correct
price in the total column. (H2) The same for J2:O2, but there's only 1 price
involved.
Column I is the vendor cost, so whatever quantity is in B2:G2 *12.5
(S,M,L,XL) or *15 (2XL, 3XL)

Column P would calculate anything in J2:O2 *15. If there is something in any
cell. Column Q is the vendor cost, so whatever is in J2:O2 *12.

Then I have totals at the bottom of the worksheet, to tally how many S, M,
L, XL, etc. And how much Column Q owes.

Is that any better? Again, I am really sorry I'm not explaining it better. I
have it all setup for basic formulas, but when I put in several variables, I
can't figure out how to setup that formula.

TYVM for your help and patience.

R

#### Ragdyer

Say you place the unit prices in Row1, along side the sizes:
H1 = 17.50
I1 = 19.50
P1 = 15.00

That way you don't have to change the formulas when you change the prices.

Then, in H2:
=SUM(B2:E2)*H\$1+SUM(F2:G2)*I\$1

And in P2:
=SUM(J2:O2)*P\$1

Copy both down as needed.