Fixing Excel SUMIFS That Return Zero When Criteria Reference Another Sheet

You've written a SUMIFS formula, tested it on a single sheet, and it works perfectly. Then you move one of the criteria ranges to another tab and the result flips to zero. The data is there, the formula looks right — nothing obviously broken.

This is one of the most frustrating Excel gotchas because the formula doesn't throw an error. It just silently returns zero and leaves you second-guessing your data.

What you'll learn

• Why SUMIFS silently returns zero instead of an error when cross-sheet criteria go wrong
• The most common causes: range size mismatches, data type mismatches, and volatile reference traps
• How to fix each cause with concrete formula examples
• When to use INDIRECT, and when to avoid it
• A helper-column pattern that sidesteps the problem entirely

Prerequisites

These fixes apply to Excel 2016 and later, including Microsoft 365. The INDIRECT approach works in Google Sheets too, with minor syntax differences. You should be comfortable entering array formulas and know how to name a range.

How SUMIFS Actually Evaluates Criteria

Before fixing anything, it helps to understand what SUMIFS is doing under the hood. For each row in the sum range, Excel checks whether every criteria range/criteria pair evaluates to TRUE. If every condition matches, that row's value is added to the total. If any single condition fails for every row, the result is zero.

When a criteria range lives on another sheet, Excel still evaluates it row by row. The problem is usually that something about the reference, the data, or the range dimensions causes zero matches rather than the formula being fundamentally broken.

Cause 1: Criteria Range Sizes Don't Match

This is the most common cause, and it's invisible at a glance. SUMIFS requires every range argument — the sum range and every criteria range — to be exactly the same size and shape.

If your sum range is Sheet1!B2:B100 (99 rows) but your criteria range on Sheet2 is Sheet2!A2:A101 (100 rows), Excel returns zero without complaint.

      

      
      
      

        Tags:
        
        #HowTo
        
        #Data Analytics
        
        #Excel
        
      
      

      
      

        

          
📤 Share this article
          
            
              
              Sign in to save
            
          
        
        

          
            𝕏 Share on X
          
          
            Facebook
          
          
            LinkedIn
          
          
            🔗 Copy Link
          
        
        
        

      

      
      
      

        
Related Articles
        

          
          
            
HowTo
            
Splitting a Delimited String into Multiple Rows in PostgreSQL
            
6m read
          
          
          
            
HowTo
            
Exporting Query Results to CSV in Postgres Using COPY and psql
            
6m read
          
          
          
            
HowTo
            
Merging Multiple Excel Sheets into One DataFrame with Pandas
            
6m read
          
          
        
      
      

      
      

        
Comments (0)

        

        
        
No comments yet. Be the first!
        

        
        

          
Leave a Comment
          

            
            

            
              

                
                
              
              
              
              

                Sign in
                to comment with your profile.
              
            

            
            

            
              Post Comment →