MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do I not Subtotal cells with no value?

Posted by A Miller on September 11, 2001 5:13 AM

I have a column, colunmn 1, that looks to another,column 2. The column 1 formula is - =IF(AG20="1NBBB12011","OK","") meaning that if the second column has certain text then it returns an "OK" if it does not have the exact text, then it returns a blank cell. So I end up with a column that has nothing but "OK"s or blanks. I want to subtotal the "OK"s but whenever I use the subtotal command it also totals the blank cells. The exact subtotal formula that I am using is =SUBTOTAL(3,AA11:AC31). I thought it wouldn't subtotal cells with no value in them but apparently that isn't so. Is there any way to not subtotal the cells that have no values in them. I can't use the COUNTIF command because I am using the autofilter. Thank you very much for your time.

Posted by Eric on September 11, 2001 8:01 AM

See if this helps

My pardon if this is too garbled to help, I'm stunned from the bad news regarding attacks on the US today.

If you can tolerate pasting as values, then try changing your if statement from "" to " " for the blank return. Then copy and paste the column as values, highlight it, go edit, replace, hit space bar in the "find" area and leave the "replace with" area blank. Tell it to find and replace all, and that should give you "truly" blank cells. You can use the isblank command to test this.