Difference between the SUBTOTAL(103) and COUNTA?

Hi all,

I have a problem. In some cells A1:AD1 are the texts, the rest are empty.

1. If I mark A2: Z2 and introduce a formula

Please Login or Register  to view this content.

completed Ctrl + Shift + Enter in these cells have values ​​0/1

2. If I mark A3: Z3 and introduce a formula

Please Login or Register  to view this content.

completed Ctrl + Shift + Enter in these cells have the same value as in the case of using COUNTA in step 1.

3. If you have a formula for A4

Please Login or Register  to view this content.

in the A4 I have the result of "OK" or "" depends on the content A1:AD1

4. If you have a formula for A5

Please Login or Register  to view this content.

I have it in A5 #N/A (Ecel 2007) or ""

My question:
1. Why formula

Please Login or Register  to view this content.

It is correct (result = OK) and the formula

Please Login or Register  to view this content.

It is no longer correct (result = #N/A or "")?

After all, and in step 1 and step 2 are returned to the same horizontal arrays.

2. In this particular case, what feature SUBTOTAL decides that the SUBTOTAL formula is correct?

Sorry, I do not know English

Thank you.

Tomek

Thank you, gmr4evr1.

It's about SUBTOTAL(103) does not SUBTOTAL(102)
In addition, the results of A2:Z2 and A3:Z3 are correct. Why the formulas in A4 is correct and in A5 - incorrect

Tony Valko, I know how it works SUBTOTAL and COUNTA. In my case, there are no hidden cells.
Question: Why formula

Please Login or Register  to view this content.

It is correct and the formula

Please Login or Register  to view this content.

It is no longer correct?
since

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

They return the same array 26 elements

"... for each element of Column($A:$Z), so 26 results ..."
I know how 'array of results' is created. That's not what I'm asking
"COUNTA can't work with that array of results like subtotal can.
It can only create a single result."
This is not true. COUNTA also can create an 'array of results'.
--SUBTOTAL (...) Creates such a 'array of results'
--COUNTA(...) Also creates such a 'array of results' - see A2:Z2 in file Book1.xlsx

For now I know that the 'array of results' returned by SUBTOTAL may be an argument for SUMPRODUCT (SUM) and the 'array of results' returned by COUNTA can not be an argument for SUMPRODUCT (SUM). Just do not know why

"... for each element of Column($A:$Z), so 26 results ..."
I know how 'array of results' is created. That's not what I'm asking
"COUNTA can't work with that array of results like subtotal can.
It can only create a single result."
This is not true. COUNTA also can create an 'array of results'.
--SUBTOTAL (...) Creates such a 'array of results'
--COUNTA(...) Also creates such a 'array of results' - see A2:Z2 in file Book1.xlsx

For now I know that the 'array of results' returned by SUBTOTAL may be an argument for SUMPRODUCT (SUM) and the 'array of results' returned by COUNTA can not be an argument for SUMPRODUCT (SUM). Just do not know why

Originally Posted by hungt

This is not true. COUNTA also can create an 'array of results'.

That may be true, but that's not the point.

It's not that counta can't 'create' an array of results.
It's that Counta cannot USE the array of ranges created by the offset.

In that formula, it's OFFSET that is 'creating' the array of ranges.


In the example I wrote up in previous post
When it gets to this point
=IF(SUMPRODUCT(--(SUBTOTAL(103;{A$1:$A$5,$B$1:$B$5,$C$1:$C$5,$D$1:$D$5,$E$1:$E$5,etc,$Z$1:$Z$5})=5))=1;"OK";"")
It's the OFFSET function that 'created' that array of ranges, not subtotal, or counta.

Subtotal can then USE that array of ranges, Counta cannot.
Counta would consider that one big range, A1:Z5
As if you wrote COUNTA(A1:A5, B1:B5, C1:C5)

Subtotal is able to use each element in that array of ranges, so you get
{SUBTOTAL(103,A1:A5), SUBTOTAL(103,B1:B5), SUBTOTAL(103,C1:C5)}

It's that Counta cannot USE the array of ranges created by the offset
...
Subtotal can then USE that array of ranges, Counta cannot

You looked at A2:Z2? You can see that COUNTA also can use that array of ranges created by the offset

Yes I did, and counta does NOT use an array of ranges..

In A2:Z2, each formula evaluates only 1 range.

In A2, it's evaluateing A1:A5
In B2, it's evaluateing B1:B5
In C2, it's evaluateing C1:C5
etc..

And sorry, i've been reading the offset wrong, it's 5 columns not rows.
so A1:E1 then B1:F1 then C1:G1 etc..

The question isn't why subtotal DOES work in A3:Z3, or counta does work in A2:Z2.
It's why counta Doesn't work in this 'single' formula
=IF(SUMPRODUCT(--(COUNTA(OFFSET($A$1;;COLUMN($A:$Z)-1;;5))=5))=1;"OK";"")
Right?

In A2:Z2, you put in this array of formula
=--(COUNTA(OFFSET($A$1;;COLUMN($A:$Z)-1;;5))=5)
Here's how that gets processed when you press CTRL + SHIFT + ENTER
A2: =--(COUNTA(OFFSET($A$1;;COLUMN($A:$A)-1;;5))=5) Then =--(COUNTA(OFFSET($A$1;;1-1;;5))=5)
B2: =--(COUNTA(OFFSET($A$1;;COLUMN($B:$B)-1;;5))=5) Then =--(COUNTA(OFFSET($A$1;;2-1;;5))=5)
C2: =--(COUNTA(OFFSET($A$1;;COLUMN($C:$C)-1;;5))=5) Then =--(COUNTA(OFFSET($A$1;;3-1;;5))=5)
etc..
Then offset get's to it
A2: =--(COUNTA($A$1:$E$1)=5)
B2: =--(COUNTA($B$1:$F$1)=5)
C2: =--(COUNTA($C$1:$G$1)=5)

COUNTA never even looks at an array of ranges.


Do you see how that process is different from how the single formula in A5 calculates as I described in my first post #8 ?

Originally Posted by Jonmo1

Do you see how that process is different from how the single formula in A5 calculates as I described in my first post #8 ?

So the result returned by SUBTOTAL (...) is treated as an array of 26 values and the result returned by the COUNTA (...) is treated as 26 single values?
If so this is exactly what I mean

Thank you very much for your time and efforts loaded

Almost, but quite.

The 'meat' of the issue is happening before subtotal or counta even come into play.
It's OFFSET with the use of COLUMN(A:Z) that is 'creating' the array of ranges. like
{A$1:$E$1,$B$1:$F$1,$C$1:$G$1,$D$1:$H$1,$E$1:$I$1,etc,$Z$1:$AD$1}

Subtotal can process that the way you want, to get an array of 26 different results of the subtotal, 1 for A1:E1, 1 for B1:F1, 1 for C1:G1 etc..
Counta cannot do that.

Counta would see that like
=COUNTA(A1:E1,B1:F1,C1:G1)

Just to see, go ahead and put that exact formula in a cell, and put values in A1:G1 and see what happens.
Even if you entered it with CTRL + SHIFT + ENTER
It's not doing what you think it does.

Originally Posted by Jonmo1

Counta would see that like
=COUNTA(A1:E1,B1:F1,C1:G1)

Just to see, go ahead and put that exact formula in a cell, and put values in A1:G1 and see what happens.
Even if you entered it with CTRL + SHIFT + ENTER
It's not doing what you think it does.

I understand. Once again, thank you very much.

You're welcome.