Filter and calculating results

**cjl83** · 12-15-2011, 08:10 PM

Hi,

I am trying to calculate the filtered results as a percentage of the overall amount of entries with the spreadsheet. I have done plenty of searching and have had no luck, with getting it to work.

Filters: internal, staff etc

There are 5933 entries in the spreadsheet in column J

Cheers,

Chris

**daddylonglegs** · 12-15-2011, 08:34 PM

Hello Chris,

You can use SUBTOTAL function to count filtered rows, so if you filter this formula will give the number of populated filtered rows in column J as a percentage of the total populated

=SUBTOTAL(3,J2:J10000)/COUNTA(J2:J10000)

**MarvinP** · 12-15-2011, 09:34 PM

Hi cjl83 and welcome to the forum,

Without a sample file it is hard to do this problem. It seems to me a pivot table could do this problem. See http://contextures.com/xlPivot10.html

**cjl83** · 12-15-2011, 10:41 PM

Great, it works a treat....thank you...

**cjl83** · 12-15-2011, 10:54 PM

Hmmm probably needs to be more accurate, unfortunately.

I have the following formula that I am trying to use to tell me how many items are in category and it is chucking out errors

=IF('sci-sci.csv'!J2:J5934)="Promotional",1,0)

Also is there anyway to link the drop down filter menu to another tab so ultimately you are not seeing all the content within the spreadsheet but just the results?

**cjl83** · 12-18-2011, 01:04 AM

Help would be appreciated, does anyone have any ideas.

Cheers,

Chris

**daddylonglegs** · 12-18-2011, 11:08 AM

Originally Posted by cjl83

I have the following formula that I am trying to use to tell me how many items are in category and it is chucking out errors

=IF('sci-sci.csv'!J2:J5934)="Promotional",1,0)

How does this relate to your original question....or is this a new question?

Are you putting that formula on each row or do you want a count for the whole range?

**MarvinP** · 12-18-2011, 03:24 AM

Hey Chris,

We need a sample workbook to see what the problem is and how it is set up. You can attach a sample by clicking on "Go Advanced" and then the Paper Clip Icon above the message area.

**cjl83** · 12-18-2011, 05:31 PM

Hi All,

Daddylonglegs it is a new question. I am wanting to be quiet accurate because I am working with 1000's of web pages and I need to be able to accurately report the type of page it is.

He is an example of what I am looking at and trying to do example.xls

Appreciate the help guys

Cheers,

Chris