Share via

SUMIF not calculating, giving sum as ZERO

Anonymous
2020-08-18T02:25:08+00:00

HI! I am having issue with SUMIF function. It is working fine on SOME cells, but not others. 

I have checked that all the cells in a column are formatted the same (all TEXT or all NUMBERS) hoping that would fix the issue. It did not. I'm at a loss as to the issue as I have more than triple checked the formula at this point. 

Here you can see cell C184 (and in C185) where the SUMIF formula is working and calculating perfectly:  formula is =SUMIF(B2:B181,"Director",C2:C181) and the value I change in subsequent cells is simply the text 'criteria' in quotes. 

Here, you can see that in C186 that the formula is NOT calculating, despite the formula being identical except for the change in the TEXT "criteria". I did the same in C185, and C189 and they all work perfectly. but this Cell, and two others, just Calculate every time to ZERO. 

I even started all over from scratching a new document to see if the corrected 'formating' (as text vs. number for example) of the cells was lingering in the cells or something. Nada. STILL did not work in random cells that I formulated. 

What am I missing? Can anyone help me out? I've searched online for hours and tried all the suggestions i have found, but nothing is working. I need to set up the entire sheet of columns for six rows with totals and need to fix this bug!

Thanks in advance for any advice. 

Tammy

Microsoft 365 and Office | Excel | For home | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2020-08-18T03:51:06+00:00

    Hi TammyMichelle,

    Also, check and remove any extra spaces before and after the text. It could be reason why your criteria and criteria range do not match.

    Hope it helps you sort this out. Please let me know if you have any further questions.

    Regards,

    John

    20 people found this answer helpful.
    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-08-18T05:38:34+00:00

    You are very much welcome TammyMichelle! I am glad that I was able to help you somehow on this issue.

    Have a great rest of the day! Stay safe and healthy too!

    John

    0 comments
  2. Anonymous
    2020-08-18T05:48:42+00:00

    Hi TammyMichelle,

    Just to let you know if you copy the cell with formula, the criteria would also change. Make sure to copy the "cell" and not just the "formula". Or if you would see the little box at the bottom right of the cell, all you have to do is to drag it down to fill the rest of the column with data. Please see below.

    Then, you would notice that the criteria also changes as it moves down.

    Please give it a try. : )

    Regards,

    John

    0 comments