| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning a value from a string in a single cell
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? -- Alan |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
returning a value from a string in a single cell
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. "AG" wrote in message ... 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? -- Alan |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
returning a value from a string in a single cell
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. -- Alan "Bob Umlas" wrote: 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. "AG" wrote in message ... 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? -- Alan |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
returning a value from a string in a single cell
What formula did you use? What error message did you receive?
AG wrote: 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. -- Alan "Bob Umlas" wrote: 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. "AG" wrote in message ... 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? -- Alan -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
returning a value from a string in a single cell
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. -- Alan "Dave Peterson" wrote: What formula did you use? What error message did you receive? AG wrote: 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. -- Alan "Bob Umlas" wrote: 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. "AG" wrote in message ... 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? -- Alan -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
returning a value from a string in a single cell
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 (;). AG wrote: 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. -- Alan "Dave Peterson" wrote: What formula did you use? What error message did you receive? AG wrote: 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. -- Alan "Bob Umlas" wrote: 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. "AG" wrote in message ... 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? -- Alan -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
returning a value from a string in a single cell
On Fri, 25 Aug 2006 16:29:02 -0700, AG
wrote: 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 __________________________ |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
returning a value from a string in a single cell
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. -- Alan "Richard Buttrey" wrote: On Fri, 25 Aug 2006 16:29:02 -0700, AG wrote: 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 __________________________ |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
returning a value from a string in a single cell
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 AG wrote: 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. -- Alan "Richard Buttrey" wrote: On Fri, 25 Aug 2006 16:29:02 -0700, AG wrote: 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 __________________________ |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
returning a value from a string in a single cell
Based on other inputs, it appears that the dates are really text strings and
not excel dates. -- Alan "Richard Buttrey" wrote: On Fri, 25 Aug 2006 16:29:02 -0700, AG wrote: 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 __________________________ |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
returning a value from a string in a single cell
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." AG wrote: Based on other inputs, it appears that the dates are really text strings and not excel dates. -- Alan "Richard Buttrey" wrote: On Fri, 25 Aug 2006 16:29:02 -0700, AG wrote: 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 __________________________ -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding a text string w/in a Cell | Excel Discussion (Misc queries) | |||
| Returning Cell Comments from One Cell to Another | Excel Worksheet Functions | |||
| xls worksheet formatting a single cell | Excel Discussion (Misc queries) | |||
| Paste rows of numbers from Word into single Excel cell | Excel Discussion (Misc queries) | |||
| GET.CELL | Excel Worksheet Functions | |||
Hybrid Mode
