Improve formula

[Kazdima]

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.

 

[Aladin Akyurek]

2000 while the formula refers to 10000 as end row. Which is valid?

 

[Kazdima]

Sorry, there are 10,000 rows.

 

[Aladin Akyurek]

Looks like you need SUMIFS...

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

 

[Kazdima]

Hello,
the formula give "#value". I see that in formula we are matching only to F3, but not to J4. Amount should be selected from C6:C10000 if a cross reference matches to two exact changing selections - F3 and J4.

 

[Kazdima]

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.

 

[Aladin Akyurek]

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.

The formula tests also J4...


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

 

[Kazdima]

Thank you, Aladin for your help.

 

[Aladin Akyurek]

Thank you, Aladin for your help.

Correction...

=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),"")