returning a value from a string in a single cell

[Guest]

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?

 

[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.

 

[Guest]

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.

 

[Dave Peterson]

What formula did you use? What error message did you receive?

 

[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.

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

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Guest]

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.

 

[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 (.

 

[Guest]

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.

 

[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

 

[Guest]

I am in the US.
--
Alan

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 (.

 

[Guest]

I do not see an apostrophe in the formula bar when I click on a date.

 

[Guest]

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

 

[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

 

[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.

 

[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."