 |
|
| TheExcelAddict.com | |
| April 6, 2019 | |
| Hi fellow Excel Addict, | |
| I'm still working on my project out of town Monday to
Friday, so that's the reason my newsletter is late and
coming on the weekend. I hope you had a great weekend. Today's tip was prompted by a reader's question. I hope you find it helpful too. If you like this week's tip, please share it and recommend your colleagues and any other Excel users you know to sign up for my newsletter. Keep on Excelling, Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com |
|
|
|
|
|
|
|
| Missed my last newsletter? | |
Click Here to View it Online |
|
|
|
|
| |
|
|
|
|
If you have a favourite quote, send it to me and I
may post it in my newsletter.
|
|
THIS WEEK'S 'EXCEL IN SECONDS' TIP |
|
| Count Or Sum Only
Cells That Are Visible |
|
I recently received this question from reader Antonio P. "The COUNT() function counts everything, including hidden rows. Is there a way to count only the rows that are visible in the spreadsheet?" You have probably asked this same question at some point. Maybe you wanted to COUNT only cells that are visible (i.e. excluding cells that are filtered or hidden) or maybe you wanted to SUM those cells. You already know that when you use a COUNT or SUM function, Excel evaluates the entire range of cells you specify regardless of whether the cells are filtered, hidden or visible. Thankfully, there is a way to get Excel to COUNT or SUM only the visible cells. This may come as a surprise to you but you need to use the SUBTOTAL function to do this. If you have ever used the SUBTOTAL function you will know that there are two parts to it. The first part is the 'function number' and the second part is the range you want to subtotal.  Most Excel users that use the SUBTOTAL function learn to use 9 as the function number without understanding what it means. Using 9 as the function number in the SUBTOTAL function tells Excel to SUM the cells in the specified range. If you want to COUNT the cells you can use the function number 2 for 'numbers only' or 3 to count 'both numeric and text' cells.  Three of these function numbers will evaluate only visible cells if rows are hidden by a filter. If there are manually hidden rows, you will need to use 109 as the function number to SUM only the visible cells, 102 to COUNT only visible numeric cells or 103 to COUNT visible cells whether numeric or text. COUNT and SUM aren't the only functions you can perform with the SUBTOTAL function. There are 11 functions that can be used that allow you to do many other types of calculations. Each function has two numbers, depending on whether you want to exclude rows hidden by a filter or rows hidden manually.  |
|
|
|
|
To share this tip with your friends and
|
|
|
|
|
 |
|
|
|
|
 |
|
| Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose. | |
|
"Spreadsheets Tips From An Excel
Addict" is a weekly publication of
TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved. 8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630 |