Formula for worksheet that's tallying and pricing
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.
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"adburnheimer" wrote in message
...
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.
"Ragdyer" wrote:
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?
--
Regards,
RD
--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!
--------------------------------------------------------------------------
-
"adburnheimer" wrote in message
...
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
"RidgeView" wrote:
I hope I am understanding you properly.
In this example, the prices are above the quantities (they could be
anywhere).
Using your cell examples:
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(B30,B3*B2,IF(C30,C3*C2,IF(D30,D3*D2,IF(E30 ,E3*E2,IF(F30,F3*F2,IF(G3
0,G3*G2,""))))))
Hope this is what you need.......
Regards...
"adburnheimer" wrote:
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.
Thank you very much, in advance for any help you can give. I have
been
working on this formula for days. I have the rest of the worksheet
done, but
this.
Angie
|