After reading this article you will be able to sum numbers after excluding blank cells.
Adding numbers referencing to the next column where some cells are empty and remaining fills with text, number, dates, etc.
We have three columns, starting with Receipt numbers, Sales and Receiving date. Our task is to add all sales (column C) with the received date (column D) and ignoring cells that are being empty.
Formula to implement:
Download Your Example Excel File
- SUMIF – Excel categorizes SUMIF function under Math functions. This function adds all numbers in a range of cells that meet specific criteria. We can apply criteria to dates, numbers and text using logical operators (>, <, <>, =) and wildcards (*, ?) for partial matching.
Syntex =SUMIF (range, criteria, [sum_ range])
The SUMIF formula in Excel takes three arguments out of which one is optional. The function follows a format where a user needs to enter range and criteria while sum_range is optional.
Range: A lookup range to apply criteria.
Criteria: The criteria that must satisfy in the given Range.
Sum_ range [Optional]: The range of cells to add if it satisfies the condition.
Formula explanation (Step by Step)
You can also debug formula using shortcut F9 or Evaluate the formula step wise.
Let’s break the formula into multiple small steps to understand its application.
Step 1: IF(D3:D12,”<>”)
It will return all values that have dates (non-empty) from range D3 to D12 based on criteria <> (not equal) i.e. dates that are being shown and return ‘0‘ if cells are empty.
Integrate first formula in step 2
Step 2: SUMIF(D3:D12,”<>”,C3:C12)
(10606+ 10998 +10039+11509+11541)
Excel will only add those numbers that are with any values (Criteria) in the D column in the range C3 to C12.
You can also add all values that are with empty cells by just changing the criteria i.e. “”(empty) in the place of “<>”(not equal) by entering date.