Nested IF OR not returning a result

[MixedMadness3]

Hi!

I have 5 possible outputs in column B (Expense, Income, Assets, Liability or Equity). Based on that, my formula needs to reference another column and look for "DR" or "CR" or blank and return either a positive or negative result.

My original formula works when only referencing 2 of the possible 5 outcomes (Income and Expense) but Excel won't read it once I added the other conditions and I don't know what I'm doing wrong.

Attached is a sample file. Column L contains my original formula that works with Income and Expense only. Rows 25 and 26 contain the formula I need help with.

I imagine this would be more efficient with VBA but my skills just aren't there yet.

Thanks in advance!

 

[Chihiro]

I'd suggest using named range (or table). See attached for an example.

Sheet2 contains named ranges for the purpose of calculation.

Note that formula is entered as array (CSE).

 

[MixedMadness3]

Array formulas have stumped me for a while but this solution gives me motivation to figure it out. Thank you so much it is exactly what I was looking for!

 

[bosco_yip]

Another option,

1] Set up a new Negative Output Table in range N1: P7 according to your specification.

2] In L2, formula copy down :

=IF(B2="","",IF(SUMPRODUCT((N$3:N$7=B2)*((O$3:O$7=I2)+(P$3: P$7=K2))),-J2,J2))

3] See attached file.

Regards
Bosco

 

[MixedMadness3]

It took some time to wrap my head around the beautiful simplicity of the Sumproduct formula. Thank you Bosco! This is another great solution.

I've got a massive data base and both of these solutions run so efficiently.

 

[bosco_yip]

It took some time to wrap my head around the beautiful simplicity of the Sumproduct formula. Thank you Bosco! This is another great solution.
I've got a massive data base and both of these solutions run so efficiently.

Or further shortened to :

=IF(B2="","",(1-SUMPRODUCT((N$3:N$7=B2)*((O$3:O$7=I2)+(P$3: P$7=K2)))*2)*J2)

Regards
Bosco