Counting rows after filters applied

I'm hoping this is a simple question with a simple answer but when I apply my fiilters to a SS, I need to count up how many rows are left, after I've filtered out the data I don't want. The rows are no longer sequential and are numbered to include the hidden rows and counting the rows manually would be a long painful process.

Find more posts tagged with

Support

Importing and Exporting

Account and User Management

Comments

JamesR

Gef

If this is something that you need on a regular basis, then create a report that shows everything except the stuff that you would normaly filter. When you have the report created go to the report builder and run it and it will tell yo how many records are in the report.

This wy these reports can be created as standing reports so you vere need to set the filters again.

J. Craig Williams

Geoff,

In addition to the reports suggested by JamesR, which are useful, I will often have a 'header' section of the main sheets with summaries and collected info that is always there. If I am filtering the rows a lot even after this information is derived, there are ways to set up the info to always show the header rows.

An easy example is filtering on checkbox like "DONE".

Somewhere in the header, this formula will show a count of how many items are checked:

=COUNTIF($Done20:$Done50, 1)

The header ends at row 20 and the data is in rows 20 to 50.

The count of the reverse is simply

=COUNTIF($Done20:$Done50, 0)

You can also do the same with text -- in which case drop down lists are easiest but searching for matches is not too difficult.

Hope this helps.

Craig

Geoff Thomson

Great advice guys, the report worked perfectly and I never thought of that.

thanks

Geoff

Travis

Those are great options! I just wanted to give you a couple more....

When you first apply a filter, the message banner at the top will tell you how many rows met your criteria. "x rows met filter criteria"

There are also automatic formulas that calculate at the bottom right of your screen when you have cells selected. Once filtered, highlight all the cells in one of your columns and you will see a COUNT formula on the bottom right of your sheet.

Abdul Khan

Hi All,

I tried to use this thread for count on status and region column of my smartsheet and could not succeed either by way of formula or report builder.

Scenario is I have a sheet and want to have count on column headers Status (9 status types) and Region (4 Regions) I want to get total based on Status and based on Region for KPI in another sheet by way of link.

Thanks

JamesR

With 4 regions and 9 status options that means that there are potentially 36 different combinations of output. This is not something that can be explained easily in a thread and would take some time to create, test and impliment. Addiionally would need to know more about the structure of your sheets and the data containe therewith.

it fundimently requires you to create the formulae in the Data sheet for every combination and then link the cells that contain the results to your second sheet using linked cells. It cannot be done in a report.

If you require help setting it up please contact us through our organisation, see my profile for contact details

Bobby Andres

Hey Abdul,

Can you give us more details about the exact formula you are looking for? I would love to help but there are too many missing pieces.

It sounds like it will COUNTIF statements.

What are the possible status types?

Regions?

Region KPIs?

It may also help to get a screenshot of your sheet!

Abdul Khan

Hi Bobby,

I am trying to use new released Smartsheet Sight for a summarized KPI. I have five sheets as source of data, one of it is the below sheet and I need total numbers and percentages which will be in two of the widgets. One widgets showing total number of accounts in particular status, another widget showing total accounts of each region. My sheet have about 500 accounts in different status and region.

Thanks for your help.

Abdul

Taslima Afroze

Hello:

I'm also looking forward to get an answer. After filtering column i like to know how many Rows are left as a count number showing, which i can use in sights as data matrix. I can definitely prepare a report which gives me the rows falls into certain parameter, but it doesn't show as count. And we don't want to see the complete report in our dashboard.

i tried to make a separate row in the actual sheet and filter. But since we don't have Header feature, filtering takes away the row itself.

Hope to get any ideas.

thank you!!

Tas