Google Sheet if value matches values in list

Question

My data looks like this.

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?

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. — doubleunary, 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. — Ganesh Satpute, Nov 2, 2021 at 9:11

doubleunary · Accepted Answer · 2021-11-02 09:25:06Z

2

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

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

answered Oct 31, 2021 at 9:51

doubleunary

6,7631 gold badge4 silver badges34 bronze badges

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
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().
– doubleunary
Nov 1, 2021 at 6:03
This helps me @doubleunary. Thanks for the answer!
– Ganesh Satpute
Nov 2, 2021 at 12:16

Add a comment |

1 Answer 1