Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-28-2019, 04:15 AM
LearnerExcel LearnerExcel is offline SubTotal function counts the blank cells with formula Windows 7 32bit SubTotal function counts the blank cells with formula Office 2013
Advanced Beginner
SubTotal function counts the blank cells with formula
 
Join Date: Nov 2016
Posts: 81
LearnerExcel will become famous soon enoughLearnerExcel will become famous soon enough
Default SubTotal function counts the blank cells with formula

Hi,



I have to count the cells which have the data in column "AC" (range from "AC10" to"AC29"). But it calculates the blank cells as well (which have the only formula in those cells). How to resolve the issue.

The below is the formula:-
Code:
=SUBTOTAL(103,$AC$10:$AC$29)
Please help.
Reply With Quote
  #2  
Old 12-28-2019, 09:48 AM
jeffreybrown jeffreybrown is offline SubTotal function counts the blank cells with formula Windows 10 SubTotal function counts the blank cells with formula Office 2016
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Quote:
it calculates the blank cells as well (which have the only formula in those cells).
It sure seems like this contradicts itself. How can the cells be blank if it has a formula?

Can you post a sample file? This will help clear up what we don't know/see about what you are working on.
Reply With Quote
  #3  
Old 12-28-2019, 12:58 PM
p45cal's Avatar
p45cal p45cal is offline SubTotal function counts the blank cells with formula Windows 10 SubTotal function counts the blank cells with formula Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by jeffreybrown View Post
How can the cells be blank if it has a formula?
formula:
=""
1. is counted as a blank by COUNTBLANK
2. is counted as not being blank by SUBTOTAL(103,… (aka COUNTA).
Reply With Quote
  #4  
Old 12-29-2019, 04:54 AM
p45cal's Avatar
p45cal p45cal is offline SubTotal function counts the blank cells with formula Windows 10 SubTotal function counts the blank cells with formula Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
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
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
SubTotal function counts the blank cells with formula SUBTOTAL FUNCTION PROBLEM when add a new line on top excelino Excel 2 05-12-2018 03:15 PM
Formula for blank cells datadummy Excel 2 09-11-2017 10:19 PM
Help with Subtotal(103, ) formula and blank cells zhl203 Excel 1 07-30-2014 03:40 PM
Formula to hide '0' from blank referenced cells formuladummy Excel 3 05-08-2014 02:33 AM
SubTotal function counts the blank cells with formula multiple conditions, and blank cells with the countifs function jaden0605 Excel 1 03-30-2014 01:50 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:11 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft