Cookies help us deliver our services. By using our services, you agree to our use of cookies.
Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

Easily sum/count/average visible cells, rows, or columns only in Excel

In general, the SUM/COUNT/AVERAGE functions will sum/count/average every cell in the specified range. But, sometimes you may filter or hide rows or columns in the specified range, and you need to exclude or ignore these filtered/hidden rows or columns while summing/counting/averaging the range. How? Kutools for Excel’s SUMVISIBLE function, COUNTVISIBLE function, and AVERAGEVISIBLE function can help you handle with visible cells only easily.

Sum visible cells, rows, or columns only in Excel

Count visible cells, rows, or columns only in Excel

Average visible cells, rows, or columns only in Excel


Sum visible cells, rows, or columns only in Excel

For example you hide 2 columns (Column B, D) and 4 rows (Row 3,4,7,10) in a table as below screenshot shown. You can apply the SUMVISIBLE functions to sum the visible cells only, with ignoring any filtered/hidden cells, rows, and columns easily.

1. Select a blank cell you will place the summing result at, and click Kutools > Functions > Statistical & Math > SUMVISIBLE. See screenshot:

shot sumvisible 1

2. In the opening Function Arguments dialog box, please select the range you will sum visible cells only into the References box, and click the OK button.

And now only visible cells are summed up, and the summing result returns into the selected cell. See screenshot:

Note: The formula =SUMVISIBLE(A1:F11) is created and shown in the Formula Bar, and you can use it directly to sum visible cells only in specified range.


Count visible cells, rows, or columns only in Excel

For counting visible cells only with ignoring any filtered/hidden cells, rows, and columns, please do as follows:

1. Select a blank cell you will place the counting result at, and click Kutools > Functions > Statistical & Math > COUNTVISIBLE. See screenshot:

2. In the opening Function Arguments dialog box, please specify the range you will count visible cells only into the Reference box, and click the OK button. See screenshot:

And now the total number of only visible cells returns into the selected cell. See screenshot:

Note: The formula =COUNTVISIBLE(A1:F11) is created and displayed in the Formula Bar, and you can use it directly to count visible cells only.


Average visible cells, rows, or columns only in Excel

For calculating the average of visible cells only with ignoring any filtered/hidden cells, rows, and columns, please do as follows:

1. Select a blank cell you will place the average at, and click Kutools > Functions > Statistical & Math > AVERAGEVISIBLE. See screenshot:

2. In the opening Function Arguments dialog box, please specify the range you will calculate the average of visible cells only, and click the OK button. See screenshot:

And now the average of visible cells only returns into the selected cell. See screenshot:

Note: You can the formula =AVERAGEVISIBLE(A1:F11) is created and displayed in the Formula Bar, and it can be used directly to calculate the average of visible cells only.


Important Tip

the built-in functions of Kutools for Excel will only work in the computers with Kutools for Excel installed and enabled. If you want to transfer or send those workbooks which have been applied the built-in functions of Kutools for Excel, please don’t forget to convert those formulas which have been created with Kutools for Excel functions into calculated results first. You can use Kutools for Excel’s To Actual utility to quickly convert formulas to calculated results. See screenshot:

shot toactual 1


Demo: Easily sum / count / average visible cells, rows, or columns only

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.