Complex text and math formula

[gstevens]

Hi everyone,
I have a complex formula I need to figure out that is way over my head. I have a list of rows with text values in one column and corresponding numeric values in another col. What I am trying to do is get subtotals of the numeric data every time the text value changes.
Example:
The first 5 rows the text value is red then the next 8 are blue, the next 10 green etc. Is it possible to have a formula read when the text value changes insert a blank row and give me a subtotal then continuing down the list repeating the process every time the text value changes?

Thank you.

 

[texasalynn]

Why not just use the subtotal feature? found in Data --> Subtotal

 

[gstevens]

Thanks Texasalynn; it’s almost perfect. The numeric data is actually in 2 columns and its giving me the subtotal for each column; not a big deal as I can add these two subtotals together to get one correct subtotal. The only issue is that the sheet has thousands of rows and it’s a timely process to do that.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
This data is from
<o></o>

<TABLE style="MARGIN: auto auto auto 4.65pt; WIDTH: 203pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=271><TBODY><TR style="HEIGHT: 12pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 46pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0in" vAlign=bottom width=61 noWrap>

$1,076.00<o></o>​

​

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 45pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0in" vAlign=bottom width=60 noWrap>

$0.00<o></o>​

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>alab Total<o></o>

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>1,076.00 <o></o>

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

<o></o>
These formulas
<o></o>
<TABLE style="MARGIN: auto auto auto 4.65pt; WIDTH: 278.65pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=372><TBODY><TR style="HEIGHT: 12pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 82.65pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0in" vAlign=bottom width=110 noWrap>;=SUBTOTAL(9,I2:I23)<o></o>

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 84pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0in" vAlign=bottom width=112 noWrap>=SUBTOTAL(9,J2:J23)<o></o>

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>alab Total<o></o>

</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 56pt; PADDING-RIGHT: 5.4pt; HEIGHT: 12pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0in" vAlign=bottom width=75 noWrap>=+J24+I24 <o></o>

</TD></TR></TBODY></TABLE>
<o></o>
Last question; is there a way to avoid manually adding these two columns and getting one subtotal?
<o></o>
Thanks again for your help!

 

[texasalynn]

I would suggest that you put in the column to add the two columns, then do the subtotal on that.

 

[gstevens]

That works.

Thank again.