How to sumif adjacent cell is equal, blank or containing text in Excel?
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.
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").
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)
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.
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.
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.
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
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!