Subtotal a COUNTIF Range with an AutoFilter

[Marhier]

Good day everyone.
I'm struggling to get my head around a bit of formula where I use a COUNTIF function, but have the result subtotal when a filter is applied.

I have a list of values in column V; my code currently just looks in that range and counts the amount of times the values are equal to or greater than "5":

=IFERROR(COUNTIF(V16:V1016,">5")+COUNTIF(V16:V1016,"5"),"")

I was wondering if someone could advise me on how to amend this formula so if an autofilter is applied, it then subtotals.
I feel is has something to do with SUMPRODUCT; I've looked on various forums and websites but struggling to apply anything to my code for it to work.

All help is greatly appreciated.
Thank you.
Kind regards
Marhier

 

[Marhier]

I'm making some progress.
I've got it to work, but when the code that looks for values greater than 5, it's counting all the cells in the rage that are blank as well.

Any idea how to stop it from doing that.

Code is now:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(V16:V1016,ROW(V16:V1016)-MIN(ROW(V16:V1016)),,1))*(V16:V1016>5))

 

[Marhier]

Fixed it!
I was using COUNTA (3), rather than COUNT (2) as the subtotal.
There's a formula in all of column V that returns blanks and the COUNTA was counting the cells with formulas in.
COUNT just looks a cells with numbers in

Code is now:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(V16:V1016,ROW(V16:V1016)-MIN(ROW(V16:V1016)),,1))*(V16:V1016>5))

 

[Marhier]

Nice one!
Thanks for your help Marhier!

 

[Marhier]

You're welcome mate, glad I could help!