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.
=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
- 01"*Pro*" is the criteria. Each * stands for any number of characters, including none, so the text can have anything before and after Pro.
- 02SUMIF checks every cell in $A$2:$A$500 against that pattern, without regard to upper or lower case.
- 03For each match it takes the value on the same row of $C$2:$C$500.
- 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
=SUMIF($A$2:$A$500,"*Pro*",$C$2:$C$500)=SUMIF($A$2:$A$500,"*Pro*",$C$2:$C$500)-- PostgreSQL. ILIKE ignores case. Elsewhere use LOWER(plan) LIKE '%pro%'.
SELECT COALESCE(SUM(mrr), 0) AS total_mrr
FROM subscriptions
WHERE plan ILIKE '%Pro%';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.