Shorten Formula or easier way

[GeneBF]

Hi so I have this formula in TAB X (it's working but i know there's an easier way you guys can come up with)
I need to Sum up the values of an item depending on it's description.
Values are on each Month's Tab; (E5:E20000)
Item's Description (a keyword) is on TAB X, A1 and so on, description on this item will match either Description A or B
Each month's tab has Description Column; Description A (C5:C20000) and Description B (D5:D20000)
Each month's tab has Code Column; (B5:B20000)

i need to sum up the value of the item with description depending on the keyword, either Description A or B will match, and if both A and B for latest month (MAY TAB) has nothing it will instead sum up on the previous month (APRIL TAB) with same scenarios

=IF(A1="",0,
IF(AND(
SUMIFS('MAY TAB'!$E$5:$E$20000,'MAY TAB'!$B$5:$B$20000,AK12,'MAY TAB'!$C$5:$C$20000,"*"&A1&"*")=0,
SUMIFS('MAY TAB'!$E$5:$E$20000,'MAY TAB'!$B$5:$B$20000,AK12,'MAY TAB'!$D$5:$F$20000,"*"&A1&"*")=0
),
IF(
SUMIFS('APRIL TAB'!$E$5:$E$20000,'APRIL TAB'!$B$5:$B$20000,AK12,'APRIL TAB'!$C$5:$C$20000,"*"&A1&"*")=0,
SUMIFS('APRIL TAB'!$E$5:$E$20000,'APRIL TAB'!$B$5:$B$20000,AK12,'APRIL TAB'!$D$5:$F$20000,"*"&A1&"*"),
SUMIFS('APRIL TAB'!$E$5:$E$20000,'APRIL TAB'!$B$5:$B$20000,AK12,'APRIL TAB'!$C$5:$C$20000,"*"&A1&"*")
),
IF(
SUMIFS('MAY TAB'!$E$5:$E$20000,'MAY TAB'!$B$5:$B$20000,'MAY TAB'!$C$5:$C$20000,"*"&A1&"*")=0,
SUMIFS('MAY TAB'!$E$5:$E$20000,'MAY TAB'!$B$5:$B$20000,'MAY TAB'!$D$5:$F$20000,"*"&A1&"*"),
SUMIFS('MAY TAB'!$E$5:$E$20000,'MAY TAB'!$B$5:$B$20000,'MAY TAB'!$C$5:$C$20000,"*"&A1&"*")
)))

 

[kvsrinivasamurthy]

Try

=Let(mc,SUMIFS('MAY TAB'!$E$5:$E$20000,'MAY TAB'!$B$5:$B$20000,AK12,'MAY TAB'!$C$5:$C$20000,"*"&A1&"*"),md,SUMIFS('MAY TAB'!$E$5:$E$20000,'MAY TAB'!$B$5:$B$20000,AK12,'MAY TAB'!$D$5:$F$20000,"*"&A1&"*"),ac,SUMIFS('APRIL TAB'!$E$5:$E$20000,'APRIL TAB'!$B$5:$B$20000,AK12,'APRIL TAB'!$C$5:$C$20000,"*"&A1&"*"),ad,SUMIFS('APRIL TAB'!$E$5:$E$20000,'APRIL TAB'!$B$5:$B$20000,AK12,'APRIL TAB'!$D$5:$F$20000,"*"&A1&"*"),IF(A1="",0,If(mc+md=0,If(ac=0,ad,ac),If(mc=0,md,mc))))

 

[Fluff]

Another option

=LET(ma,SUMIFS('MAY TAB'!$E$5:$E$20000,'MAY TAB'!$B$5:$B$20000,AK12,'MAY TAB'!$C$5:$C$20000,"*"&A1&"*"),mb,SUMIFS('MAY TAB'!$E$5:$E$20000,'MAY TAB'!$B$5:$B$20000,AK12,'MAY TAB'!$D$5:$D$20000,"*"&A1&"*"),aa,SUMIFS('APRIL TAB'!$E$5:$E$20000,'APRIL TAB'!$B$5:$B$20000,AK12,'APRIL TAB'!$C$5:$C$20000,"*"&A1&"*"),ab,SUMIFS('APRIL TAB'!$E$5:$E$20000,'APRIL TAB'!$B$5:$B$20000,AK12,'APRIL TAB'!$D$5:$D$20000,"*"&A1&"*"),IF(A1="",0,IF(ma>0,ma,IF(mb>0,mb,IF(aa>0,aa,ab)))))

 

[GeneBF]

Try

=Let(mc,SUMIFS('MAY TAB'!$E$5:$E$20000,'MAY TAB'!$B$5:$B$20000,AK12,'MAY TAB'!$C$5:$C$20000,"*"&A1&"*"),md,SUMIFS('MAY TAB'!$E$5:$E$20000,'MAY TAB'!$B$5:$B$20000,AK12,'MAY TAB'!$D$5:$F$20000,"*"&A1&"*"),ac,SUMIFS('APRIL TAB'!$E$5:$E$20000,'APRIL TAB'!$B$5:$B$20000,AK12,'APRIL TAB'!$C$5:$C$20000,"*"&A1&"*"),ad,SUMIFS('APRIL TAB'!$E$5:$E$20000,'APRIL TAB'!$B$5:$B$20000,AK12,'APRIL TAB'!$D$5:$F$20000,"*"&A1&"*"),IF(A1="",0,If(mc+md=0,If(ac=0,ad,ac),If(mc=0,md,mc))))

Thanks! I forgot the Let function entireley, appreciate this one.

 

[GeneBF]

Another option

=LET(ma,SUMIFS('MAY TAB'!$E$5:$E$20000,'MAY TAB'!$B$5:$B$20000,AK12,'MAY TAB'!$C$5:$C$20000,"*"&A1&"*"),mb,SUMIFS('MAY TAB'!$E$5:$E$20000,'MAY TAB'!$B$5:$B$20000,AK12,'MAY TAB'!$D$5:$D$20000,"*"&A1&"*"),aa,SUMIFS('APRIL TAB'!$E$5:$E$20000,'APRIL TAB'!$B$5:$B$20000,AK12,'APRIL TAB'!$C$5:$C$20000,"*"&A1&"*"),ab,SUMIFS('APRIL TAB'!$E$5:$E$20000,'APRIL TAB'!$B$5:$B$20000,AK12,'APRIL TAB'!$D$5:$D$20000,"*"&A1&"*"),IF(A1="",0,IF(ma>0,ma,IF(mb>0,mb,IF(aa>0,aa,ab)))))

Thanks for another option you provided, appreciate it!

 

[Fluff]

Glad we could help & thanks for the feedback.