Please improve this formula.

[srizki]

I have this mega formula, but it is just repetitive of IFS. I want to see if it can be more efficient?

=IF(OR($A14="4 NET_ INCOME ',$A14="ECCS Net Income") ,SUMIFS(PGL!K:K,PGL!$E:$E,"NET
INCOME (Excl Non-Ctrl)"),IF($A14="4_ NET INCOME_ INCL NC" ,SUMIFS(PGL!K:K,PGL!$E:$E,"NET
INCOME (Incl Non-
Ctrl)"),IF($A14="4_NI BEF SUB INCL NC",SUMIFS(PGL!K:K,PGL!$E:$E,"INCOME BEFORE SUBS
(Incl Non-Ctrl)"),IF($A14="4_NI_ BEF _TAX SUB",SUMIFS(PGL!K:K,PGL!$E:$E,"INCOME BEFORE TAX
& SUBS (Incl Non-Ctrl)"),IF($A14="41 INTRST INC",SUMIFS(PGL!K:K,PGLI$E:$E, "INTEREST
INCOME") ),IF($A14="41 NET INTRST INC" ,SUMIFS(PGL!K:K,PGLI$E:$E,"NET INTEREST
INCOME"),IF($A14="42 INTEREST _EXP",SUMIFS(PGL!K:K,PGLI$E:$E. "INTEREST
EXPENSE"),IF($A14="44 LOAN LOSS PRV",SUMIFS(PGL!K:K,PGL!$E:$E,"LOAN LOSSES
PROV/(REVERSAL)") ),IF($A14="45 NII NON INTINC",SUMIFS(PGL!K:K,PGLI$E :$E "NON-INT
INCOME"),IF($A14="5 NON INTR EXP",SUMIFS(PGL!K:K,PGL!$E:$E, "NON-INTEREST
EXPENSE"),IF($A14="7 INCOME_ _TAX",SUMIFS(PGL!K:K,PGL!$E:$E "INCOME
TAX"),IF($A14="70 INCOME SUBS" ,SUMIFS(PGL!K:K,PGL!$E:$E, "INCOME FROM
SUBS"),IF($C14<>"" ,SUMIFS(PGL!K:K,PGL!$F:$E!Compare (Rollup)'!$C14),SUMPRODUCT(--
(CONCATENATE("%,N",$A14)=PGL!$A$17.$A$ 1000),PGL!K$17:K$ 1000)+SUMPRODUCT(--
(CONCATENATE("%,R," ,$A14)=PGL !.$A$17:$A$1000),PGL!K$17:K$ 1O0O))))))))))))))

Best Regards

 

[shift-del]

Please provide some data. 15 to 20 rows. Use XL2BB or a sharted link.

 

[srizki]

I can not provide the data, it is on my work computer, but the formula says it all, =IF(SUMIFS, and SUMPRODUCT formula.

 

[shift-del]

The formula says: I am invalid.

 

[Alex Blakenburg]

You really need to help us help you. As shift-del mentioned your formula is invalid. Did you use an ink to text converter on it or something, the errors look like typo's but you can't possibly have typed that all in eg: PGL!K$17:K$ 1O0O (the letter O where it should be 0)

I suggest you start with throwing a lookup table into the mix to convert what you have in A14 into the value you need for the SumIfs column E evaluation.
That should reduce the whole formula to a single SumIfs and your catch all SumProduct formula at the end.
I would recommend making the mapping table an Excel Table to minimise future maintenance.

20240517 Nested If with Mapping Table srizki.xlsx
	A	B	C	D	E
11				Lookup Table to Convert A14 value to Data Table values
12
13	Category to Sum	SumIfs		A14 Value	Full Description
14	4_ NET INCOME_ INCL NC	3,030		ECCS Net Income	NETINCOME (Excl Non-Ctrl)
15				4_ NET INCOME_ INCL NC	NETINCOME (Incl Non-Ctrl)
16				4_NI BEF SUB INCL NC	INCOME BEFORE SUBS(Incl Non-Ctrl)
17				4_NI_ BEF _TAX SUB	INCOME BEFORE TAX& SUBS (Incl Non-Ctrl)
18
19
Main

Cell Formulas
Range	Formula
B14	B14	=SUMIFS(PGL!K:K,PGL!E:E,VLOOKUP($A14,$D$14:$E$17,2,FALSE))

 

[srizki]

Hi Alex,
Thanks for providing me with the formula. I am using the following formula:
=F(C13<>"",SUMIF(PGL!F:F,'COMPARE (ROllup)'!C13,PGL!CC:CC),SUMIF(PGL!E:E,'COMPARE(ROLLUP)'!B13,PGL!CC:CC))
I just noticed that the lookup value is in column B, on sheet COMPARE (ROllup), but the range to match the value on sheet PGL could be in column A or Column E.
Please provide the formula,

 

[Alex Blakenburg]

I need to see what your 2 sheets look like. If you use an image please include the row and column references.
An XL2BB of both would be useful or a sample of the workbook via Dropbox, Google Drive or some other sharing platform.
Also were is the formula going and what is the expected result