| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting data inexcell 2003
I have 2 spreadsheets, one listing a list of sales, the other showing a
summary of sales by each month. how do i get the summary from the detailed list by month. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting data inexcell 2003
Assuming there is a date associated with each sale:
=SUMPRODUCT(--(MONTH(A2:A100)=6),B2:B100) Column A contains Dates Column B contains Sales Above give sales for June (ignoring year) =SUMPRODUCT(--(MONTH(A2:A100)=6),--(YEAR(A2:A100)=2007),B2:B100) Above give sales for June 2007 HTH "John cheshire lad" wrote: I have 2 spreadsheets, one listing a list of sales, the other showing a summary of sales by each month. how do i get the summary from the detailed list by month. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting data inexcell 2003
Thank you very much I will give it a try. Thanks again.
"Toppers" wrote: Assuming there is a date associated with each sale: =SUMPRODUCT(--(MONTH(A2:A100)=6),B2:B100) Column A contains Dates Column B contains Sales Above give sales for June (ignoring year) =SUMPRODUCT(--(MONTH(A2:A100)=6),--(YEAR(A2:A100)=2007),B2:B100) Above give sales for June 2007 HTH "John cheshire lad" wrote: I have 2 spreadsheets, one listing a list of sales, the other showing a summary of sales by each month. how do i get the summary from the detailed list by month. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting data inexcell 2003
Hi Toppers
Thanks a lot but is the syntax correct as I keep getting formula errors. regards john "Toppers" wrote: Assuming there is a date associated with each sale: =SUMPRODUCT(--(MONTH(A2:A100)=6),B2:B100) Column A contains Dates Column B contains Sales Above give sales for June (ignoring year) =SUMPRODUCT(--(MONTH(A2:A100)=6),--(YEAR(A2:A100)=2007),B2:B100) Above give sales for June 2007 HTH "John cheshire lad" wrote: I have 2 spreadsheets, one listing a list of sales, the other showing a summary of sales by each month. how do i get the summary from the detailed list by month. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting data inexcell 2003
If you don't want to stretch the clairvoyant powers of the group, it might
be handy if you tell us what error message you're getting. Where in the formula does the cursor sit when Excel reports the error? What do your windows regional settings use for separators? Are you sure that lists are separated by commas, or might they be semi-colons in your system? -- David Biddulph "John cheshire lad" wrote in message ... Hi Toppers Thanks a lot but is the syntax correct as I keep getting formula errors. regards john "Toppers" wrote: Assuming there is a date associated with each sale: =SUMPRODUCT(--(MONTH(A2:A100)=6),B2:B100) Column A contains Dates Column B contains Sales Above give sales for June (ignoring year) =SUMPRODUCT(--(MONTH(A2:A100)=6),--(YEAR(A2:A100)=2007),B2:B100) Above give sales for June 2007 HTH "John cheshire lad" wrote: I have 2 spreadsheets, one listing a list of sales, the other showing a summary of sales by each month. how do i get the summary from the detailed list by month. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting data inexcell 2003
I have now sorted it out.
Thanks very much foe all your help. John "David Biddulph" wrote: If you don't want to stretch the clairvoyant powers of the group, it might be handy if you tell us what error message you're getting. Where in the formula does the cursor sit when Excel reports the error? What do your windows regional settings use for separators? Are you sure that lists are separated by commas, or might they be semi-colons in your system? -- David Biddulph "John cheshire lad" wrote in message ... Hi Toppers Thanks a lot but is the syntax correct as I keep getting formula errors. regards john "Toppers" wrote: Assuming there is a date associated with each sale: =SUMPRODUCT(--(MONTH(A2:A100)=6),B2:B100) Column A contains Dates Column B contains Sales Above give sales for June (ignoring year) =SUMPRODUCT(--(MONTH(A2:A100)=6),--(YEAR(A2:A100)=2007),B2:B100) Above give sales for June 2007 HTH "John cheshire lad" wrote: I have 2 spreadsheets, one listing a list of sales, the other showing a summary of sales by each month. how do i get the summary from the detailed list by month. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting data inexcell 2003
Glad it's sorted. Perhaps you could let us know why Toppers' formula wasn't
working for you and how you solved it, for the benefit of the group archive and other readers who may have similar problems? -- David Biddulph "John cheshire lad" wrote in message ... I have now sorted it out. Thanks very much foe all your help. John "David Biddulph" wrote: If you don't want to stretch the clairvoyant powers of the group, it might be handy if you tell us what error message you're getting. Where in the formula does the cursor sit when Excel reports the error? What do your windows regional settings use for separators? Are you sure that lists are separated by commas, or might they be semi-colons in your system? "John cheshire lad" wrote in message ... Hi Toppers Thanks a lot but is the syntax correct as I keep getting formula errors. "Toppers" wrote: Assuming there is a date associated with each sale: =SUMPRODUCT(--(MONTH(A2:A100)=6),B2:B100) Column A contains Dates Column B contains Sales Above give sales for June (ignoring year) =SUMPRODUCT(--(MONTH(A2:A100)=6),--(YEAR(A2:A100)=2007),B2:B100) Above give sales for June 2007 "John cheshire lad" wrote: I have 2 spreadsheets, one listing a list of sales, the other showing a summary of sales by each month. how do i get the summary from the detailed list by month. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting data inexcell 2003
David
One other question. What If I wanted to rerieve data from another worksheet what would be needed. thanks. john "John cheshire lad" wrote: I have now sorted it out. Thanks very much foe all your help. John "David Biddulph" wrote: If you don't want to stretch the clairvoyant powers of the group, it might be handy if you tell us what error message you're getting. Where in the formula does the cursor sit when Excel reports the error? What do your windows regional settings use for separators? Are you sure that lists are separated by commas, or might they be semi-colons in your system? -- David Biddulph "John cheshire lad" wrote in message ... Hi Toppers Thanks a lot but is the syntax correct as I keep getting formula errors. regards john "Toppers" wrote: Assuming there is a date associated with each sale: =SUMPRODUCT(--(MONTH(A2:A100)=6),B2:B100) Column A contains Dates Column B contains Sales Above give sales for June (ignoring year) =SUMPRODUCT(--(MONTH(A2:A100)=6),--(YEAR(A2:A100)=2007),B2:B100) Above give sales for June 2007 HTH "John cheshire lad" wrote: I have 2 spreadsheets, one listing a list of sales, the other showing a summary of sales by each month. how do i get the summary from the detailed list by month. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting data inexcell 2003
Change =SUMPRODUCT(--(MONTH(A2:A100)=6),B2:B100) to
=SUMPRODUCT(--(MONTH('Sheet 2'!A2:A100)=6),'Sheet 2'!B2:B100) if you want to retrieve data from a sheet called Sheet 2. -- David Biddulph "John cheshire lad" wrote in message ... David One other question. What If I wanted to rerieve data from another worksheet what would be needed. "John cheshire lad" wrote: I have now sorted it out. Thanks very much foe all your help. "David Biddulph" wrote: If you don't want to stretch the clairvoyant powers of the group, it might be handy if you tell us what error message you're getting. Where in the formula does the cursor sit when Excel reports the error? What do your windows regional settings use for separators? Are you sure that lists are separated by commas, or might they be semi-colons in your system? "John cheshire lad" wrote in message ... Hi Toppers Thanks a lot but is the syntax correct as I keep getting formula errors. regards john "Toppers" wrote: Assuming there is a date associated with each sale: =SUMPRODUCT(--(MONTH(A2:A100)=6),B2:B100) Column A contains Dates Column B contains Sales Above give sales for June (ignoring year) =SUMPRODUCT(--(MONTH(A2:A100)=6),--(YEAR(A2:A100)=2007),B2:B100) Above give sales for June 2007 HTH "John cheshire lad" wrote: I have 2 spreadsheets, one listing a list of sales, the other showing a summary of sales by each month. how do i get the summary from the detailed list by month. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help extracting data.... | Excel Worksheet Functions | |||
| Extracting Data for .Txt Files By Unique Field Data | Excel Discussion (Misc queries) | |||
| extracting data | Excel Discussion (Misc queries) | |||
| extracting data | Excel Worksheet Functions | |||
| Extracting data | Excel Worksheet Functions | |||
Linear Mode
