Help on SUMIFS with text in the sum_range

[badsham777]

I do the below SUMIFS to get SUM of value that meet the criteria and working fine.

=SUMIFS('book1.xlsx'!$G:$G,'book1.xlsx'!$B:$B,D2,'book1.xlsx'!$D:$D,"*"&G2&"*",'book1.xlsx'!$E:$E,H2)

The problem is now for column G (as bolded) has become text and I just want the formula to display the text result that meet the criteria as in the SUMIF.

Can anyone help me on this?

 

[Chrisdontm]

Your problem is not a SUMIFS anymore, it may just be an IF...
Or, it has become an INDEX / MATCH

 

[kvsrinivasamurthy]

Try this

=SUMPRODUCT(VALUE('book1.xlsx'!$G:$G)*('book1.xlsx'!$B:$B=D2)*ISNUMBER(FIND(G2,'book1.xlsx'!$D:$D))*('book1.xlsx'!$E:$E=H2))

 

[badsham777]

Try this

=SUMPRODUCT(VALUE('book1.xlsx'!$G:$G)*('book1.xlsx'!$B:$B=D2)*ISNUMBER(FIND(G2,'book1.xlsx'!$D:$D))*('book1.xlsx'!$E:$E=H2))

Sadly I just got #VALUE ! on this......

 

[kvsrinivasamurthy]

Pl upload an image of sheet book1.xlsx