Summing two cells only if certain criteria are met

[mibrahim]

Hi All,

Let me state the scenario:

Cell A1 contains "-50" and cell B1 contains nothing.
Cell A2 contains "-50" and cell B2 contains nothing.
Cell A3 contains nothing and cell B3 contains "+51".
Cell A4 contains nothing and cell B4 contains nothing.
Cell A5 contains nothing and cell B5 contains "+59".
Cell A6 contains "-49" and cell B6 contains nothing.

The sum I am looking for is "+3" which is calculated by the sum(A1,B3,A6).

How can I write a formula that will sum cell(column) A1(A) with the next adjacent value in cell(column) B3(B) and then again with the next adjacent value in cell(column) A6(A)? Meaning, I do not want to include any redundant values in the sum formula from column A, until it is offset with an adjacent value in column B. Then the process can be repeated in reverse. I have a very large data set, so the formula must be uniform so that I can copy it down like 30k rows

PS. Or any workaround that may achieve the desired result will suffice.

Thanks so much!
m

 

[jasonb75]

How do you get +3 from sum(A1,B3,A6)?

-50+51+49 = -48

Am I missing something?

 

[mibrahim]

Jason, good point. Sorry, I forgot to clarify that I would like the formula to repeat the process based on the last figure used. So, basically I would like the formula to sum(A1,B3) and sum(B3,A6) and then sum(A6,'whatever is next in the array'). This would be sum(A1,B3)="+1" and sum(B3,A6)="+2". If the formula provides for a continuous summation, then the sum of both of those would be "+3".

Thanks for the question to clarify.

 

[jasonb75]

That's not quite the answer I was expecting I had an idea for the result I thought might be right, but this is going to need a bit more thinking.

 

[mibrahim]

Oh man :/ Oh well, I'm all ears to potential ideas in the meantime.

Thanks

 

[jasonb75]

It works, but it uses 4 columns to get a result, considering you have 30k rows in your real data set, this could be a problem.

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 53px"><COL style="WIDTH: 53px"><COL style="WIDTH: 59px"><COL style="WIDTH: 49px"><COL style="WIDTH: 59px"><COL style="WIDTH: 82px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Value 1</TD><TD>Value 2</TD><TD>Helper 1</TD><TD>Extract</TD><TD>Last pair</TD><TD>Runing total</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">-50</TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">-50</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">-50</TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: right">51</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">51</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="TEXT-ALIGN: right">59</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">-49</TD><TD> </TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">-49</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C2</TD><TD>=IF(A2<>"",1,IF(B2<>"",2,MAX(0,C1)))</TD></TR><TR><TD>D2</TD><TD>=IF(OR(C2=C1,C2=0),"",SUM(A2:B2))</TD></TR><TR><TD>E3</TD><TD>=IFERROR(D3+LOOKUP(1E+100,D$2:D2),"")</TD></TR><TR><TD>F3</TD><TD>=IF(ISNUMBER(E3),SUM(E$3:E3),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

 

[mibrahim]

Jason,

Let me begin by saying, "you are the man!" Secondly, my 3rd and 4th columns (Last Pair and Running Total) are not displaying anything. Did you mean to type "1E+100" or "E1+100"?

Thanks
m

 

[jasonb75]

1E+100 is correct, it's scientific notation, 1 followed by 100 0's. Did you retain the single $ prompt in the last range of those formula?

 

[mibrahim]

Hey Jason,

Thanks for all the help last week. My computer was having some trouble doing the calculations on 30k rows on Friday, but I'm back up and running today. You have been a great help!

Cheers
m

 

[mibrahim]

Hey Jason, since you're so awesome, I thought I would give you a chance to tackle another predicament I have

I have one (middle) column of numbers and I want to have a running total as we did in the last scenario (above) except only if a certain pattern appears as shown in the numbers below:

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl66, width: 64"]91[/TD]
[TD="class: xl66, width: 64"]91
[/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]92[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]93[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]94[/TD]
[TD="class: xl66, width: 64"]94[/TD]
[TD="class: xl66, width: 64"]3[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]95[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]96[/TD]
[TD="class: xl66, width: 64"]96[/TD]
[TD="class: xl66, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]97[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]98[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]99[/TD]
[TD="class: xl66, width: 64"]99[/TD]
[TD="class: xl66, width: 64"]3[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]100[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]99[/TD]
[TD="class: xl66, width: 64"]99[/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]98[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]97[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]96[/TD]
[TD="class: xl66, width: 64"]96[/TD]
[TD="class: xl66, width: 64"]3[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]95[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]96[/TD]
[TD="class: xl66, width: 64"]96[/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]97[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]96[/TD]
[TD="class: xl66, width: 64"]96[/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]95[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]94[/TD]
[TD="class: xl66, width: 64"]94[/TD]
[TD="class: xl66, width: 64"]-2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]Net Total[/TD]
[TD="class: xl66, width: 64"]9[/TD]
[/TR]
</tbody>[/TABLE]

You'll notice that 3 is the difference between 91 and 94. 2 is the difference between 96 and 94. 3 is the difference between 99 and 96. 99 repeats twice, so we continue to add 3, which is the difference between 96 and 99. Then, 96 is repeated 3x so we now subtract 2, which is the difference between 94 and 96. If the numbers would continue to decrease one or two at a time, the formula should continue to add the differences until the number is repeated 3 or more odd times. As long as it's repeated once or an even amount of times, it should continue to add the difference instead of subtract it. It should only subtract the difference if the number is repeated 3 or more odd times. I want to be able to copy this down 30k rows again - but don't worry about how many columns it takes to come to a conclusion. My comp can handle it

Hope I was clear. Lemme know if you need any more clarification.

Thanks!