Issues with the COUNTA function in Excel

Hello again

It seems you're having a lot of difficulty showing what you have in your file. Here is the second thread you opened on two different subjects without providing the appropriate information?

If you don't want to say more, there's no point in asking questions that are riddles.

No one here will take the time to go through solutions one by one hoping to eventually hit upon the right one.!!

So if your information is confidential, create a similar dummy file with fake information and share it.

Otherwise, expect to search on your own.

To be continued?

I didn't quite understand,

If I have 70 cells each containing a COUNTA function and as you said above, COUNTA counts the number of non-empty cells and when I have a cell with a function it is counted as non-empty. So logically, if I have 70 COUNTA cells, then I have 70 non-empty cells and not 891. ??

And which function could I use then? I've already used COUNT and SUMPRODUCT but the result remains the same.

Good evening
we don't quite understand either but we are looking.
You only have 70 cells with formulas in your range... and the others are really empty, meaning without results and without formulas?

But let me take one of your sentences:
And when I want to know how many cells contain the COUNTA function, I get a result like I mentioned above of 891, whereas I know very well that I have about 70 cells.

You don't want to count how many cells in the range contain the COUNTA function in a list where there would be other values, do you???

Looking forward to your reply.

Re the forum

The easiest way is to attach a file with the formulas used.
Please specify the expected result and in which cell.

To attach a file, you create a link using
https://www.cjoint.com/ or http://cijoint.fr/ and then
copy the link in your next message.

Mytå

So logically, if I have 70 COUNTA cells, then I have 70 non-empty cells and not 891. ??

If COUNTA finds 891, it counts 70 COUNTA cells + 821 other cells that are not empty but contain information.

If you have cells where you see nothing but are counted, press F2 then delete and enter and you will see your total will be modified.

That's exactly it. It's counting empty cells for me as well.

These empty cells come from the fact that my row numbers do not follow consecutively since they have been filtered. Why were they filtered? Because I only selected the hens that lay one egg in a week, so I have my hens from Monday, Tuesday, Wednesday, etc. This means that my rows have "gaps".
So I can have:
3375
3376
3377
Then suddenly jump to
4179
4180
4181

And in my formula, I have =COUNT(E3375:E4266)
So 4266-3375 = 891
Excel did not take into account that I have filtered rows.

Yes, the information is strictly confidential, so I can't elaborate on that.

I may have forgotten to mention one piece of information:
Since my column containing the COUNT formulas is filtered, my first column containing the number of each row is also filtered, and to be clearer, I can jump from row 1 to 5 if rows 2, 3, and 4 have been filtered out.

I apologize for being so discreet about my work, but I'm trying to be as clear as possible, and I thank you for helping me in any case.

To summarize my problem:

I have a column E containing the COUNT function on each row, such as:
=COUNT(D5620:D5620), which allows me to display a color based on the result of the cell.

And when I want to know how many cells contain the COUNT function, I get a result, as I said above, of 891, whereas I know that I have about 70 cells.

In response to the problem, Excel tells me "the formula refers to empty cells."
I ask it to show me which ones, and it shows me the first cell I selected, whereas this cell does indeed contain a function and a result, and is therefore not empty.

I don’t know if I provided enough information, what other information would you need?

Let's try to clarify then!
How many cells does the COUNTA formula contain, that's what matters! Are you sure there are only 70, or are there only 70 with a non-null value?
Please explain yourself as clearly as possible on the subject.
Is your column E equipped with row formulas? from row to row?
What is its exact purpose? If it is to detect that a cell that contains a formula does not yield a result, it does not work; a cell with a formula is certainly counted as 1 by COUNTA.

What is the exact formula that counts the COUNTA of column E and where is it located? And what is the result that should give you a counted value in your last COUNTA, which we assume is a summary?

Looking forward to your response.

I will give you a summary of my Excel workbook:

I have 2000 chickens that lay eggs over a week. And I want to sort out which ones lay every day, which only lay on Mondays, which only lay twice a week, etc.

My COUNTA cells in column K allow me to know which ones lay only once a week, 2 times this week, 3, 4, 5, 6, or 7 times this week.

Here’s an example of two cells that allow me to know that two of my chickens lay 1 egg per week. In the cell result, I therefore have 1.

=COUNTA(D5394:D5394)
=COUNTA(D5388:D5388)

I only want to calculate the number of chickens laying just one egg per week, so I only select my column containing the result 1.

My formula that allows me to calculate the total number of these cells is:
=COUNTA(E3375:E4266)

The result I have is 891, while the correct result should be 316.

Knowing that my formula =COUNTA(E3375:E4266) is on the same sheet as my 316 cells.

re
we will eventually understand each other but not just yet..
if you use the COUNT function to detect a value in a cell, it is not associated with the value of the cell, but with the fact that there is a value in it, whatever it may be!
so if you stretch the height of the field, it will count the number of times a value is placed in K but not the number of 1 or 2 or 3, etc...

let's try differently to see if we have understood correctly
fill column K with your number of eggs per row
try this formula:
=COUNTIF(K:K,1)
which will give you the number of times 1 appears in the column.
=COUNTIF(K:K,2) etc., to get the number of layers with 2 eggs, with 3 eggs, etc...

have a good omelet

best regards

PS in case you need to find something else, it is essential to understand first that the COUNT function does not give you the value of a cell, but the number of values it contains, and therefore necessarily 1 for each affected cell;

It's working, thanks!