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.

 

[ChrisChee]

You are correct. Also would need to work with the Accounts listed in the accounts sheet....so 3 formulas that incorporate all 4 columns. Again, really appreciate your help.

Thanks,
Chris

 

[godsaaint]

Ok, think I finally got it. Though you didn't ask for it, I also provided formulas for the beginning value and ending value:

Beginning Value	=SUMPRODUCT(SUMIFS(B2:B27,A2:A27,Accounts!D2:D11))
Sum of Value differences less than zero (Array formula:CTRL-SHIFT-ENTER)	=SUMPRODUCT(IF(SUMIFS(C2:C27,A2:A27,Accounts!D2:D11)-SUMIFS(B2:B27,A2:A27,Accounts!D2:D11) < 0,SUMIFS(C2:C27,A2:A27,Accounts!D2:D11)-SUMIFS(B2:B27,A2:A27,Accounts!D2:D11)))+SUMPRODUCT(SUMIFS(B2:B27,A2:A27,Accounts!D2:D11,C2:C27,0))
Sum of Value differences less than zero where 2/1/13 is zero	=-SUMPRODUCT(SUMIFS(B2:B27,A2:A27,Accounts!D2:D11,C2:C27,0))
Sum of Value differences greater than zero (Array formula: CTRL-SHIFT-ENTER)	=SUMPRODUCT(IF(SUMIFS(C2:C27,A2:A27,Accounts!D2:D11)-SUMIFS(B2:B27,A2:A27,Accounts!D2:D11) > 0,SUMIFS(C2:C27,A2:A27,Accounts!D2:D11)-SUMIFS(B2:B27,A2:A27,Accounts!D2:D11)))
Ending Value	=SUMPRODUCT(SUMIFS(C2:C27,A2:A27,Accounts!D2:D11))

<tbody>
</tbody>

 

[ChrisChee]

Thank you! That works perfectly in Excel...really appreciate your help! Unfortunately, I was trying to use this formula in a Google Sheet and it doesn't appear as though Google Sheets works with Sumproduct(Sumifs formulas (only sumif, not plural). I will definitely use this in Excel. Very helpful, thanks again!