Count help for filtered fields only!!

J

junes

New Member
Joined
Jan 24, 2008
Messages
38
All,
I have been ranking my brain all morning and i just can't get excel to output the correct vaule.

I have a excel spreadsheet which 25000 rows.

I have filtered the spreadsheet so it only shows me rows which Years (Column C) is less than or equal to 2005.

Now i want to count all the colums which are being displayed, but what i am finding is it is counting all the 25000 rows.

I have tried to use SUMIF/COUNTIF and SUMPRODUCT.

But i think i am looking and trying to hard and i am not getting anywere.

Can somehelp please.

<TABLE style="WIDTH: 99pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=132 x:str><COLGROUP><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1316" width=36><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 27pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 width=36 x:num>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 40pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=53>Apr</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 32pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 width=43 x:num>2002</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 x:num>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>Apr</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 x:num>2000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 x:num>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>Apr</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 x:num>2004</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 x:num>4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>Apr</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 x:num>2000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 x:num>4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>Apr</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 x:num>2005</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 x:num>4</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>Apr</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 x:num>2000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 x:num>3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>Apr</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 x:num>2001</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 x:num>3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>Apr</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 x:num>2002</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 x:num>3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>Apr</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 x:num>2000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 height=17 x:num>29</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22>Sep</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22 x:num>2001</TD></TR></TBODY></TABLE>
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Just need explain all the information has come from a file, which i have dragged to a excel spreadsheet

So all the columns are String (General) and not a number
 
Upvote 0
Once you have applied your filter try using the subtotal formula. I have fitlered the same list as you indicated, so no 2005 shown, filter arrows missing in this view.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">5</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Apr</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">2002</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">5</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Apr</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">2000</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">5</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Apr</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">2004</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">4</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Apr</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">2000</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">4</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Apr</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">2000</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">3</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Apr</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">2001</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">3</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Apr</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">2002</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">3</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Apr</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">2000</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">29</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 10pt">Sep</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Arial; FONT-SIZE: 10pt">2001</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Tahoma; COLOR: #333333; FONT-SIZE: 10pt">9</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C11</TD><TD>=SUBTOTAL(103,C1:C10)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Click to expand...
 
Upvote 0
Can you describe what you are trying to count ( exact details please )? I assume doing Subtotals on the list isn't doing what you want somehow?

... too late, see Trevors post.
 
Upvote 0
I am trying to count all of coulmn C once i have filtered out anything with less or equal than 2005.
 
Upvote 0
The SUBTOTAL function does that ( as Trevor said ).
 
Upvote 0
junes said:
Can you explain to me what 103 is.......
Click to expand...

If you copy what I have shown you in the thread into a sheet then use the Function Wizard on the formula bar you can read the help file about Subtotal Functions and there numbers.

Here is the table

<TABLE class=collapse><TBODY><TR class=trbgeven><TH class=thhead vAlign=top align=left>Function_num
(includes hidden values)

</TH><TH class=thhead vAlign=top align=left>Function_num
(ignores hidden values)

</TH><TH class=thhead vAlign=top align=left>Function</TH></TR><TR class=trbgodd><TD class=noborder vAlign=top align=left>1</TD><TD class=noborder vAlign=top align=left>101</TD><TD class=noborder vAlign=top align=left>AVERAGE</TD></TR><TR class=trbgeven><TD class=noborder vAlign=top align=left>2</TD><TD class=noborder vAlign=top align=left>102</TD><TD class=noborder vAlign=top align=left>COUNT</TD></TR><TR class=trbgodd><TD class=noborder vAlign=top align=left>3</TD><TD class=noborder vAlign=top align=left>103</TD><TD class=noborder vAlign=top align=left>COUNTA</TD></TR><TR class=trbgeven><TD class=noborder vAlign=top align=left>4</TD><TD class=noborder vAlign=top align=left>104</TD><TD class=noborder vAlign=top align=left>MAX</TD></TR><TR class=trbgodd><TD class=noborder vAlign=top align=left>5</TD><TD class=noborder vAlign=top align=left>105</TD><TD class=noborder vAlign=top align=left>MIN</TD></TR><TR class=trbgeven><TD class=noborder vAlign=top align=left>6</TD><TD class=noborder vAlign=top align=left>106</TD><TD class=noborder vAlign=top align=left>PRODUCT</TD></TR><TR class=trbgodd><TD class=noborder vAlign=top align=left>7</TD><TD class=noborder vAlign=top align=left>107</TD><TD class=noborder vAlign=top align=left>STDEV</TD></TR><TR class=trbgeven><TD class=noborder vAlign=top align=left>8</TD><TD class=noborder vAlign=top align=left>108</TD><TD class=noborder vAlign=top align=left>STDEVP</TD></TR><TR class=trbgodd><TD class=noborder vAlign=top align=left>9</TD><TD class=noborder vAlign=top align=left>109</TD><TD class=noborder vAlign=top align=left>SUM</TD></TR><TR class=trbgeven><TD class=noborder vAlign=top align=left>10</TD><TD class=noborder vAlign=top align=left>110</TD><TD class=noborder vAlign=top align=left>VAR</TD></TR><TR class=trbgodd><TD class=noborder vAlign=top align=left>11</TD><TD class=noborder vAlign=top align=left>111</TD><TD class=noborder vAlign=top align=left>VARP</TD></TR></TBODY></TABLE>
Click to expand...

Morning Glenn hope you have a good week.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
624
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,618
Messages
6,179,917
Members
452,949
Latest member
beartooth91

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top