Sum the number of rows within a range in a table, containing any data

**JPollard** · 11-08-2022, 12:18 PM

I need a formula to put in one cell, that will display how many rows within a range within a table have any data in them at all. One row with multiple cells containing data should just count as one row. Blank rows should not count towards the total.

Any help greatly appreciated.

Edit: In addition to the initial request, Fluff13 provided a formula further down the thread that enables the table column names to be entered in two cells outside the table so that the formula uses these two cells to define the columns in the table. Pretty neat!

**Pete_UK** · 11-08-2022, 12:23 PM

Sounds as if you want to COUNT the rows, rather than SUM them - perhaps you can use COUNTIF/S, but it is difficult to say exactly without seeing an example file - follow the guidelines in the yellow banner at the top of the screen.

Pete

**JPollard** · 11-10-2022, 06:52 AM

Hi Pete,
Thanks for the reply. Example file attached.

Jonathan

**TMS** · 11-10-2022, 07:10 AM

One way is to put this in a column in the Table and switch on Totals.

Formula:

Please Login or Register  to view this content.

**JPollard** · 11-10-2022, 07:27 AM

Thanks TMS. That's a great way to do it but I wonder if there is also a way with a formula in one cell outside the table?

**TMS** · 11-10-2022, 08:45 AM

Are you still using Excel 2013?

**HansDouwe** · 11-10-2022, 09:15 AM

If you are updated to Excel 365 you can try this formula outside the table:

Formula:

Please Login or Register  to view this content.

**Fluff13** · 11-10-2022, 09:21 AM

If you now have 365 or 2021

Formula:

Please Login or Register  to view this content.

or non-volatile for earlier versions

Formula:

Please Login or Register  to view this content.

or volatile version

Formula:

Please Login or Register  to view this content.

**HansDouwe** · 11-10-2022, 09:35 AM

The formula in post #7 does not look to a specific period.

If you want to look to a specific period, please adjust the range in the formula, for example:

Formula:

Please Login or Register  to view this content.

**JPollard** · 11-10-2022, 10:46 AM

Thanks for the question, TMS. I've updated my profile to show I'm using 365.

**JPollard** · 11-10-2022, 10:47 AM

Thanks for this detailed reply, Fluff13. I use 365 and the first formula works great.

**Fluff13** · 11-10-2022, 10:49 AM

Glad to help & thanks for the feedback.

**JPollard** · 11-10-2022, 10:56 AM

Thanks HansDouwe. This formula works great. I found that Fluff13's formula does actually also work for a specific period when I tested it.

**JPollard** · 11-10-2022, 11:01 AM

So the formulas from Fluff13 and HansDouwe both work great. Thanks so much to both for their speedy replies and different approaches but equally effective formulas.

A further question: I want to be able to enter the period start in one cell and the period end in another cell (both outside the table and matching the names of table columns) so that the formula uses these two cells to define the columns in the table.

Any ideas?

**Fluff13** · 11-10-2022, 11:17 AM

If you are entering the start & end points as text

Formula:

Please Login or Register  to view this content.

**JPollard** · 11-10-2022, 11:21 AM

Thanks again Fluff13. That works a beaut!

**HansDouwe** · 11-10-2022, 11:22 AM

I found that Fluff13's formula does actually also work for a specific period when I tested it.

My formula in post #9 does also

**Fluff13** · 11-10-2022, 11:29 AM

You're welcome & thanks for the feedback.

**JPollard** · 11-10-2022, 11:33 AM

Originally Posted by HansDouwe

My formula in post #9 does also

It does indeed. I made reference to it working in a direct reply to that post and in a further reply in the whole thread. Thanks again.

Sum the number of rows within a range in a table, containing any data

LinkBack

Thread Tools

Rate This Thread

Display

Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Re: Sum the number of rows within a range in a table, containing any data

Thread Information

Users Browsing this Thread

Similar Threads

How fit the scroll bar range to the number of rows with data?

Creating sequencial incremental number for rows of a data table in a special format

[SOLVED] Insert Xnumber (X=A1) of rows between data in a table& copy data from a range to new cells

Add rows to table based on value in a specific cell + fill table with data from range

Copying table from input data into database - keep same reference number for range table

Popiulate Table from Data Range With Varying number of Records

[SOLVED] Pivot Table - max rows allowed in data range

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions