In a typical Google Sheets setup, we usually store text in one column and numbers in another. That makes it easy to apply formulas like SUMIF, SUMIFS, or even a simple SUM. But what if your data is a bit messy — with both text and numbers in the same column?

That’s where things get tricky. The regular SUMIF function won’t work as expected. But don’t worry — with a little formatting magic, you can still use SUMIF in a text and number column in Google Sheets.

Let me show you how.

Scenario 1: A Single Column with Text and Numbers

Let’s say column A contains the following values:

You want to sum the total for “Tuesday”, which would be 40 + 20 = 60.

If you try to apply a regular SUMIF to this, it won’t work — because the values aren’t split into clean lookup and number columns. So, let’s break it down step by step.

Step-by-Step: SUMIF in a Text and Number Column

The key here is data transformation. We’ll format the text properly before applying the SUMIF formula.

Step 1: Join the Values with a Consistent Delimiter

We’ll use TEXTJOIN to merge all the values from column A, using "+" as a delimiter (since one row already uses it).

=TEXTJOIN("+", TRUE, A1:A)

Output:
Sunday 25+Monday 30 + Tuesday 40+Tuesday 20

Step 2: Split the Merged Text at the + Sign

=SPLIT(TEXTJOIN("+", TRUE, A1:A), "+")

Output:

Sunday 25  
Monday 30  
Tuesday 40  
Tuesday 20

Step 3: Flatten the Result into a Column

=TOCOL(SPLIT(TEXTJOIN("+", TRUE, A1:A), "+"))

This makes sure the values are listed vertically — useful for the next step.

Step 4: Separate the Text and Number Parts

Now split each line into two parts — the label (like “Tuesday”) and the value (like 20):

=ArrayFormula(SPLIT(TOCOL(SPLIT(TEXTJOIN("+", TRUE, A1:A), "+")), " "))

Output (in two columns):

Step 5: Apply SUMIF

Now that the data is formatted correctly, you can apply a standard SUMIF:

=SUMIF(C1:C, "Tuesday", D1:D)

Result: 60

And that’s how you can use SUMIF in a text and number column in Google Sheets — once you reformat the data properly.

Scenario 2: A Text + Number Column and a Separate Number Column

Now consider a more realistic dataset where:

• Column A contains text or text+number
• Column B contains numbers (but should only be used when Column A has no number)

Here’s the trick: we want to ignore the values in column B when column A already contains numbers.

We’ll use a formula that combines both columns:

=ArrayFormula(SPLIT(TOCOL(SPLIT(TEXTJOIN("+", TRUE, TRIM(A1:A & " "&B1:B)), "+")), " "))

This will give you a three-column output. For the SUMIF, you’ll only need the first column (labels) and second column (numbers).

Notice how the value 70 from column B is effectively ignored in row 2 — because numbers already existed in column A (30 and 40).

Now apply SUMIF:

=SUMIF(D1:D, "Tuesday", E1:E)

Result: 60

FAQs

Why doesn’t SUMIF work directly with text and number mixed columns?

SUMIF needs a clear criteria range and a numeric sum range. When your numbers are embedded in text (like "Tuesday 40"), Google Sheets treats them as plain text — and won’t sum them.

Can I use this with partial matches (e.g., “Tues”)?

Yes, just update your SUMIF to use a wildcard:

=SUMIF(D1:D, "Tues*", E1:E)

Or use SEARCH with FILTER or QUERY for more advanced needs.

Can this approach handle multiple keywords?

For multiple criteria like "Tuesday" and "Monday", you can use SUMIFS or a combination of FILTER and SUM:

=SUM(FILTER(E1:E, (D1:D="Tuesday") + (D1:D="Monday")))

Conclusion

When your data isn’t in a clean tabular format — especially when text and numbers are in the same column — standard functions like SUMIF won’t behave as expected.

But with a little creativity and smart use of functions like TEXTJOIN, SPLIT, TOCOL, and ARRAYFORMULA, you can use SUMIF in a text and number column in Google Sheets just as effectively.

Resources

• Extract All Numbers from Text and Sum Them in Google Sheets
• Sum Cells With Numbers and Text in a Column in Google Sheets
• SUMIF Excluding Hidden Rows in Google Sheets
• How to Perform a Case-Sensitive SUMIF in Google Sheets
• How to Use Dynamic Ranges in SUMIF Formula in Google Sheets
• Multiple Sum Columns in SUMIF in Google Sheets
• Include Adjacent Blank Cells in SUMIF Range in Google Sheets
• Sum of Matrix Rows or Columns Using SUMIF in Google Sheets
• SUMIF with ArrayFormula in Filtered Data in Google Sheets
• How to Use SUMIF in Merged Cells in Google Sheets
• SUMIF Across Multiple Sheets in Google Sheets
• Dynamic Sum Column in SUMIF in Google Sheets