0

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
1

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.