SUBTOTAL with OFFSET TO COUNT

[pugazh]

<article>

Formula below does not return any value? What is wrong with this formula?
=SUMPRODUCT(SUBTOTAL(103,OFFSET(AZ8:AZ488,ROW(AZ8:AZ488)-ROW(AZ8),0,1)),--(AZ8:AZ488<>""))

Requirement is count all cells which has text or numbers and leave the blank cells from counting. Each cell in the range AZ8:AZ488 has the following formula
=IF(COUNTA(AT9:AY9),INDEX({"NP";"D2";"D3";"OK"},AGGREGATE(15,6,MATCH(AT9:AY9,{"NP";"D2";"D3";"OK"},0),1)),"")​

</article>

 

[Aladin Akyurek]

Nothing wrong with:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(AZ8:AZ488,ROW(AZ8:AZ488)-ROW(AZ8),0,1)),--(AZ8:AZ488<>""))


What do you get with the following?

=COUNTIFS(AZ8:AZ488,"?*")

 

[pugazh]

I get number 218. This is total count of text/number in that range. But i need with filters. If filter 218 should show filtered value.

 

[Aladin Akyurek]

i get number 218. This is total count of text/number in that range. But i need with filters. If filter 218 should show filtered value.

Does this deliver anything useful?

=sumproduct(subtotal(103,offset(az8,row(az8:az488)-row(az8),0,1)),--(len(az8:az488)>0))

 

[Fluff]

Cross posted https://chandoo.org/forum/threads/subtotal-with-offset.40722/

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[pugazh]

No. This formula returns wrong value 60 instead of 218. Entry in each cell will either be blank or one of these"NP";"D2";"D3";"OK"

 

[Aladin Akyurek]

No. This formula returns wrong value 60 instead of 218. Entry in each cell will either be blank or one of these"NP";"D2";"D3";"OK"

Are you saying that

=sumproduct(subtotal(103,offset(az8,row(az8:az488)-row(az8),0,1)),--(len(az8:az488)>0))

returns 60 while you apply a filter instead of 218?

I must say I don't follow: You have also said:

=COUNTIFS(AZ8:AZ488,"?*")

returns 218. Note that this is insensitive to filters.

To recap: We have 218 non-blank values. What do you expect when filtered?

 

[pugazh]

[a] Total no of entries is 218 in cell range AZ8:AZ488.
=COUNTIFS(AZ8:AZ488,"?*") formula works. This formula returns value of 218. 2 issues - (1) this does not work when filter is applied. Means shows same value of 218 when filter is applied. (2) this formula does not count numbers.
[3]=sumproduct(subtotal(103,offset(az8,row(az8:az488)-row(az8),0,1)),--(len(az8:az488)>0)) - returns value of 60 instead of 218. Something wrong in this formula.
[4] how can i upload the sample file for you to check?

 

[Aladin Akyurek]

[a] Total no of entries is 218 in cell range AZ8:AZ488.
=COUNTIFS(AZ8:AZ488,"?*") formula works. This formula returns value of 218. 2 issues - (1) this does not work when filter is applied. Means shows same value of 218 when filter is applied. (2) this formula does not count numbers.

As I said, the SUBTOTAL formula is correct, but you get apparently a 0 count.


The COUNTIFS formula counts text entries and is not (auto)filter sensitive. I asked to apply that to the range of interest in order to see whether you have any text value in that range at all.


If you have text as well as numbers in the target range...

=SUMPRODUCT(LEN(AZ8:Az488)>0)+0)

will return a count of text and numeric values in that range, irrespective of filter.

[3]

=sumproduct(subtotal(103,offset(az8,row(az8:az488)-row(az8),0,1)),--(len(az8:az488)>0)) - returns value of 60 instead of 218. Something wrong in this formula.

What value are you expecting instead of 60 under the filter which you have applied?[/quote]

[4] how can i upload the sample file for you to check?

See: https://www.mrexcel.com/forum/about-board/508133-attachments.html