One for the Excel gurus

[toon10]

I wonder if an Excel genius can lend a fellow a hand. I have a basic sheet and I am trying to search a range for text. Every time text exists in the range, it sums up the value in a corresponding column. So far so good and I can do this using this formula… =SUMIF(G2:G500,"ABCD",E2:E500)

If the text “ABCD” is found in within G2 to G500, my cell totals up the values in the corresponding E cells. For example, if cell G5 and G13 = “ABCD” and cell E5 = 65 and cell E13 = 30 then my formula cell will display the total 95. This all works well.

What I am trying to do is put a condition on it. If the range does not contain “ABCD” anywhere, I want the cell to display “Nothing” but if the range find “ABCD” I want to display the SUMIF figure minus another value.

The logic reads something like:
If my range does contain “ABCD” then display the text “Nothing”. If the range does have one or more instances of “ABCD” then display the SUMIF figure minus 50

If tried the search function, if function and various other formulas but I am not getting there.
Any ideas? A virtual pint and cyber high five to anyone who can help.

[Alex]

=IF(ISNA(MATCH("ABCD",G2:G500,0)),"Nothing",SUMIF(G2:G500,"ABCD",E2:E500))

[toon10]

Alex said:

=IF(ISNA(MATCH("ABCD",G2:G500,0)),"Nothing",SUMIF(G2:G500,"ABCD",E2:E500))

Wow, that was quick! Works a treat. I've not come across the ISNA function before. I tried Google but wasn't really getting anywhere. I knew the PH smart Alex's would come to my rescue ;-)

Thanks, much appreciated.