Countif on Formula Column

MatR · 06/20/19

Can someone tell me what I'm doing wrong?

I want to count all entries in a column that say 'Exceeds'. The formula I have in place is this:

=COUNTIF([Q4 Performance]:[Q4 Performance], "Exceeds")

The answer it is returning is the '#Invalid Operation' error.

The only thing I can think of is that the entries in [Q4 Performance] are formula results, i.e. if a different column is over a certain figure then [Q4 Performance] says Exceeds, if it is under that figure it says Not Achieved. Could this be the problem? If it is, how do I count entries in this column?

Andrée Starå · 06/20/19

Hi Mat,

At a glance, it should work.

Can you maybe share the formula and some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

MatR · 06/20/19

Hi Andrée

So, The image labelled Table Example shows which column I'm trying to count, and the column titled Q1 to the right hand side contains the formula and is showing the error.

The image labelled Formula Example shows the formula in column Q4 Performance that generates the 'Exceeds' result, whilst image Formula Example 2 shows the non-working formula in-situ.

I oversimplified the formula in the Q4 Performance in my original post because I had it confused with a different formula elsewhere on the sheet. Essentially the formula in Q4 Performance is checking a few other columns: Q4 Override, Tolerance Type, Q4 Percentage, and Q4 Number.

But, as far as I can tell, the CountIf formula should still work.

Formula Example.PNG

Table example 1.PNG

Formula Example 2.PNG

MatR · 06/20/19

Andrée

Thanks as always for your help, but you can ignore me because I'm an idiot.

I started playing around with the range and discovered that it worked provided I didn't go any higher than row 79. Row 80 caused the error message.

Turns out that on row 80 the formula in Q4 Performance is displaying an error which, in turn, is affecting the CountIf formula.

I checked everything except all of the entries in Q4 Performance.

Apologies for the mistake. I obviously need more coffee.

Paul Newcome · 06/20/19

Happens to all of us. Haha.

Were you able to correct the error in row 80 so that you can continue referencing the entire column in your COUNTIF function?

Andrée Starå · 06/20/19

I'm always happy to help!

No worries! It happens to us all. It's easy to miss.

Best,

Andrée

MatR · 06/21/19

I was, thanks.

It was a user input error (not mine for once). A text entry elsewhere in the sheet that I hadn't accounted for in my string of formulas.

Still, it's all a learning experience.

Paul Newcome · 06/21/19

Happens to me more than I care to admit. Lol

Ken Morrill · 09/10/20

Debugging why COUNTIF to determine number of values of in a column of computed values is failing.

Final SKU is a computed Column. These are Rows 1 and 2 of the sheet.

Debugging formula on Row 1 is: =COUNTIF([Final SKU]:[Final SKU], "592810")

Debugging formula on Row 2 is: =COUNTIF([Final SKU]:[Final SKU], [Final SKU]1) --> which is the desired formula.

Ultimately, I need to test that no [Final SKU] has a Count greater than 1.

Quick add: There are no rows in [Final SKU] that have invalid results.

Countif on Formula Column

Comments

Help Article Resources

Categories