Improve or change a formula

[Dan_K_2014]

Hello,
would you please help me to improve the following formula?

=IFERROR(SUMIF(TB!$A$7:$A$10000,$B6,INDEX(TB!$C$6:$P$10000,0,MATCH($F$3,TB!$C$5:$P$5,0))),"")

I need add one more MATCH cell $J$4, TB!$B$6:$B$2000.

Thank you.

 

[bosco_yip]

Maybe,

=IFERROR(SUMIFS(INDEX(TB!$C$6:$P$10000,0,MATCH($F$3,TB!$C$5:$P$5,0)),TB!$A$6:$A$10000,$B6,TB!$B$6:$B$10000,$J$4),"")

Regards
Bosco

 

[Dan_K_2014]

Hello Bosco,
Your formula is working, but there is one issue. If I do not select any identifier in J4, I would like to get Total number(sum) from C610000, based on selection of A... like I had in my formula before addition of J4.

 

[Chihiro]

Just nest it in IF statement?
=IF($J$4="",SUM($C$6:$P$10000),Formula)

 

[Dan_K_2014]

tHIS WHAT i GOT: =-IFERROR(SUMIFS(INDEX(TB!$C$6:$P$10000,0,MATCH($D$3,TB!$C$5:$P$5,0)),TB!$A$6:$A$10000,$B9,TB!$B$6:$B$10000,$J$4),"")
BUT it shows "zero" instead of $4,000

 

[Dan_K_2014]

Sorry, copied wrong cell:
.=-IF($J$4="",SUM(TB!$C$6:$P$10000),IFERROR(SUMIFS(INDEX(TB!$C$6:$P$10000,0,MATCH($D$3,TB!$C$5:$P$5,0)),TB!$A$6:$A$10000,$B8,TB!$B$6:$B$10000,$J$4),""))

 

[Dan_K_2014]

Hello all,
Can anyone help to improve this formula?
=IF($J$4="",SUM(TB!$C$6:$P$10000),IFERROR(SUMIFS(INDEX(TB!$C$6:$P$10000,0,MATCH($D$3,TB!$C$5:$P$5,0)),TB!$A$6:$A$10000,$B8,TB!$B$6:$B$10000,$J$4),""))
It returns "zero" instead of $4,000.
Initially, I had this formula:

=IFERROR(SUMIF(TB!$A$7:$A$10000,$B6,INDEX(TB!$C$6:$P$10000,0,MATCH($F$3,TB!$C$5:$P$5,0))),"")

I needed to add one more MATCH cell $J$4, TB!$B$6:$B$2000.

 

[Chihiro]

So original formula works without J4, and bosco's formula works for when there is J4 condition. Correct? Then IF formula becomes...

=IF($J$4="",IFERROR(SUMIF(TB!$A$7:$A$10000,$B6,INDEX(TB!$C$6:$P$10000,0,MATCH($F$3,TB!$C$5:$P$5,0))),""),IFERROR(SUMIFS(INDEX(TB!$C$6:$P$10000,0,MATCH($F$3,TB!$C$5:$P$5,0)),TB!$A$6:$A$10000,$B6,TB!$B$6:$B$10000,$J$4),""))

 

[Chihiro]

Don't know if it will work with your data, but you could try another method.
=IFERROR(SUMIFS(INDEX(TB!$C$6:$P$10000,0,MATCH($F$3,TB!$C$5:$P$5,0)),TB!$A$6:$A$10000,$B6,TB!$B$6:$B$10000,IF($J$4="","<>",$J$4)),"")

 

[Dan_K_2014]

Thank you, Chihiro. It looks like, it is working. I will test it later and if there is a problem, I will write a post-