Get Hooked To Learn To Sum If a Cell Contains Text in Excel

Excel is a vast software with many features for users of all kinds, as it lets you organize your data in a much more manageable way. Among other things, it allows you to sum up numerical values based on corresponding cells with text. In fact, it is one of the most common scenarios for people who use Excel regularly.

However, it's tricky for some people to do Excel function sum if cell contains text in Excel, especially if they're beginners. Therefore, this article will teach you practical and effective ways to calculate the sum if cell contains Excel text. So, let’s get straight into the details.

Part 1. How to Sum If A Cell Contains Any Text? 

The first method we will discuss is about calculating the Excel sum if cell contains any text. What it means is that by using this method, we can calculate the numerical values inside the cells with that specific text. Excel cells either contain text or numbers and by using the SUM function, you calculate the numerical values of the cells that contain text.

It automatically ignores the cells without text and those with numerical values. This method is beneficial when dealing with a dataset with both text and numbers. 

The function used to Excel sum if a cell contains text is SUMIF. 

In this example, we'll check how many dresses are in our stock, using the following formula for the sum if cell contains text Excel:

=SUMIF(A2:A10,"*dress*",B2:B10)

Step 1: The Layout & Data 

Here, A2:A10 are text values, and B2:B10 are numbers to sum. Or, you can create a cell E1 and put it in your formula, like shown in this screenshot:

Step 2: See the Results 

We'll check how many blue dresses are in stock using the following formula for the sum if cell contains text Excel:

=SUMIFS(B2:B10, A2:A10,"*dress*",A2:A10,"*blue*")

Or, this formula:

=SUMIFS(B2:B10, A2:A10,"*"&E1&"*",A2:A10,"*"&E2&"*")

Part 2. How to Sum if a Cell Contains Specific Text? 

This method is proper when calculating the Excel sum if cell contains specific text and adding the associated numerical values. It means having a sum of numerical values corresponding to cells with a particular text. As a result, you can focus on relevant parts of the dataset and automatically ignore those that do not satisfy the text criteria. 

Calculating a sum if cell contains text Excel also uses the same SUMIF function above. It filters out the useless values and uses only those with the keyword attached to them.

Here's a basic syntax of the formula for this sum if cell contains text Excel:

SUMIF(range,"criteria or keyword," cell values to be added)

Step 1: The Data

Let's look at the following example containing the individual scores of 3 teams:

Suppose we want to calculate AKASH's score, but as we can see, two are in our data. So, our formula for calculating AKASH's score will look like this:

=SUMIF(A2:A6,"AKASH",C2:C6)

Step 2: Applying the Formula 

By applying the formula to the H2 column, our output will look like this:

Part 3. How to Sum if a Cell Contains Text in Another Cell? 

For Excel sum if cell contains text in another cell, you want to calculate a total sum when the text in one cell is also present in another cell. So, you're basically matching different cells to check if any of them contain the exact text, and if they do, you add their corresponding numerical values into the sum. Using this approach, you can leverage the relationship between different cells and have your desired sum if cell contains text Excel.

Step 1: The Data 

Let's consider the following example: we have to sum a list of orders in different colors.

If we want to work with the blue color, we'll use the following formula in cell F3:

=SUMIF(B3:B10,"*"&"Blue"&"*",C3:C10)

Step 2: Applying the Formula 

By using the above formula, we'll get the result numerical value of all cells that have the text "Blue" in column B as shown below:

You can also use the following alternative formula for this sum if cell contains text Excel after entering *Blue* in cell E4: 

=SUMIF(B3:B10,E4,C3:C10)

We use the following formula in cell F3:

=SUMIFS(C3:C10,B3:B10,"*"&"Blue"&"*")

And the following formula in cell F4: 

=SUMIFS(C3:C10,B3:B10,E9)

As you can see below, both formulae return the same results for sum if cell contains text Excel:

Ensure you understand that SUMIF is used when there is one criterion, while SUMIFS is useful when there are one or multiple criteria.

Part 4. Bonus Tip: Best PDF to Excel Converter 

PDF is undoubtedly a helpful format, but sometimes it just doesn't fulfill your requirements. In such cases, you need to convert it to other formats to get the most out of it. Data analysts dealing with large swathes of data on a PDF file do better by converting it to Excel, which offers better representation, editing, and sharing capabilities.

However, you need a quality PDF-to-Excel converter that is easy to use. One such example is UPDF, a well-regarded PDF-to-Excel converter that includes all the essential features. Let's discuss its prominent features first before understanding its conversion process.

1. A Universal Program 

One of the best aspects of UPDF is that it's a universal program that works well on all platforms, including Android, iOS, MacOS, and Windows. So, no matter what kind of user you're, UPDF is ready to serve you without compromising efficiency.

2. Highly Productive 

UPDF brings you state-of-the-art editing tools that you may not find elsewhere. From editing text to adding images and inserting links to incorporating other pages, UPDF provides you with all the avenues for making your files richer.

Regarding text, UPDF allows you to add or delete it how you like. Moreover, you can modify your text's size, font, and color as much as you want while keeping the original format intact.

3. Delightful Annotations 

Annotations are the hallmark of any useful PDF document. Therefore, UPDF brings you an incredible annotation experience with highlights, underlines, strikethroughs, sticky notes, stamps, and whatnot. Modifications like these make reviewing documents delightful and fulfilling, a considerable strength of UPDF.

Whether you're a student, a professional, or a casual reader, these annotations can come in really handy. Just highlight the necessary information, and you can access it instantly whenever required!

4. The OCR 

One of the most innovative and practical features of UPDF is OCR, which allows you to convert your PDF files to Excel, Word, PowerPoint, and other popular formats. This feature is a lifesaver; you can convert your PDF documents and explore your data in an entirely new manner.

If you want to edit a large dataset and share it with others, UPDF is at your service.

The Conversion Process 

Let's take a step-by-step look at how you can convert your PDF to Excel using UPDF:

Step 1: Download UPDF & Import the PDF File

Download UPDF on your device and launch it. A swift method is to drag and drop the PDF into the program. Or you can also click Open File to open PDF in the program.

Step 2: Choose the Output Format 

Click "Export PDF" at the right panel and choose "Excel."

Step 3: Export Your Excel File 

Click Export at the bottom of the pop-up window and select a suitable folder for saving your converted file.

Sometimes you need to deal with scanned PDF files, but the regular method doesn't work to scan its text. Here, OCR comes to your rescue if you follow these steps:

Step 1: Making Text Editable 

Click "Recognize Text Using OCR" on the right panel, which recognizes the text and converts it into an editable form.

Step 2: Choose Your Settings 

Select "Searchable PDF" and choose the layout, image resolution, and language.

Step 3: Perform OCR 

After finishing the above settings, click "Perform OCR" to make your file editable and searchable.

Step 4: Choose Your Format 

Click "Export PDF" on the right and choose "Excel."

Step 5: Export Your File 

Hit "Export" to convert your PDF file into Excel. Once the conversion process is done, you can open it on your computer. As you can see in the following example, the content of your file remains precisely the same, and you can edit it effortlessly.

Congratulations, you have successfully converted your PDF files into Excel and can now edit them however you want.

Conclusion 

Calculating the sum if cell contains text in Excel can be tricky for anyone, especially beginners with little experience using Excel. However, by following this comprehensive guide, you can do it quickly and efficiently.

If you have your data stuck in your PDF file that you want to edit, converting it to Excel using UPDF is pertinent. The latter gives you much freedom to tinker with your data, including sum calculations. No matter how many cells of texts or numerical values the data contains, Excel will keep pace with it. 

As described above, UPDF has all the important features of a PDF-to-Excel converter. With its simple UI, even beginners can master the conversion process. So, download UPDF today and play with your data like never before!