Excel SUMIF Not Blank

Sometimes we have a dataset that contains blank inputs. However, we have a corresponding value assigned to it. In such cases, we may want to exclude the cells that contain blank inputs and do the total. In such cases, use the criteria of the “SUMIF Not Blank.” An example could be to know the total number of passengers and exclude blank names criteria.

Examples

Example #1 – SUMIF Not Blank

The below set of data contains certain blanks in the range criteria. Suppose we still want to know the total no. of passengers:

Here now, criteria range conditions completely change for both by city and month. Important to note the condition which we put here in the syntax for criteria:

  • <>” -> indicates the “NOT EQUAL TO” sign. It has to be in double inverted commas as the formula processes it as characters. When we use this, the formula sums up all the values that are not blanks and completely ignores the blank cells during summation.“-> indicates “Blanks.” Double inverted commas with no characters in them signify equal to blank. When we use this, it sums up all the values containing blanks and ignores cells containing some characters/values.

For the ease of understanding the final output of tables 1 and 2. Refer to the below explanation:

Example 2 – Using Function Argument

Point to Remember

  • The SUMIF Not Blank has limited use unless that dataset contains blank rows in the criteria range.

This article has been a guide to the SUMIF Not Blank. Here, we discuss how to add cells based on blank / not blank criteria using SUMIF, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Select cell F2(active cell), where we need SUMIF() value as shown below: Click on the “Formulas” tab and select “Math & Trig.” A dropdown appears with various mathematical functions in Excel. For example, click “SUMIF”(highlighted in red). A dialog box appears, as shown below. The next steps would be to input corresponding respective cell references or values: Here, we will insert the cell reference of the “City” column in “Range” input, “City name/ cell reference” in “Criteria” input, and “No. of Passenger” in “Sum_range.” Go to the “Range” section in the dialog box, click in the blank space, select range starting from A2, and drag up to A17. Next, select all the row ranges in “City” column B. Then, go to the “Criteria” section and select the “City” cell reference, cell E2. Similarly, go to the “Sum_range” section in “Column C” and choose from C2 to C17. Click on “OK” and see the value in cell E2.

Here, we will insert the cell reference of the “City” column in “Range” input, “City name/ cell reference” in “Criteria” input, and “No. of Passenger” in “Sum_range.”

  • Sumif Text in ExcelSumif Text In ExcelSUMIF function is a conditional IF function to sum the cells based on specific criteria. For example, to add a group of cells, if the cell adjacent to them has a specified text in them, then function is used as follows =SUMIF(Text Range,” Text”, cells range for sum).read moreExcel Shortcut for SUMExamples of COUNTIFExcel SUMIF with Multiple CriteriaExcel SUMIF With Multiple CriteriaThe SUMIF (SUM+IF) with multiple criteria sums the cell values based on the conditions provided. The criteria are based on dates, numbers, and text. The SUMIF function works with a single criterion, while the SUMIFS function works with multiple criteria in excel.read more