# 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

## 2 Answers

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)
``````

If I read your question correctly, this is an accounting application where you are relying on the word "RECEIVED" to treat the amount as a credit. If that is correct, you would need to add an expression to Raystafarian's solution:

``````    =SUMIF(B5:B10,"<>"&"RECEIVED",A5:A10)-SUMIF(B5:B10,"="&"RECEIVED",A5:A10)
``````

Note that this relies on a perfect match in spelling and capitalization of the word "RECEIVED". A typo or use of lower case won't match. The reliable way to do this is to make received amounts negative values and just add all of the values without the need for fancy logic.