Problem with sum of filtered numbers

[Cushlapper]

I have successfully texted to columns and delimited the info I want. The next step is to sum the numbers relating to the time of day for each day of the year. But apparently, Excel will not allow me to perform that function. e.g.:

<TABLE style="WIDTH: 292pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=389><COLGROUP><COL style="WIDTH: 149pt; mso-width-source: userset; mso-width-alt: 7082" width=199><COL style="WIDTH: 143pt; mso-width-source: userset; mso-width-alt: 6769" width=190><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 149pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19 width=199> 09:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 143pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=190 align=right>35537</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19> 09:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1047</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19> 09:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>44566</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19> 09:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>40889</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19> 09:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>33771</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19> 09:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>36865</TD></TR></TBODY></TABLE>

If I want to sum those number on the right, I've tried the ways I know how, but Excl doesn't sum in this instance. Column D is the time (which is filtered) and column E is a general number. Any help would be greatly appreciated. Thanks, Cush

 

[martindwilson]

are they text numbers? put 0 in a spare cell ,copy
select column of numbers then paste special add
make sure when using text to columns the field is set to general

 

[T. Valko]

I have successfully texted to columns and delimited the info I want. The next step is to sum the numbers relating to the time of day for each day of the year. But apparently, Excel will not allow me to perform that function. e.g.:

<TABLE style="WIDTH: 292pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=389 border=0><COLGROUP><COL style="WIDTH: 149pt; mso-width-source: userset; mso-width-alt: 7082" width=199><COL style="WIDTH: 143pt; mso-width-source: userset; mso-width-alt: 6769" width=190><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 149pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" width=199 height=19>09:00</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 143pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right width=190>35537</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" height=19>09:00</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1047</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" height=19>09:00</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>44566</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" height=19>09:00</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>40889</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" height=19>09:00</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>33771</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" height=19>09:00</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>36865</TD></TR></TBODY></TABLE>

If I want to sum those number on the right, I've tried the ways I know how, but Excl doesn't sum in this instance. Column D is the time (which is filtered) and column E is a general number. Any help would be greatly appreciated. Thanks, Cush

If you're using autofilter then use the SUBTOTAL function:

=SUBTOTAL(9,E2:E100)

 

[Cushlapper]

right, I made sure now they are general numbers and I tried the paste special 'add' function, but it still didn't work. How do I confirm what value the numbers are? as in, are they still in text format?

 

[Cushlapper]

Valko to the rescue! Thanks buddy. That was it. As George once said: "You are my density." (my ode to Biff and that awesme movie)

I appreciate both of your assistance. Have a great weekend.

 

[T. Valko]

Valko to the rescue! Thanks buddy. That was it. As George once said: "You are my density." (my ode to Biff and that awesme movie)

I appreciate both of your assistance. Have a great weekend.

You're welcome. Thanks for the feedback!