=COUNTA Not Working

Copper Contributor

It was working but I wanted it to count 2 additional rows so I changed the range and it stopped working.

 

The original formula was =COUNTA(E4:E24)

 

I changed it to =COUNTA(E4:E26)

 

instead of giving me a value in the cell it just has the formula listed in the cell 

=COUNTA(E4:E26)
5 Replies

@John_Garland 

  • Make sure that the cell with the COUNTA formula is not formatted as Text, but as General.
  • Activate the Formulas tab of the ribbon. Make sure that the Show Formulas button in the Formula Auditing group is not highlighted.

@HansVogelaar 

Hello,

it is still problem and your solution is not related to maybe bug.

When I am using something like this:

 

=UNIQUE( FILTER( table[Column 1], (table[Column 1]<>"List") ) )

 

it properly display entries from my table different than List in Column 1.

After cover it with IFERROR to show me just "" if nothing exist we have:

 

=IFERROR( UNIQUE( FILTER( table[Column 1], (table[Column 1]<>"List") ) ), "" )

 

My table don't have anything so result is ""

Now, I pot it inside COUNTA as below:

 

=COUNTA( IFERROR( UNIQUE( FILTER( table[Column 1], (table[Column 1]<>"List") ) ), "" ) )

 

and result is always 1, because for COUNTA("") it is always 1... no sense :(

Can we fix it or maybe another way?

I know maybe my example is little bit complicated and there is simple way to count elements in range, table or any. Can you help please?

In this moment I just going around and checking IF first cell in Column 1 is empty, if it is return 0 if not - formula above :(

@Orghal 

Try this:

 

=LET(f, FILTER(table[Column 1], (table[Column 1]<>"List", ""), IF(f="", 0, COUNTA(UNIQUE(f))))

@HansVogelaar 

Hello Hans

thank you for it, little similar to my work around :(
So sad Micro$oft never doing thing professional, also in Excel.

It is so sad they never update this software and improve after users suggestions and many many posts in this area. Google sheets going to be much better and maybe someday Google release offical app as Excel. I am sure world switch into it. Online documents is not good for confidential solution, but maybe someday powerful Google make a good thing.

@Orghal 

As a comment, COUNTA() counts cells/array elements with any kind of information, includes error. That's in Excel, Google Sheets and other spreadsheet software.