SUBTOTAL with OFFSET TO COUNT

pugazh

New Member
Joined
Feb 21, 2010
Messages
11
<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>


 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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

New Member
Joined
Feb 21, 2010
Messages
11
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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))
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,677
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 21, 2010
Messages
11
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

ADVERTISEMENT

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

New Member
Joined
Feb 21, 2010
Messages
11
[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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
[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.

=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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,820
Messages
5,638,529
Members
417,032
Latest member
Qasdrf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top