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]

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

<colgroup><col span="7"><col><col></colgroup><tbody>
</tbody>

 

[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 >

Sum of Value differences less than zero	=SUMIFS(Accounts!H2:H11,Accounts!H2:H11," < "&0,Accounts!G2:G11," > "&0)
Sum of Value differences less than zero where 2/1/13 is zero	=SUMIFS(Accounts!H2:H11,Accounts!H2:H11," < "&0,Accounts!G2:G11,"="&0)
Sum of Value differences greater than zero	=SUMIFS(Accounts!H2:H11,Accounts!H2:H11," > "&0)

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[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.