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

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 > Kutools Functions > Statistical & Math > SUMVISIBLE. See screenshot:

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.

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

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

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: