returning a value from a string in a single cell

A

AG

I am using Excel 2000.
I have a very long list of numbers that were entered on a given day during
the year. I want to add all of the numbers that were entered during a given
month. The date column of my data is formatted with 1/1/05 to 12/31/05. How
can I select only the numbers entered in a given month and total them for
that month?
 
B

Bob Umlas

If the dates are in A1:A365 and the values are in B1:B365:
=SUMPRODUCT(--(MONTH(A1:A365)=8),B1:B365)
will sum B for August months.
 
A

AG

When I enter this formula into my spreadsheet, I get an error message. Here
is how the data is loaded into the spreadsheet:
date hours
1/1/05 36.5
12/15/05 15.2
8/8/05 24.0

I need to select the data in the "hours" column based on the month in the
"date" column.
 
R

Richard Buttrey

When I enter this formula into my spreadsheet, I get an error message. Here
is how the data is loaded into the spreadsheet:
date hours
1/1/05 36.5
12/15/05 15.2
8/8/05 24.0

I need to select the data in the "hours" column based on the month in the
"date" column.
Click to expand...

Are the dates real Excel date numbers, or just text strings?

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
A

AG

I used the formula as listed by Bob.
The error message I get is: "The formula you typed contains an error."
It offers no help to correct the error.
 
D

Dave Peterson

Do you use USA settings?
Do you use an English version of excel?

For most people in the USA, the list separator is a comma. For lots of other
folks (are you in Europe), they use a semicolon (;).
 
A

AG

The cells were "general" format, but, when I changed them to date format, I
still get the same error message. If I use the LEFT(A2,2) function, it
returns "1/", so it appears to me that number is formatted ok.
 
P

Pete_UK

No, this means the cells are text - if they were entered as proper
dates then a LEFT function would return a number like 39. Do you see an
apostrophe in the formula bar when you click on one of the dates?

Pete
 
A

AG

I am in the US.
--
Alan


Dave Peterson said:
Do you use USA settings?
Do you use an English version of excel?

For most people in the USA, the list separator is a comma. For lots of other
folks (are you in Europe), they use a semicolon (;).
Click to expand...
 
A

AG

Based on other inputs, it appears that the dates are really text strings and
not excel dates.
 
P

Pete_UK

Try this on a blank worksheet: in cell A1 enter 1/10/06 and in cell A2
enter '1/10/06 (with an apostrophe). They both look like dates, but
widen the column - a proper date (the first one) will be right-aligned,
whereas the text date will be left-aligned. Enter this formula in B1:

=LEFT(A1,2)

and copy to B2 - you should see 39 in B1 and 1/ in B2.

As a further check, highlight the column containing your dates then
click Format | Cells | Number (tab) | Date and choose a format like
03/14/98 from the list - do all your dates change to this format? Then
repeat, but choose 14-Mar-98 from the list - do they all follow this
format? If not, then your dates are text.

While the column is highlighted, click Data | Text-to-columns then
click Finish - this should convert them away from Text.

Hope this helps - it's getting late here, but hopefully someone else
can carry on with this.

Pete
 
D

Dave Peterson

I'd try retyping that formula from scratch.

If that doesn't work, copy the formula from the formula bar and paste it into
your response.
I am in the US.
Click to expand...
 
D

Dave Peterson

You may not get the answer you want. In fact, your formula may return an error.

But the data won't cause this error:
"The formula you typed contains an error."
 
Top