Help out for How to use sumifs with index, match formulas

[Yvonneyeong]

How to get the summary list for total sum base on multi criteria search base on company and status.

Waiting for your prompt reply, thanks!

Yvonne Yeong

 

[bosco_yip]

Total sum of "total payable" Col L based on company (G4) and status (H4)

In I4 enter formula :

=SUMPRODUCT((B7:B13=H4)*ISNUMBER(SEARCH(LEFT(D7:D13,FIND(" ",D7:D13&" ")-1),G4)),L7:L13)

Regards
Bosco

 

[Yvonneyeong]

Total sum of "total payable" Col L based on company (G4) and status (H4)

In I4 enter formula :

=SUMPRODUCT((B7:B13=H4)*ISNUMBER(SEARCH(LEFT(D7:D13,FIND(" ",D7:D13&" ")-1),G4)),L7:L13)

Regards
Bosco

Thank you very much. How about if total sum for entire entity with difference status?

 

[bosco_yip]

Thank you very much. How about if total sum for entire entity with difference status?

1] Your G4 criteria of company name changed as same as the Col D namelist, then a shorter Sumifs formula can be used instead of Sumproduct.

2] In I4, enter :

=SUMIFS(L7:L13,B7:B13,IF(H4="ALL","<>",H4),D7:D13,IF(G4="ALL","<>",G4))

Regards
Bosco