calculating total cost?

[pharuk]

Hi, i have a pressing need and am stuck, Using a formula how can i calculate total cost of an Item? say
item is B2(1000)
cost C2 (13.53)
sales D2 (50)?

and what formula can i use to find the mean average cost as well?
thanks

 

[repairman615]

Hello,

Not really sure what your numbers are.

Is b2 the item name?
Is D2 the markup, sale price or other?

In other words, you would like to know how much an item cost you for selling it?

Jeff

 

[pharuk]

Hi Jeff,
Thanks for replying..
B2 is the item
D2 is the Sales
C2 is the cost

Am looking for a formula to calculate total cost for B2 which is the item and the mean average cost as well
thanks

 

[repairman615]

Hello,

Here is the data in a Table named "Table 1"


Table 1

<TABLE style="WIDTH: 340pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=455><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" span=5 width=91><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 68pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" height=20 width=91>Item</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 68pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=91>Cost</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 68pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=91>Sales</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 68pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=91>Total Cost</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; WIDTH: 68pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=91>Ave Cost</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=20 align=right>1000</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>13.53</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>50</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>36.47</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 align=right>43.694</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=20 align=right>2000</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>14</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>25</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=20 align=right>3000</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>22</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>70</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>48</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=20 align=right>4000</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>5</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>28</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>23</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=20 align=right>5000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>50</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>150</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>100</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">


</TD></TR></TBODY></TABLE>


Here is the Formulas:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Item</TD><TD>Cost</TD><TD>Sales</TD><TD>Total Cost</TD><TD style="BACKGROUND-COLOR: #fcd5b4">Ave Cost</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">1000</TD><TD style="TEXT-ALIGN: right">13.53</TD><TD style="TEXT-ALIGN: right">50</TD><TD style="TEXT-ALIGN: right">36.47</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #fcd5b4">43.694</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">2000</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">3000</TD><TD style="TEXT-ALIGN: right">22</TD><TD style="TEXT-ALIGN: right">70</TD><TD style="TEXT-ALIGN: right">48</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">4000</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">28</TD><TD style="TEXT-ALIGN: right">23</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">5000</TD><TD style="TEXT-ALIGN: right">50</TD><TD style="TEXT-ALIGN: right">150</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E2</TH><TD style="TEXT-ALIGN: left">=D2-C2</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F2</TH><TD style="TEXT-ALIGN: left">=AVERAGE(Table1[Total Cost])</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E3</TH><TD style="TEXT-ALIGN: left">=D3-C3</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E4</TH><TD style="TEXT-ALIGN: left">=D4-C4</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E5</TH><TD style="TEXT-ALIGN: left">=D5-C5</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E6</TH><TD style="TEXT-ALIGN: left">=D6-C6</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Does this help?

-Jeff

 

[pharuk]

Thanks Jeff, that was what I was looking for
on the other hand, do you reckon if there is a formula i could use to calculate the numbers of item that cost above 1000
thanks again

 

[repairman615]

(2007)

Yes,

=Countif()

If you would like on the Total Cost:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Item</td><td style=";">Cost</td><td style=";">Sales</td><td style=";">Total Cost</td><td style="background-color: #FCD5B4;;">Ave Cost</td><td style="background-color: #F2DDDC;;">>1000</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">13.53</td><td style="text-align: right;;">50</td><td style="text-align: right;;">36.47</td><td style="text-align: right;background-color: #FCD5B4;;">246.094</td><td style="text-align: right;background-color: #F2DDDC;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2000</td><td style="text-align: right;;">14</td><td style="text-align: right;;">25</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">500</td><td style="text-align: right;;">1560</td><td style="text-align: right;;">1060</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4000</td><td style="text-align: right;;">5</td><td style="text-align: right;;">28</td><td style="text-align: right;;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5000</td><td style="text-align: right;;">50</td><td style="text-align: right;;">150</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=D2-C2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=AVERAGE(<font color="Blue">Table1[Total Cost]</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=COUNTIF(<font color="Blue">Table1[Total Cost],">1000"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=D3-C3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">=D4-C4</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">=D5-C5</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E6</th><td style="text-align:left">=D6-C6</td></tr></tbody></table></td></tr></table><br />

Change Total Cost to Sales if needed for your results.

-Jeff

 

[pharuk]

Thanks Jeff..
Much appreciated

 

[repairman615]

Your Welcome,

Welcome to the board!

 

[pharuk]

Just another quick question if you dont mind..

1)Can i use the same formula to calculate Total cost for some categories (if category x has 5 different values and category y has 3 different values) is there a formula to calculate total cost by categories?


2) Can I get a formula to show the position of each Item based on it's cost from the most expensive to the least expensive?

 

[repairman615]

Yes,

Hopefully this data is in a table as this feature is very helpful for what you are needing.

If using a table this can be done by =SUM(Table1[Catagory x])

similar for catagory y


Have a look at the function SUMIF() should the previous not work for you.


As for the second question, the auto filter will sort as you would like.

In your table you see drop down arrows. click arrows then select sort largest to smallest.

Hope that helps.

-Jeff