SheetSense

SUMIF with a wildcard for cells that contain text

SUMIF accepts the wildcard * in its criteria, so "*Pro*" adds up every row whose text contains Pro anywhere, not only rows that equal it exactly.

fx
=SUMIF($A$2:$A$500,"*Pro*",$C$2:$C$500)

The sheet: Subscriptions sheet: column A is Plan name, such as Pro Monthly, Pro Annual, Starter, or Team Pro. Column C is MRR. You want the total MRR for every plan with Pro in its name.

How it works, step by step

  1. 01"*Pro*" is the criteria. Each * stands for any number of characters, including none, so the text can have anything before and after Pro.
  2. 02SUMIF checks every cell in $A$2:$A$500 against that pattern, without regard to upper or lower case.
  3. 03For each match it takes the value on the same row of $C$2:$C$500.
  4. 04SUMIF adds those values and returns the total. Pro Monthly, Pro Annual, and Team Pro are included, and Starter is not.

The same logic in four tools

Excel
=SUMIF($A$2:$A$500,"*Pro*",$C$2:$C$500)
Google Sheets
=SUMIF($A$2:$A$500,"*Pro*",$C$2:$C$500)
SQL
-- PostgreSQL. ILIKE ignores case. Elsewhere use LOWER(plan) LIKE '%pro%'.
SELECT COALESCE(SUM(mrr), 0) AS total_mrr
FROM subscriptions
WHERE plan ILIKE '%Pro%';
Python (pandas)
import pandas as pd

mask = subs["plan"].str.contains("Pro", case=False, regex=False, na=False)
total = subs.loc[mask, "mrr"].sum()

Mistakes that break it

  • !"*Pro*" also matches words such as Product and Prospect, because it only looks for the letters. Use more specific text, or the pattern "Pro *" for plans that start with Pro and a space.
  • !To match a literal asterisk or question mark, put a tilde in front of it, for example "~*".
  • !Wildcards only work on text. A numeric column cannot be searched with * and returns 0.
  • !SQL LIKE is case-sensitive in PostgreSQL but not in MySQL by default, so the same pattern can give different totals in each database.

Common questions

How do I sum cells that contain certain text?
Use SUMIF with the text wrapped in asterisks, such as "*Pro*", or build it from a cell with "*"&E2&"*". With several conditions use SUMIFS.
Does SUMIF wildcard matching ignore case?
Yes. "*pro*" and "*PRO*" give the same total. pandas str.contains needs case=False to behave the same way.
How do I sum cells that start with a certain text?
Put the asterisk only at the end, "Pro*". To match the end of the text put it only at the start, "*Monthly".

Got a version of this with your own ranges?

Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.