Skip to main content

How to sumif adjacent cell is equal, blank or containing text in Excel?

Author Siluvia Last modified

When working with Microsoft Excel, there are many instances where you may need to sum the values in one column, but only if the adjacent cell in another column meets a certain condition. These conditions could include the adjacent cell being equal to a specific value (such as a product name or code), being blank, or containing any text. Accurately performing these types of conditional sums can help you quickly analyze sales figures, inventory, attendance, or other datasets where related information is spread across columns.

This tutorial offers practical, step-by-step formula solutions to meet these needs, explains their application scenarios, and offers tips to ensure accurate results.

Sumif adjacent cell equals a criterion in Excel
Sumif adjacent cell is blank in Excel
Sumif adjacent cell containing text in Excel


Sumif adjacent cell equals a criterion in Excel

As the screenshot below shows, imagine you have several products listed alongside their prices in a table. If you need to sum all prices for "Beef", you can accomplish this efficiently using a formula. This approach is very practical for sales reporting or expense tracking when you frequently need to total values tied to a specific label or category.

a screenshot of the original data range

1. Select the cell where you want the sum result to appear.

2. Copy and paste the following formula into the Formula Bar:

=SUM(IF(A4:E10=A13, B4:F10,0))

After entering the formula, press Ctrl + Shift + Enter at the same time to create an array formula. Excel will calculate the sum of prices only for the product listed in cell A13 (e.g., "Beef").

a screenshot of using formula to sum values based on adjacent cells that are equal a criterion

Parameters explained: In this formula, A4:E10 is the range with your category or criteria, A13 is the cell containing the criteria you want to match, and B4:F10 is the range containing the values you wish to sum. Adjust these references as needed to fit your table’s layout.

Tips: Double-check for extra spaces or inconsistencies in your criteria to avoid incorrect results. If your data is not organized in a block (e.g., not multi-column), you can adjust the formula for single column ranges.

Applicability: This solution is suitable for moderate-sized datasets where you want a quick, criteria-based total and no advanced logic is needed. For changing criteria, simply update the value in A13.

Potential issues: If you forget to use Ctrl + Shift + Enter (and are not using newer Excel versions that support dynamic arrays), you may receive a #VALUE! error or incorrect results.


Easily combine duplicates in a column and sum values in another column based on the duplicates in Excel

The Kutools for Excel's Advanced Combine Rows utility helps you quickly combine duplicate rows in a column and calculate or combine values in another column based on the duplicates in Excel. This is especially valuable when cleaning up and summarizing large tables, and requires minimal manual adjustment for multiple scenarios.
Download and try it now! (30-day free trail)

a screenshot of using Kutools for Excel to combine duplicates in a column while summing corresponding values in another column


Sumif adjacent cell is blank in Excel

In many Excel sheets, it’s common to encounter rows where some cells are left empty because data was not recorded or not applicable. If you only want to sum the values where the adjacent cell is blank, such as totaling items with missing descriptions or unassigned categories, apply this formula solution.

a screenshot of the original data range with blank cells in the adjacent column

1. Select a blank cell for the result display.

2. Copy the following formula into the Formula Bar (adjust the ranges to fit your actual data):

=SUM(IF(ISBLANK(B2:B7),A2:A7,0))

Press Ctrl + Shift + Enter to apply as an array formula. The sum will update to include only the values whose adjacent (corresponding) cell is empty.

a screenshot of using formula to sum values based on adjacent cells that are blank

Now, the result cell shows the sum of values where the corresponding cell in column B is blank.

Parameters explained: B2:B7 refers to the range with potential blank cells, and A2:A7 is the range of values to sum.

Practical advice: Before applying the formula, check for hidden spaces or non-visible characters in your “blank” cells. Cells that appear empty but contain space characters will not be counted as blank, causing the formula to skip them.

Applicability: This is suitable for inventory checks, attendance records, or financial sheets where blank data points need to be highlighted and quantified.


Sumif adjacent cell containing text in Excel

Sometimes you need to total only those values where the adjacent cell contains any kind of text. This can be useful in situations where you want to ignore empty or numeric values beside your target data, for example, summing order amounts where the “Remarks” column is non-empty or where a label is present in the neighboring cell.

Continuing with the previous example, apply this approach:

1. Select a blank cell for the sum output, then copy and paste this formula into the Formula Bar:

=SUMIF(B2:B7,"<>"&"",A2:A7)

After entering, press Ctrl + Shift + Enter if required by your Excel version. The formula will sum only those values in A2:A7 where the adjacent cell in B2:B7 contains text (i.e., is not blank).

The result will display in the cell you chose, providing a real-time total of values with adjacent text entries.

a screenshot of using formula to sum values based on adjacent cells that contain text

Parameters explained: B2:B7 is the reference range for the adjacent text condition, and A2:A7 is the values to total.

Tips: This method ignores adjacent cells that are completely blank. If some cells appear empty but actually contain invisible text or formulas, they may still be included, so inspect your data for consistency.

Usage scenario: This is best suited for operational or summary reports with optional notes/descriptions, or to analyze participation or status fields that are marked textually.


Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!