SubTotal function counts the blank cells with formula

LearnerExcel · #1 12-28-2019, 04:15 AM

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.

jeffreybrown · #2 12-28-2019, 09:48 AM

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.

p45cal · #3 12-28-2019, 12:58 PM

Quote:

Originally Posted by jeffreybrown

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).

p45cal · #4 12-29-2019, 04:54 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
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
multiple conditions, and blank cells with the countifs function	jaden0605	Excel	1	03-30-2014 01:50 AM