# SUBTOTAL with OFFSET TO COUNT

#### pugazh

##### New Member
<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.

##### MrExcel MVP
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
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.

##### MrExcel MVP
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

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
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"

##### MrExcel MVP

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
[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?

##### MrExcel MVP
[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?

Replies
2
Views
41
Replies
5
Views
156
Replies
6
Views
121
Replies
5
Views
142
Replies
1
Views
103

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.

### Which adblocker are you using?

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

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