Are you struggling to use the SUMIF function in a Google Sheets file that contains both text and numbers? Don’t worry, I’ve got you covered! In this article, I’ll explain how you can format your data and utilize the SUMIF formula to tackle this problem with ease.
Formatting the Data for SUMIF
Naturally, the SUMIF function won’t work with mixed data types. Therefore, the first step is to format the data correctly. Let’s take a look at how you can achieve that.
Scenario 1: Single Column Text and Number Values
In this scenario, you have a single column with both text and number values. Your goal is to sum the total of a specific text value. Let’s say you want to sum the total of “Tuesday” from the following table:
| A | |
|---|---|
| 1 | Tuesday |
| 2 | Monday |
| 3 | Tuesday |
To accomplish this, follow these steps:
1. Remove ‘+’ Sign Using Regexreplace
Use the following formula in cell B1 to remove the ‘+’ sign from the values:
=ArrayFormula(regexreplace(A1:A,"+",""))
If your separator is different, adjust the formula accordingly. If there is no separator, you can skip this step.
2. TextJoin to Combine Sumif Range
Now, let’s use the TextJoin function to combine the values. If you skipped step 1, simply use the original column (A1:A). The formula in cell B1 will be:
=textjoin("",1,ArrayFormula(regexreplace(A1:A,"+","")))
Here’s the output you’ll get:
Sunday 25
Monday 30
Tuesday 40
Tuesday 20Feel free to use the TextJoin and Regexreplace formula for the examples below.
3. Split Numbers and Text to Use in Sumif as Range and Sum Range
To solve the Sumif problem with text and numbers in Google Sheets, we’ll use a formula to split the values. Follow the steps mentioned in my Regex tutorial on “How to Split Number from Text When No Delimiter Present in Google Sheets”.
Apply the formula mentioned in that tutorial to split the value in cell B1. Then, transpose the result using the Transpose function. The formula will be:
=transpose(split(REGEXREPLACE(textjoin("",1,ArrayFormula(regexreplace(A1:A,"+",""))),"([0-9.]+)",",$1,"),","))
And voila! Your data is now split and ready to use in the SUMIF formula.
4. Move Text and Numbers to Separate Columns Using Filter
To finalize the process, we’ll filter the data and separate the text and numbers into different columns. Here’s how:
Filter Texts (E1):
=filter(trim(C1:C8),istext(C1:C8))
Replace the range/array C1:C8 with the formula from cell C1. The final formula will look like this:
=filter(trim(transpose(split(REGEXREPLACE(textjoin("",1,ArrayFormula(regexreplace(A1:A,"+",""))),"([0-9.]+)",",$1,"),","))),istext(transpose(split(REGEXREPLACE(textjoin("",1,ArrayFormula(regexreplace(A1:A,"+",""))),"([0-9.]+)",",$1,"),","))))
Filter Numbers (F1):
=filter(transpose(split(REGEXREPLACE(textjoin("",1,ArrayFormula(regexreplace(A1:A,"+",""))),"([0-9.]+)",",$1,"),",")),isnumber(transpose(split(REGEXREPLACE(textjoin("",1,ArrayFormula(regexreplace(A1:A,"+",""))),"([0-9.]+)",",$1,"),","))))
Now that your data is properly filtered, you can use the SUMIF formula as usual. Isn’t that cool?
Scenario 2: Multiple Column Text and Number Values
In this scenario, you have two columns with text and number values. To use SUMIF in such cases, we need to follow a few additional steps in the beginning. Here they are:
1. Filter Text that Contains Numbers
Use the following formula to extract texts that contain numbers:
=filter(A1:A,regexmatch(A1:A,"[0-9]+")=TRUE)
Replace the ‘+’ with a blank.
=regexreplace(filter(A1:A,regexmatch(A1:A,"[0-9]+")=TRUE),"+","")
2. Filter Text that Doesn’t Contain Numbers
The opposite of the previous formula, this one returns a two-column data:
=filter(A1:B,regexmatch(A1:A,"[0-9]+")=FALSE)
3. TextJoin Two Filters
Combine the two formulas using TextJoin:
=textjoin("",1,regexreplace(filter(A1:A,regexmatch(A1:A,"[0-9]+")=TRUE),"+",""),filter(A1:B,regexmatch(A1:A,"[0-9]+")=FALSE))
Now you’re ready to use SUMIF in a text and number column in Google Sheets. The remaining steps are the same as in Scenario 1, starting from point #3.
Feel free to explore and experiment with the SUMIF function to make the most out of your data!
That’s all you need to know about using the SUMIF formula in a text and number column in Google Sheets. Happy summing!
