# Excel Formula to deduct the amount of a text

I want a formula in excel.10 that I have some amounts in Column "A" and their comments in column "B". If I write the text "RECEIVED" in Cell B5 so it automatically deduct the amount of cell "A5" from the sum of A:A, for example

i have some amounts in column A5:A10. and Remarks in column B5:B10. Sum of A5:A10 is in A11. And i write a comment "RECEIVED" in cell B6, so formula will deduct the value of Cell A6 from "A11 (sum of A5:A10) and gives me the result in Cell B11 that your remaining Balance is this.

• What did you try so far? – Hennes Nov 6 '14 at 8:00
• Does the word "RECEIVED" indicate that the associated amount in col A should be excluded from the total or subtracted from the total? (Normally, accounting-type applications make the amounts, themselves, positive or negative depending on whether it is an amount owed or received.) – fixer1234 Nov 6 '14 at 16:16

You could, for instance, put this in cell `A11` for sum of range `A5:A10` where `B5:B10` is not "received".
``````=SUMIF(B5:B10,"<>"&"received",A5:A10)
``````    =SUMIF(B5:B10,"<>"&"RECEIVED",A5:A10)-SUMIF(B5:B10,"="&"RECEIVED",A5:A10)