Testing Multiple Criteria to sum specific values in a range

I am trying to put together a budget spreadsheet to track expenses and income. Both credits and debits are entered into the same range of cells ongoing, for each budget line item. NOTE: the type of transaction is NOT a column heading or a text string that is typed in; it is selected from a dop-down list of several choices (screenshot)

screenshot1.jpg

The only formula I have trouble with is how to filter out most types of credits but retain specific credits that offset spending (such as a refund, which is not truly "income") so as to avoid inaccurate totals. For example: a deposit of $200 (not counted), a debit of $75, a refund of $50, and another debit of $100. In this example total spending should be $125 (because of the refund). If "Refund" is filtered out along with "Dep Csh" when calculating "Total Debits" it would incorrectly show "Total Debits" as $175 when it should be $125. So far I have come up with a nested IF function using OR and AND, but I keep getting a #VALUE! error. (screenshot)

Screenshot2.jpg

Please help! I need to balance my budget tonight. I suppose a "Plan B" could be to create additional columns that deal separately with "Total Refunds" etc and then account for those totals in the "Total Debits" formula but I really want to avoid excessive space and formulas.

nigelbloomy,

that gets me farther than I was before because it does evaluate to $125 by appropriately accounting for "Refund". I need to see if the first SUMIF in your formula can be revised to search a2:d2 for any negative value, so that any kind of debit (bank fee, ACH withdrawal, debit, etc) would be totaled in the "Total Debits" column.

Special-K,

I must confess my ignorance on array formulas or how to use them. Please elaborate if you can.

hey thanks! Funny I actually ended up solving with the same equation ^ sometime after your first reply. just couldn't log back on yet to tell you. So I guess if I need to specify additional criteria to include or exclude in the formula I could just tack on another SUMIF function in this formula. That makes sense. Have a good weekend, and thanks again!