Sumif the difference between two columns for accounts listed in column x if the value is less than zero

[ChrisChee]

Good Morning,

I am trying to find if there is any way to use a sumif formula that will:

SumIf the difference between Column C and Column B for all accounts in Column A that are in the list shown in Column D on another spreadsheet.

I have used a sumproduct(sumif formula in order to sum the value in Column C of accounts in Column A that are in the list in Column D on another spreadsheet (ending value) and again the sumproduct(sumif formula in order to sum the value in Column B of accounts in Column A that are in the list in Column D on another spreadsheet (beginning value). However, I want to be able to break down the difference between beginning and ending value by accounts that have changed value between Column C and Column B greater than zero, less than zero, and no change at all.

Any help would be much appreciated!

Thanks.

 

[godsaaint]

Could you provide an example of your table with the expected result?

 

[ChrisChee]

https://drive.google.com/file/d/0B1R1HMa1jqOVUDdCVV9DTW9nQjA/view?usp=sharing

Hi,

Let me know if the link above does not work. This is a very simple example as I have multiple lists and many periods to compare, however, I think you will see what I am trying to do from the example attached. I have highlighted in yellow the fields that I need a formula for with the expected results now (note I was able to get these results manually given the small amount of data).

Thanks!

 

[godsaaint]

I can't access it where I am at the moment, could you copy-paste it into the message box?

 

[ChrisChee]

[TABLE="width: 812"]
<colgroup><col span="7"><col><col></colgroup><tbody>[TR]
[TD]Accounts[/TD]
[TD="align: right"]1/1/2013[/TD]
[TD="align: right"]2/1/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Account Name[/TD]
[TD]Expected Results:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD]Beginning Value[/TD]
[TD="align: right"]316[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]Sum of Value differences less than zero[/TD]
[TD="align: right"]-4[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD][/TD]
[TD]Sum of Value differences less than zero where 2/1/13 is zero[/TD]
[TD="align: right"]-13[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD][/TD]
[TD]Sum of Value differences greater than zero[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD]E[/TD]
[TD][/TD]
[TD]Ending Value[/TD]
[TD="align: right"]322[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD]Z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]37[/TD]
[TD][/TD]
[TD][/TD]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]43[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]41[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]46[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]52[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]O[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Q[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]61[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]64[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]U[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]68[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]V[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]73[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]79[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]77[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]82[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]89[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[godsaaint]

Ok, I think I follow most of what you mean. You only need formulas to get the "-13" and "23" in this case?

 

[ChrisChee]

Awesome! And the -4, however, that figure could be backed into as that would be the only remaining variable.

Thanks again!

 

[godsaaint]

Try the following:

Delete the spaces before and after each < or >

[TABLE="class: grid, width: 419"]
<tbody>[TR]
[TD]Sum of Value differences less than zero[/TD]
[TD]=SUMIFS(Accounts!H2:H11,Accounts!H2:H11," < "&0,Accounts!G2:G11," > "&0)[/TD]
[/TR]
[TR]
[TD]Sum of Value differences less than zero where 2/1/13 is zero[/TD]
[TD]=SUMIFS(Accounts!H2:H11,Accounts!H2:H11," < "&0,Accounts!G2:G11,"="&0)[/TD]
[/TR]
[TR]
[TD]Sum of Value differences greater than zero[/TD]
[TD]=SUMIFS(Accounts!H2:H11,Accounts!H2:H11," > "&0)[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[ChrisChee]

Thanks for the quick response. Assuming column G is 1/1/2013 and column H is 2/1/2013, this formula doesn't really get to where I need to be. For only those accounts listed under "Account Name" in my example, I am trying to aggregate from the table to the left in my example, those accounts which decreased in value between 1/1/2013 and 2/1/2013, those which churned (e.g. had a value at 1/1/2013, but no value at 2/1/2013), and those that increased in value between 1/1/2013 and 2/1/2013. In my example, I am looking the difference between only two periods, however, I am trying to use a formula that can bifurcate the change in account values looking at different periods. Therefore, I cannot simply create a column to show the value differences and then sumif as the periods are changing depending on what span of months I am looking at and the list of accounts changes depending on the period I am referencing.

Hopefully I did not over complicate things with my explanation, however, thought maybe explaining the purpose would help produce the result. Thanks again for your help.

 

[godsaaint]

I got to open the first link you provided and I guided myself with the "Accounts" sheet where you had beginning value and ending value. So, what you need are 3 formulas that work directly from Accounts/1-1-2013/2-1-2013 columns? Let me think a little bit.