My data looks like this.

1 1000 Credit Card
2 200 Debit Card
3 1000 Pay Later

I want to sum of column B entries where the corresponding value in C is either "Credit Card", "Pay Later" (May have more values in future).

How do I calculate total debt in the above data?

Sample Spreadsheet

  • It is unclear whether you want a single result or multiple row-by-row results. Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. Commented Oct 31, 2021 at 9:48
  • @doubleunary Apologies for that. I've changed the description of the question and added a simple spreadsheet as well. I hope that helps. Commented Nov 2, 2021 at 9:11
  • Edited the answer to match your new requirements. Commented Nov 2, 2021 at 9:25

1 Answer 1


Use filter() with regexmatch(), like this:

=sum( iferror( filter( C7:C, regexmatch(D7:D, "(?i)credit card|pay.?later") ) ) )

  • Just for clarification: The purpose of iferror(1/0), as I understand it, is that it returns a blank cell. An alternative would be to use '' to create an an empty string but this would be included in any counta function.
    – Tedinoz
    Commented Nov 1, 2021 at 2:23
  • Yes — iferror(1/0) gets a truly blank value, which is different from the zero-length text string "". Truly blank values do not consume storage space while zero-length text strings do. You can test cells for blankness with isblank(). Commented Nov 1, 2021 at 6:03
  • This helps me @doubleunary. Thanks for the answer! Commented Nov 2, 2021 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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