Add Criteria to Complex Sumifs Formula

[NNewburger]

Hello Forum,

In any worksheet, there will be five columns of data: A, B, C, & D will always have data. E will sometimes have data.

(A) Trans. Date (B) Post Date (C) Description (D) Amount (E) Category

There will be a dynamic number of rows depending on the number of transactions.

The goal is to create a grouping of cells containing formulas that can be copied and pasted to other worksheets that contain transactions from different sources and time periods. Below is that grouping so far.
[TABLE="width: 546, align: left"]
<tbody>[TR]
[TD="colspan: 5"] RECONCILIATION SECTION[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]NET CHANGE PER TRANSACTIONS[/TD]
[TD]-3127.51[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]SUMIFS($D$2:$D$200,$C$2:$C$200,"<>"&"",$C$2:$C$200,"<>"&"NET CHANGE PER TRANSACTIONS",$C$2:$C$200,"<>"&"Stmt Beginning Balance",$C$2:$C$200,"<>"&"Stmt Ending Balance",$C$2:$C$200,"<>"&"NET CHANGE PER STATEMENT",$C$2:$C$200,"<>"&"RECONCILING ITEMS",$C$2:$C$200,"<>"&"NET CHANGE CURRENT MONTH",$C$2:$C$200,"<>"&"ADJUSTED BALANCE")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12/12/15[/TD]
[TD]Stmt Beginning Balance[/TD]
[TD]-6215.58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]01/09/16[/TD]
[TD]Stmt Ending Balance[/TD]
[TD]-3088.07[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]NET CHANGE PER STATEMENT[/TD]
[TD]3127.51[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]SUM(OFFSET($A$1,MATCH("Stmt Ending Balance",$C$2:$C$200,0),3))-OFFSET($A$1,MATCH("Stmt Beginning Balance",$C$2:$C$200,0),3)[/TD]
[/TR]
[TR]
[TD]01/01/16[/TD]
[TD]01/09/16[/TD]
[TD]RECONCILING ITEMS[/TD]
[TD]3898.33[/TD]
[TD]-3898.33[/TD]
[/TR]
[TR]
[TD="colspan: 5"]SUMIFS($D$2:$D$200,$B$2:$B$200,">="&OFFSET($A$1,MATCH("RECONCILING ITEMS",$C$2:$C$200,0),0),$C$2:$C$200,"<>"&"NET CHANGE PER TRANSACTIONS",$C$2:$C$200,"<>"&"Stmt Beginning Balance",$C$2:$C$200,"<>"&"Stmt Ending Balance",$C$2:$C$200,"<>"&"NET CHANGE PER STATEMENT",$C$2:$C$200,"<>"&"RECONCILING ITEMS",$C$2:$C$200,"<>"&"NET CHANGE CURRENT MONTH",$C$2:$C$200,"<>"&"ADJUSTED BALANCE")*-1[/TD]
[/TR]
[TR]
[TD="colspan: 5"]SUMIFS($D$2:$D$200,$B$2:$B$200,">="&OFFSET($A$1,MATCH("RECONCILING ITEMS",$C$2:$C$200,0),0),$C$2:$C$200,"<>"&"NET CHANGE PER TRANSACTIONS",$C$2:$C$200,"<>"&"Stmt Beginning Balance",$C$2:$C$200,"<>"&"Stmt Ending Balance",$C$2:$C$200,"<>"&"NET CHANGE PER STATEMENT",$C$2:$C$200,"<>"&"RECONCILING ITEMS",$C$2:$C$200,"<>"&"NET CHANGE CURRENT MONTH",$C$2:$C$200,"<>"&"ADJUSTED BALANCE")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/01/15[/TD]
[TD]12/31/15[/TD]
[TD]NET CHANGE CURRENT MONTH[/TD]
[TD]-770.82[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]SUM(OFFSET($A$1,MATCH("NET CHANGE PER STATEMENT",$C$2:$C$200,0),3)-(OFFSET($A$1,MATCH("RECONCILING ITEMS",$C$2:$C$200,0),3)))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12/31/15[/TD]
[TD]ADJUSTED BALANCE[/TD]
[TD]-6986.40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]SUM(OFFSET($A$1,MATCH("Stmt Beginning Balance",$C$2:$C$200,0),3)+(OFFSET($A$1,MATCH("NET CHANGE CURRENT MONTH",$C$2:$C$300,0),3)))[/TD]
[/TR]
</tbody>[/TABLE]
The issue I need help with is to add conditions to the formula in the First Column to the Right of “NET CHANGE PER TRANSACTIONS” (Pink).
In addition to what the formula is currently doing, I want to add the conditions that:

If Column E is Not Blank
the Amount in Net Change Per Transactions (-3127.51) should be multiplied by -1 (Negative One)

If Column E is Blank
the Amount in Net Change Per Transactions (-3127.51) should be multiplied by 1 (One)

These formulas might not be the most efficient or elegant way to accomplish what I need, but they work and because I am a novice at formulas, in some cases I’m not sure how they work. I would appreciate any answers to be on the simpler side.

Sorry for all the CAPS, but that is how the text is written and therefore, the formulas. Thanks in advance to all who are willing to help with this.

 

[Rijnsent]

Hi NNewburger,
if formulas tend to get this big, I generally create some extra columns in my data. E.g:

SUMIFS($D$2:$D$200,$C$2:$C$200,"<>"&"",$C$2:$C$200,"<>"&"NET CHANGE PER TRANSACTIONS",$C$2:$C$200,"<>"&"Stmt Beginning Balance",$C$2:$C$200,"<>"&"Stmt Ending Balance",$C$2:$C$200,"<>"&"NET CHANGE PER STATEMENT",$C$2:$C$200,"<>"&"RECONCILING ITEMS",$C$2:$C$200,"<>"&"NET CHANGE CURRENT MONTH",$C$2:$C$200,"<>"&"ADJUSTED BALANCE")

->Basically there are a whole lot of conditions for column C. I would put the "not desired" values into a list, add column F to your data and use a match formula to create a simple value (e.g. 0 or 1) that you could use in your SUMIF formula. The same goes with your other questions: add one or 2 columns and that makes your SUMIFS formula a whole lot more readable.

Hope that helps,

Koen