How to count visible rows and exclude zero?

[honkin]

I have some code which counts the number of selections in a column, but I would like it to exclude 0. it uses subtotal 103 so as to only count visible cells

Here is the code

=SUBTOTAL(103,AA16:AA1048561)

So how can I make this formula exclude all 0 in the column? I tried this, but it was not correct =SUBTOTAL(103,AA16:AA1048561,"<>0")

cheers

 

[JEC]

For example

=SUBTOTAL(103;A2:A10)-COUNTIF(A2:A10;0)

 

[Alex Blakenburg]

Try this:-
(You will need to extend the range)

=SUMPRODUCT(SUBTOTAL(103,OFFSET(AA16,ROW($AA$16:$AA$23)-ROW($AA$16),0)),--($AA$16:$AA$23<>0))

 

[honkin]

For example

=SUBTOTAL(103;A2:A10)-COUNTIF(A2:A10;0)

cheers JEC, but that does not seem to work, at least not on a Mac. It generates the standard "there's a problem with this formula" message with the 0 appearing to be highlighted in red

 

[honkin]

Try this:-
(You will need to extend the range)

=SUMPRODUCT(SUBTOTAL(103,OFFSET(AA16,ROW($AA$16:$AA$23)-ROW($AA$16),0)),--($AA$16:$AA$23<>0))

That appears to be perfect, Alex. Just to double check, I hid all the cells in the column which had 0 and the answer matches when it is not filtered, so spot on.

Thanks so much

 

[JEC]

Ah try to replace the ";" by comma's. I use different region settings

 

[honkin]

Ah try to replace the ";" by comma's. I use different region settings

ah, looks like 2 ways to skin a cat JEC. Yes, changing to commas worked a treat, with the answer matching that from Alex's code. Unfortunately I cannot choose both as a solution and had already clicked on the tick for Alex's post. I hope you understand, though I very much appreciate both responses.

cheers

 

[JEC]

No problem! goodluck

 

[Alex Blakenburg]

@JEC - FYI - the "-COUNTIF(A2:A10;0)" doesn't distinguish between filtered and unfiltered zeroes.
So if there are any zeroes in the hidden rows it will deduct more zeroes that it should.

 

[Alex Blakenburg]

@honkin - Thanks for the feedback. Glad we could help.