Thread: SubTotal function counts the blank cells with formula
View Single Post
 
Old 12-29-2019, 04:54 AM
p45cal's Avatar
p45cal p45cal is offline Windows 10 Office 2019
Expert
  
Join Date: Apr 2014
Posts: 871
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default
You could try a formula such as:
=SUMPRODUCT(--(LEN($AC$10:$AC$29)>0))
but that would take no account of hidden rows.
This might work though:
Code:
=SUMPRODUCT((LEN($AC$10:$AC$29)>0)*(SUBTOTAL(103,OFFSET($AC$10,ROW($AC$10:$AC$29)-MIN(ROW($AC$10:$AC$29)),0))))
yuk.
Reply With Quote