Unusual Calculation

Hollie Green

Below is the formula I am using and it is giving me a value of 0 but should never give a value below 1. Any ideas on why it would give a 0?

=COUNTIFS([Oracle Id]:[Oracle Id], [Oracle Id]@row, Week:Week, Week@row, [Eval #]:[Eval #], [Eval #]@row)

Genevieve P.

@ker9 is correct - I've had this happen before with values that start with 0. Try using an @cell = in front of your criteria, this fixed it for me last time.

=COUNTIFS([Oracle Id]:[Oracle Id], [Oracle Id]@row, Week:Week, Week@row, [Eval #]:[Eval #], @cell = [Eval #]@row)

Kleerfyre

Its doing that because it is looking for something that is equal to all of the variables at the same time. If what you are looking for just needs to be equal to one of the variables, then you will have to break it up.

Hollie Green

But the criteria has to occur at least once

It's literally looking for each criteria that occurs in that particular row so it does always meet all of the criteria at least once in the sheet.

Paul Newcome

@Hollie Green I would suggest reaching out to Support. I agree with your reasoning that it should never output less than 1. One of the first things they will probably suggest is the "turn it off then back on again" approach which does often work for me whenever I run into something that is definitely unexpected behavior.

Remove the formula from the sheet completely.

Log out.

Clear your browser's cookies and cache.

Log back in.

Retype the formula.

Hollie Green

@Paul Newcome Thank you! I tried your suggestion and still giving the error. I opened a support ticket hopefully they can figure out what is causing it.

ker9

Hi @Hollie Green

I set it up and tested it exactly as you have it and it returned 1 (or more).

Just throwing these thoughts out:

Is it a column formula? Are there any hidden rows?

You can try retyping the data in one row, one column at a time to see if there is anything strange in the values.

If you total the CountIFs column, does it return >0?

Hollie Green

It is working correctly in most of the rows I have attempted retyping in some of the ones that is giving a 0 as a value and it hasn't fixed them. all of the columns are text/number. I originally thought it may be because some of the data in the columns are text and some are numbers but it is calculating some of both correctly. This one really has me stumped

ker9

@Hollie Green - could you provide a little snapshot of a row that is working and one that isn't?

Hollie Green

Below is a screenshot. In the screenshot I have some columns hid to hide private information but these are the columns that matter and it gives the same result regardless of if they are hidden or not

Hollie Green

One thing I just discovered is it is always where the eval # is 001 but it doesn't work incorrectly on all of them. As you can see one is giving the 1 calculation and 1 is giving a 0

Paul Newcome

Can you apply a filter to the sheet to only show rows where the calculation is equal to zero and provide that screenshot?

ker9

@Hollie Green - I can duplicate the "001" problem and with any value that begins with 0 (02, 002). Not sure how it is working for you on the "002" item. It does seem to behave a little strange when you make changes.

Genevieve P.

@ker9 is correct - I've had this happen before with values that start with 0. Try using an @cell = in front of your criteria, this fixed it for me last time.

=COUNTIFS([Oracle Id]:[Oracle Id], [Oracle Id]@row, Week:Week, Week@row, [Eval #]:[Eval #], @cell = [Eval #]@row)

ker9

@Genevieve P. - excellent solution!

@Hollie Green

Hollie Green

Thank you that resolved the issue!