This guide will explain how to sum if not blank in Excel using the SUMIF function.
Since it has several built-in functions and tools, Excel is a popular tool for different situations and purposes. For instance, Excel allows us to perform calculations based on certain criteria or conditions.
Thus, we can specifically choose or pick out certain values to be included in the calculation. And this makes it efficient when dealing with data sets with incomplete data or values.
In this guide, we will focus on learning how to sum if not blank in Excel. Basically, we want to get the total sum of values that belong to cells that are not blank.
Luckily, Excel has a built-in function that we can utilize to perform this exact task. So we will be using the SUMIF function to get the sum of if not blank cells.
Let’s take a sample scenario wherein we need to sum if not blank cells in Excel.
Suppose we are creating a monthly sales report. So we want to get the total amount of products sold for this month. However, certain products have incomplete information, such as the receipt. Thus, we want only to get the sum of the products that have the receipt, excluding the blank cells.
To do this, we utilize the SUMIF function and the not equal to logical operator. With this, we can get the sum of only the products with complete information.
Before we move on to a real example of summing, if not blank in Excel, let’s first understand how to use the SUMIF function in Excel.
The Anatomy of the SUMIF Function
The syntax or the way we write the SUMIF function is as follows:
=SUMIF(range, criteria, [sum_range])
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we begin any function in Excel.
- SUMIF() is our
SUMIFfunction. And this is used to add the cells specified by a given single criterion or condition. - range is a required argument. So this refers to the range of cells we want to evaluate for the given condition or criteria.
- criteria is another required argument. And this refers to the condition or criteria we set which can be in the form of a number, expression, or text value that will define the cells to be added.
- sum_range is an optional argument. The sum_range value refers to the actual cells to be summed.
Note: If we want to input more than one condition or criteria, we must use the SUMIFS function.
Great! Now we can move on and dive into a real example of summing if not blank in Excel.
A Real Example of Summing Not Blank in Excel
Let’s say we have a data set showing the sales report for this month. Firstly, we have a column containing the different products. Secondly, we have the total amount sold for each product. Then, we have a column displaying if a receipt for the product has been submitted. So our initial data set would look like this:
Moreover, our goal is to get the sum of the products that have submitted a receipt. Essentially, we are referring to getting the sum for the products that do not have a blank cell in the receipt column.
To do this, we will utilize the SUMIF function. So the SUMIF function is often used to return the sum of cells that meet the single condition or criteria we set. Additionally, the SUMIF function supports the use of wildcard characters such as the question mark and asterisk.
However, we will not be using any wildcard characters in our criteria. Since we want to sum non-blank cells, we will use logical operators in our criteria. And there are six types of logical characters in Excel.
Here is a table to easily remember the logical operators in Excel.
| Logical Operator Symbol | Meaning |
| > | More than |
| < | Less than |
| = | Equal to |
| <> | Not equal to |
| >= | More than or equal to |
| <= | Less than or equal to |
In this case, we will utilize the not equal to (<>) symbol in our formula to sum if not blank in Excel. So we will input the not equal to a logical operator in our SUMIF function.
For instance, we want to get the total amount for the products with complete information or submitted a receipt. In our data set, the products that did not submit a receipt are left empty or blank in the column. Thus, we want to exclude the products with blank cells in our calculation.
To interpret blank cells in Excel, we simply input double quotation marks without any space in between, like “”. Since we want to sum not blank cells in our data set, we simply add the not equal sign in our criteria, such as “<>” meaning not equal to blank.
Furthermore, we will select the receipt column as the range we want to evaluate. Hence, the formula will check for the cells that match the criteria of not equal to blank. Then, we will select the column containing the amount for each product as the range to be added.
So the formula will add the cells in the amount column that match the criteria of not being blank in the receipt column. Thus, our final data set would look like this:
You can make your own copy of the spreadsheet above using the link attached below.
Amazing! Now we can dive into the steps of how to sum if not blank in Excel.
How to Sum If Not Blank in Excel
In this section, we will explain the step-by-step process of how to sum if not blank in Excel. Furthermore, each step contains pictures and detailed instructions to guide you along the way.
1. Firstly, we will create a new location to input the result then we can proceed to sum the cells that are not blank in the data set. To do this, we can type in the formula “=SUMIF(D2:D8,”<>”,C2:C8)”. Lastly, we will press the Enter key to return the result.
2. And tada! We have successfully summed if not blank in Excel.
And that’s pretty much it! We have successfully explained how to sum if not blank in Excel using the SUMIF function. Now you learn this method and apply it to your work whenever you need to sum only specific values in your data set, such as cells that are not blank.
Are you interested in learning more about what Excel can do? You can now use the SUMIF function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
