extract numbers then sumproduct values against another column of data

Rachaelsqa

Rachaelsqa

New Member
Joined
Oct 22, 2009
Messages
19
The Table :
  • Column R represents a list of services
  • Column L is the full price
  • Column N is the discounted price ( my example reflects no discount in this case)
  • Column C is an associated code , in some cases the code is n/a
  • Starting at Row 13 ; Column U through Z ( depending on how many time points may require more or less columns than example shown but the Column U is static) represents different points in time in which the service can take place.
    • Each point is time is coded as such :
      • R0 – means it’s the discount price and represents a quantity of 1
      • R0x? – means it’s the discount price and the quantity if reflected after the x value - my example shows r0x2 meaning discount price and a quantity of 2
      • Q1 or Q0 – both represent the full price and a quantity of 1
      • Q1x? or Q0x? - means it’s the FULL price and the quantity if reflected after the x value - my example shows q0x2 meaning discount price and a quantity of 2
      • Each point in time can have any combination of these designations within the column.
      • <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:stockticker w:st="on">ROW</st1:stockticker> 11 & 12 should be the sumproduct of each column timepoint reflecting the associted costs*quanity for the Full Cost and for the Discount Cost
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
schema

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 73px"><COL style="WIDTH: 89px"><COL style="WIDTH: 80px"><COL style="WIDTH: 287px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 63px"><COL style="WIDTH: 59px"><COL style="WIDTH: 62px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD><TD>L</TD><TD>N</TD><TD>R</TD><TD>U</TD><TD>V</TD><TD>W</TD><TD>X</TD><TD>Y</TD></TR><TR style="HEIGHT: 124px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="FONT-SIZE: 11pt; FONT-WEIGHT: bold"> </TD><TD style="FONT-SIZE: 11pt; FONT-WEIGHT: bold"> </TD><TD style="FONT-SIZE: 11pt; FONT-WEIGHT: bold"> </TD><TD style="COLOR: #333333; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Total SOC Costs Per Time point</TD><TD style="TEXT-ALIGN: center; COLOR: #333333; FONT-SIZE: 11pt; FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; COLOR: #333333; FONT-SIZE: 11pt; FONT-WEIGHT: bold">$2,529.00</TD><TD style="TEXT-ALIGN: center; COLOR: #333333; FONT-SIZE: 11pt; FONT-WEIGHT: bold">$2,589.00</TD><TD style="TEXT-ALIGN: center; COLOR: #333333; FONT-SIZE: 11pt; FONT-WEIGHT: bold">$96.00</TD><TD style="TEXT-ALIGN: center; COLOR: #333333; FONT-SIZE: 11pt; FONT-WEIGHT: bold">$260.00</TD></TR><TR style="HEIGHT: 84px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="FONT-SIZE: 11pt; FONT-WEIGHT: bold"> </TD><TD style="FONT-SIZE: 11pt; FONT-WEIGHT: bold"> </TD><TD style="FONT-SIZE: 11pt; FONT-WEIGHT: bold"> </TD><TD style="COLOR: #ff0000; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Total Research Costs Per Time point</TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt; FONT-WEIGHT: bold">$2,477.00</TD><TD style="COLOR: #ff0000; FONT-SIZE: 11pt; FONT-WEIGHT: bold"> </TD><TD style="COLOR: #ff0000; FONT-SIZE: 11pt; FONT-WEIGHT: bold"> </TD><TD style="COLOR: #ff0000; FONT-SIZE: 11pt; FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt; FONT-WEIGHT: bold">$2,571.00</TD></TR><TR style="HEIGHT: 84px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt; FONT-WEIGHT: bold">HCC Code</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Full Unit Charge </TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Research Cost Per Item </TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Protocol Schema Service Description </TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Invoiced Research Costs</TD><TD style="TEXT-ALIGN: center; COLOR: #333399; FONT-SIZE: 11pt; FONT-WEIGHT: bold">pretreatment</TD><TD style="TEXT-ALIGN: center; COLOR: #333399; FONT-SIZE: 11pt; FONT-WEIGHT: bold">course 1 day 1</TD><TD style="TEXT-ALIGN: center; COLOR: #333399; FONT-SIZE: 11pt; FONT-WEIGHT: bold">course 1 day2</TD><TD style="TEXT-ALIGN: center; COLOR: #333399; FONT-SIZE: 11pt; FONT-WEIGHT: bold">course 1 day 3</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">542619</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 11pt">$ 45.00 </TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 11pt">$ 45.00 </TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 11pt">Manual Differential</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">q1</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">q1</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">547677</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 11pt">$ 7.00 </TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 11pt">$ 7.00 </TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 11pt">Blood Smear</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">q0</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">q0</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">540370</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 11pt">$ 56.00 </TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 11pt">$ 56.00 </TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 11pt">D-Dimer</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">q1x2</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">q1x2</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">540385</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 11pt">$ 48.00 </TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 11pt">$ 48.00 </TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 11pt">PT</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">q0x2</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">q0x2</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">540376</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 11pt">$ 47.00 </TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 11pt">$ 47.00 </TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 11pt">PTT</TD><TD style="COLOR: #ff0000; FONT-SIZE: 11pt"> </TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt">r0x2</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">126</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">2201642</TD><TD style="TEXT-ALIGN: center; FONT-STYLE: italic; FONT-SIZE: 11pt">$ 613.00 </TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 11pt">$ 613.00 </TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 11pt">Clinic Charge / Bx core </TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt">r0</TD><TD style="TEXT-ALIGN: center">q1</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">q0x2</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt">r0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">127</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">2201630</TD><TD style="TEXT-ALIGN: center; FONT-STYLE: italic; FONT-SIZE: 11pt">$ 60.00 </TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 11pt">$ 60.00 </TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 11pt">Anesthesia</TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt">r0</TD><TD style="TEXT-ALIGN: center">q1</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">q0x2</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt">r0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">128</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">707614</TD><TD style="TEXT-ALIGN: center; FONT-STYLE: italic; FONT-SIZE: 11pt">$ 137.00 </TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 11pt">$ 137.00 </TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 11pt">Supplies/ Preparation of the Tissue</TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt">r0</TD><TD style="TEXT-ALIGN: center">q1</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">q0x2</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt">r0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">129</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; FONT-STYLE: italic; FONT-SIZE: 11pt">$ 277.00 </TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 11pt">$ 277.00 </TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 11pt">Procurement of Tissue</TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt">r0</TD><TD style="TEXT-ALIGN: center">q1</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">q0x2</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt">r0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">130</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; FONT-STYLE: italic; FONT-SIZE: 11pt">$ 382.00 </TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 11pt">$ 382.00 </TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 11pt">Interpretation & Report </TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt">r0</TD><TD style="TEXT-ALIGN: center">q1</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">q0x2</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt">r0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">131</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">1007649</TD><TD style="TEXT-ALIGN: center; FONT-STYLE: italic; FONT-SIZE: 11pt">$ 504.00 </TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 11pt">$ 504.00 </TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 11pt">Gross & Micro Exam Lvl IV, PathLab; (ea sample ,2 min)</TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt">r0</TD><TD style="TEXT-ALIGN: center">q1</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">q0x2</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt">r0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">132</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">1007649</TD><TD style="TEXT-ALIGN: center; FONT-STYLE: italic; FONT-SIZE: 11pt">$ 504.00 </TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-SIZE: 11pt">$ 504.00 </TD><TD style="TEXT-ALIGN: left; FONT-SIZE: 11pt">Gross & Micro Exam Lvl IV, PathLab; (ea sample ,2 min)</TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt">r0</TD><TD style="TEXT-ALIGN: center">q1</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 11pt">q0x2</TD><TD style="FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-SIZE: 11pt">r0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">133</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 11pt">n/a</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 11pt; FONT-WEIGHT: bold">$ 2,477.00 </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-SIZE: 11pt">$ 2,477.00 </TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 11pt; FONT-WEIGHT: bold">TOTAL Per Procedure</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-SIZE: 11pt">r0</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">q1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-SIZE: 11pt">q0x2</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-SIZE: 11pt"> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; COLOR: #ff0000; FONT-SIZE: 11pt">r0</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4



Calculations Required :
The results will be calculated only once so I do not mind if this is hard coded in VBA . It can of course be placed as a formula or as a user defined function.

<o:p></o:p>
I need a formula ( or vba function, or hard coped –either way) to go down each point in time ( example column U “Invoiced Reach Costs”) , and multiply the associated costs by the designated quantity to provide a total for the. Full price( all the items designated with a Q) and the Research discount price ( all the items designated with an R).
<o:p></o:p>

The curveball is - I must ensure any value whose code in column C is n/a is excluded for the sums .

<o:p></o:p>
I have figured out a nested if statement to extract the correct numeric values but have no idea how to incorporate the results into an array in order to sum product the values against their associated costs.
For Full costs
IF(<st1:stockticker w:st="on">AND</st1:stockticker>(<st1:stockticker w:st="on">LEN</st1:stockticker>(u14)=2,u14<>"r0"),1,IF(<st1:stockticker w:st="on">AND</st1:stockticker>(<st1:stockticker w:st="on">LEN</st1:stockticker>(u14)>=4,LEFT(u14,3)<>"r0x"),MID(u14,4,6),0))

For Discounted Costs
IF(<st1:stockticker w:st="on">AND</st1:stockticker>(<st1:stockticker w:st="on">LEN</st1:stockticker>(u14)=2,LEFT(u14,1)<>"q"),1,IF(<st1:stockticker w:st="on">AND</st1:stockticker>(<st1:stockticker w:st="on">LEN</st1:stockticker>(u14)>=4,LEFT(u14,1)<>"q"),MID(u14,4,6),0))

<o:p></o:p>
I currently have formulas there now that do not seem to work consistantly if at all . I have tried it with both as an array formula and without.





<o:p></o:p>
  • I used this one for the totaling of values for the Qs then sum product the values by the full charge .(It tend to also add the R’s )=SUMPRODUCT(VALUE("0"&MID(U14:U133, SEARCH({0}&"x", U14:U133&{0}&"x")+<st1:stockticker w:st="on">LEN</st1:stockticker>({0})+1, 255)),$L14:$L133)+SUMIF(U14:U133,"q0",$L14:$L133)+SUMPRODUCT(VALUE("0"&MID(U14:U133, SEARCH({0}&"x", U14:U133&{0}&"x")+<st1:stockticker w:st="on">LEN</st1:stockticker>({0})+1, 255)),$L14:$L133)+SUMIF(U14:AA133,"q1",$L14:$L133)

<o:p></o:p>
  • I use this one for the Rs but it tends to ignore items reflects as R0x?
=SUMPRODUCT(VALUE("0"&MID(U14:U133, SEARCH({0}&"x",U14:U133&{0}&"x")+<st1:stockticker w:st="on">LEN</st1:stockticker>({0})+1, 255)),$N14:$N133)+SUMIF(U14:U133,"r0",$N14:$N133)
<o:p></o:p>
<o:p></o:p>
<o:p>Assitance offered would be greatly appreciated. </o:p>
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Depending upon quantities involved (ie always positive and always less than 10) I wonder if perhaps the below would work for you ?

U11: =SUMPRODUCT(--($C$14:$C$132<>"n/a"),--(LEFT(U$14:U$132)="q"),--MID(U$14:U$132&"x1x1",4,1),$L$14:$L$132)
copied across as required

U12 as above but change ref to L range to N range and change "q" to "r"
(if viable use a cell in each row to denote q/r and use one formula for all)

I hope that helps.

FWIW using your file the above would generate results of:

Code: 
0	2529	5066	96	260
2477	0	0	0	2571
 
Last edited:
Upvote 0
****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 10">****** name="Originator" content="Microsoft Word 10"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><o:smarttagtype namespaceuri="urn:schemas-microsoft-com:eek:ffice:smarttags" name="stockticker"></o:smarttagtype><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if !mso]>******** classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui></object> <style> st1\:*{behavior:url(#ieooui) } </style> <![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} a:link, span.MsoHyperlink {color:blue; text-decoration:underline; text-underline:single;} a:visited, span.MsoHyperlinkFollowed {color:purple; text-decoration:underline; text-underline:single;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} /* List Definitions */ @list l0 {mso-list-id:349449990; mso-list-type:hybrid; mso-list-template-ids:-490022096 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l0:level1 {mso-level-tab-stop:.5in; mso-level-number-position:left; text-indent:-.25in;} ol {margin-bottom:0in;} ul {margin-bottom:0in;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]-->
  • the row 135 appears to be irrelevant for the calculations. I am Not deleting that row. But for calculation purposes I am ignoring that row
  • you have given only the rows 126 onwards and I presume that rows 28 to 125 are hidden and do have same type of data
  • the data sheet is called “sheet1”
  • Ignoring the hidden rows I have UPLOADED the data sheet in the following wen page
  • the url for that is:
<o:p> </o:p>
http://www.speedyshare.com/files/21093639/rachael.xls
<o:p> </o:p>
your original results are in rows in 11 and 12. I have kept them there for checking purposes. But the results of the macro will be in rows 8 and 9
<o:p> </o:p>
the macro “test” is in the vbeditor. However I am giving that macro below
<o:p> </o:p>
Sub test()<o:p></o:p>
Dim r As Range, j As Integer, c As Range, costq As Double, costr As Double<o:p></o:p>
Dim totalcostq As Double, totalcostr As Double, unitcostq As Double, unitcostr As Double<o:p></o:p>
Dim k As Integer, m As Integer, lastrow As Integer<o:p></o:p>
Worksheets("sheet1").Activate<o:p></o:p>
lastrow = Cells(Rows.Count, "N").End(xlUp).Offset(-1, 0).Row<o:p></o:p>
MsgBox lastrow<o:p></o:p>
<o:p> </o:p>
k = Range("U13").End(xlToRight).Column - Range("U13").Column<o:p></o:p>
MsgBox k<o:p></o:p>
For m = 1 To k<o:p></o:p>
Set r = Range("v14:v25").Offset(0, m - 1)<o:p></o:p>
MsgBox r.Address<o:p></o:p>
totalcostq = 0<o:p></o:p>
totalcostr = 0<o:p></o:p>
For Each c In r<o:p></o:p>
If c = "" Then<o:p></o:p>
j = 0<o:p></o:p>
GoTo nnext<o:p></o:p>
End If<o:p></o:p>
If Right(c, 1) = 0 Or Right(c, 1) = 1 Then j = 1<o:p></o:p>
If Right(c, 1) > 1 Then j = Right(c, 1) + 0<o:p></o:p>
If Left(c, 1) = "r" Then<o:p></o:p>
unitcostr = Cells(c.Row, "N")<o:p></o:p>
Else<o:p></o:p>
unitcostr = 0<o:p></o:p>
End If<o:p></o:p>
costr = j * unitcostr<o:p></o:p>
'MsgBox costr<o:p></o:p>
If Left(c, 1) = "q" Then<o:p></o:p>
unitcostq = Cells(c.Row, "R")<o:p></o:p>
Else<o:p></o:p>
unitcostq = 0<o:p></o:p>
End If<o:p></o:p>
costq = j * unitcostq<o:p></o:p>
'MsgBox costq<o:p></o:p>
'cost = j * unitcost<o:p></o:p>
<o:p> </o:p>
totalcostq = totalcostq + costq<o:p></o:p>
totalcostr = totalcostr + costr<o:p></o:p>
nnext:<o:p></o:p>
Next c<o:p></o:p>
'MsgBox "totalcost for q=" & totalcostq<o:p></o:p>
'MsgBox "toalcost for r=" & totalcostr<o:p></o:p>
Cells(9, r.Column) = totalcostr<o:p></o:p>
Cells(8, r.Column) = totalcostq<o:p></o:p>
Next m<o:p></o:p>
End Sub<o:p></o:p>
<o:p> </o:p>
Your value in X11(11<sup>th</sup> row in column X)does not agree with my calculation in X8
<o:p> </o:p>
I am giving the relevant data with actual calculations for your perusal.
Please check and give me feedback
<o:p> </o:p>
******** ******************** ************************************************************************><center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="#0c266b"><table width="100%" align="center" border="0"><tbody><tr><td align="left">Microsoft Excel - rachael.xls</td><td style="font-size: 9pt; color: rgb(255, 255, 255); font-family: caption;" align="right">___Running: xl2002 XP : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); height: 25px;" colspan="5" bgcolor="#d4d0c8"><table valign="MIDDLE" width="100%" align="center" border="0"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</td><td valign="center" align="right"><form name="formCb605117"><input *******="window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);" value="Copy Formula" name="btCb942116" type="button"></form></td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="white"><table border="0"><tbody><tr><form name="formFb202339"></form><td style="width: 60px;" align="middle" bgcolor="white"><select onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name="sltNb447362"><option value="=I2*G2" selected="selected">J2<OPTION value==I3*G3>J3</option><option value="=I4*G4">J4</option><option value="=I5*G5">J5</option><option value="=I6*G6">J6</option><option value="=I7*G7">J7</option><option value="=I8*G8">J8</option><option value="=I9*G9">J9</option><option value="=I10*G10">J10</option><option value="=I11*G11">J11</option><option value="=I12*G12">J12</option><option value="=I13*G13">J13</option><option value="=SUM(J2:J13)">J14<!--<st1:stockticker-->OPTION></option></select></td><td width="3%" align="right" bgcolor="#d4d0c8">=</td><td align="left" bgcolor="white"><input size="80" value="=I2*G2" name="txbFb150492"></td></tr></tbody></table></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" width="2%" align="middle">
</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>G</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>H</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>I</center></td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>J</center></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>1</center></td><td style="border: 0.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: center;">Full Unit Charge</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-weight: bold; font-size: 11pt; vertical-align: bottom; color: rgb(51, 51, 153); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: center;">course 1 day 1</td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; text-align: right;"> </td><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><<st1:stockticker>CENT</st1:stockticker>ER>2</td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;">$45.00 </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; text-align: right;">0</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; text-align: right;">$0.00 </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>3</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;">$7.00 </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; text-align: right;">0</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; text-align: right;">$0.00 </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>4</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;">$56.00 </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: center;">q1x2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">2</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; text-align: right;">$112.00 </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>5</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;">$48.00 </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; text-align: right;">0</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; text-align: right;">$0.00 </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>6</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;">$47.00 </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; text-align: right;">0</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; text-align: right;">$0.00 </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>7</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-style: italic; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: center;">$613.00 </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: center;">q0x2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; text-align: right;">2</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; text-align: right;">$1,226.00 </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>8</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-style: italic; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: center;">$60.00 </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: center;">q0x2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">2</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; text-align: right;">$120.00 </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>9</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-style: italic; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: center;">$137.00 </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: center;">q0x2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; text-align: right;">2</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; text-align: right;">$274.00 </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>10</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-style: italic; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: center;">$277.00 </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: center;">q0x2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; text-align: right;">2</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; text-align: right;">$554.00 </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>11</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-style: italic; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: center;">$382.00 </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: center;">q0x2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">2</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">$764.00 </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>12</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-style: italic; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: center;">$504.00 </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: center;">q0x2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">2</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">$1,008.00 </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>13</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-style: italic; font-family: Calibri; background-color: rgb(255, 255, 255); text-align: center;">$504.00 </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(0, 0, 0); font-size: 11pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Calibri; background-color: rgb(255, 255, 255); text-align: center;">q0x2</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">2</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">$1,008.00 </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" width="2%" align="middle"><center>14</center></td><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;"> </td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-size: 12pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial Black; background-color: rgb(255, 255, 255); text-align: right;">$5,066.00 </td></tr><tr><td style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; background-color: rgb(212, 208, 200);" colspan="5"><table valign="TOP" width="100%" align="left"><tbody><tr><td style="border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); border-width: 0.5pt; width: 120pt; background-color: rgb(255, 255, 255);" align="left">Sheet2</td><td> </td></tr></tbody></table></td></tr></tbody></table>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>
<o:p> </o:p>
IMPORTANT NOTE
KEEP THE ORIGINAL <st1:stockticker>DATA</st1:stockticker> <st1:stockticker>SAFE</st1:stockticker>LY SOMEWHERE BEOFER RUNNING THE MACRO. <o:p></o:p>
<o:p> </o:p>
<o:p>give me feedback after running the macro
</o:p>
<o:p> </o:p>
 
Upvote 0
there is a small error in the macro test

I am giving you the correct macro (replace in the vbeditor of the file)


Code: 
Sub test()
Dim r As Range, j As Integer, c As Range, costq As Double, costr As Double
Dim totalcostq As Double, totalcostr As Double, unitcostq As Double, unitcostr As Double
Dim k As Integer, m As Integer, lastrow As Integer
Worksheets("sheet1").Activate
lastrow = Cells(Rows.Count, "N").End(xlUp).Offset(-1, 0).Row
'msgbox lastrow

k = Range("U13").End(xlToRight).Column - Range("U13").Column
'msgbox k
For m = 1 To k
'Set r = Range("v14:v25").Offset(0, m - 1)
Set r = Range("v14:v" & lastrow).Offset(0, m - 1)
'msgbox r.Address
totalcostq = 0
totalcostr = 0
For Each c In r
If c = "" Then
j = 0
GoTo nnext
End If
If Right(c, 1) = 0 Or Right(c, 1) = 1 Then j = 1
If Right(c, 1) > 1 Then j = Right(c, 1) + 0
If Left(c, 1) = "r" Then
unitcostr = Cells(c.Row, "N")
Else
unitcostr = 0
End If
costr = j * unitcostr
''msgbox costr
If Left(c, 1) = "q" Then
unitcostq = Cells(c.Row, "R")
Else
unitcostq = 0
End If
costq = j * unitcostq
''msgbox costq
'cost = j * unitcost

totalcostq = totalcostq + costq
totalcostr = totalcostr + costr

nnext:
Next c



''msgbox "totalcost for q=" & totalcostq
''msgbox "toalcost for r=" & totalcostr


Cells(9, r.Column) = totalcostr
Cells(8, r.Column) = totalcostq
Next m
End Sub
Administrator: Sorry I forgot to put the macro within code tags in my firsts message. aplogise
 
Upvote 0
Thank you both so much! I will try each and see which one can offer the best results .

be back after testing

Sincerely ,

Rachael .
 
Upvote 0
DonkeyOte said:
Depending upon quantities involved (ie always positive and always less than 10) I wonder if perhaps the below would work for you ?

U11: =SUMPRODUCT(--($C$14:$C$132<>"n/a"),--(LEFT(U$14:U$132)="q"),--MID(U$14:U$132&"x1x1",4,1),$L$14:$L$132)
copied across as required

U12 as above but change ref to L range to N range and change "q" to "r"
(if viable use a cell in each row to denote q/r and use one formula for all)

I hope that helps.

FWIW using your file the above would generate results of:

Code: 
0	2529	5066	96	260
2477	0	0	0	2571
Click to expand...
Is there a alter this formula? Yes they will always be positive whole numbers and never more than a total of 7 characters ( a max of 4 digits after the X , ex R0x9999, Q1X9999, Q0X9999)
 
Upvote 0
I think the below might work for any value assuming the value is the last value in the string (if resident at all).

U11:
=SUMPRODUCT(--($C$14:$C$132<>"n/a"),--(LEFT(U$14:U$132)="q"),--(0&SUBSTITUTE(REPLACE(U$14:U$132&"x1",1,FIND("x",U$14:U$132&"x1"),""),"x1","")),$L$14:$L$132)
copied across

U12 as above but again changing "q" to "r" and ref to L range should be N range.
 
Upvote 0
I get a type mismatch error when i applied the macro in my test setting. I am not sure how to post the code all nice like you did. Here is the line of code that triggers the mismatch error :
unitcostq = Cells(c.Row, "R") ' i get a run time error 13 type mismatch




Also i let me address the items you mentioned :
  • the row 135 appears to be irrelevant for the calculations. I am Not deleting that row. But for calculation purposes I am ignoring that row . The last row in my example is row 133 . The list of services is much longer than this , I just pulled this particular portion as an example . There are many more line items like row 133 that need to be excluded from the sums . These are designated by the n/a in column C.
  • you have given only the rows 126 onwards and I presume that rows 28 to 125 are hidden and do have same type of data - correct , and there are additional rows past row 133 , the row numbers are dynamic depending on what services are needed.
I appreciate your hard work and effort . I want to reference your work in the macro , so please let me know what info you would like included.
 
Upvote 0
DonkeyOte said:
I think the below might work for any value assuming the value is the last value in the string (if resident at all).

U11:
=SUMPRODUCT(--($C$14:$C$132<>"n/a"),--(LEFT(U$14:U$132)="q"),--(0&SUBSTITUTE(REPLACE(U$14:U$132&"x1",1,FIND("x",U$14:U$132&"x1"),""),"x1","")),$L$14:$L$132)
copied across

U12 as above but again changing "q" to "r" and ref to L range should be N range.
Click to expand...


Thank you the formula seems to work like a charm , i just have a questions .
can i change the FIND for SEARCH , as the Rs & Qs may be either case and still have the formula work correctly.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
439
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
846
gvsumich
G
J
  • Question
Outlook Hypperlink of fila in email body will not open
Replies
1
Views
448
josros60
J
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A

Forum statistics

Threads
1,215,219
Messages
6,123,687
Members
449,117
Latest member
Aaagu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top