extract numbers then sumproduct values against another column of data

[Rachaelsqa]

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-comffice: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-comfficeffice" /><o></o>
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></o>
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></o>

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

<o></o>
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></o>
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></o>

• 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></o>

• 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></o>
<o></o>
<o>Assitance offered would be greatly appreciated. </o>

 

[DonkeyOte]

You should only need to change the FIND to SEARCH if the quantity prefix (x) varies in case - otherwise the formula should work without issue.

 

[venkat1926]

Rachael

If the problem has already been solved I will not pursue with my solution please confirm

 

[Rachaelsqa]

Yes thank you . I appreciate everyones help very much!