Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT not recognising dates
Hi.
I have a spreadsheet that imports data from an Access query. The query has 3 columns - date of purchase, profit & whether the product was new(N) or used(U). It has grouped the information by month, so the spreadsheet looks like this: Column A Column B Column C DealDate Profit NewUsed January 2007 £50,000 N January 2007 £30,000 U February 2007 £60,000 N February 2007 £45,000 U etc... What I want to happen is to be able to key in a date (ie 01/02) in cell G2 and to have 2 other cells showing the New & Used profit for that month: £60,000 & £45,000. If I use SUMIF, it recognises column A as a date, but I can't get it to split the monthly total (I get £105,000) If I use SUMPRODUCT, it doesn't recognise column A as a date but as text and instead of typing in 01/02 in G2, I have to type on 'February 2007. =SUMPRODUCT(--(A1:A500=G2),--(C1:C500="N"),(B1:B500)) Is there anyway of getting SUMPRODUCT to recognise the data in column A as a date instead of text? Many thanks Del. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT not recognising dates
Your formula works perfect for me. I'm guessing that where it's messing up
for you is that while the cell shows "January 2007", the date is actually 1/1/07, instead of 1/07. Typing in 1/1/07 into g2, I get the correct answer. hth, Dave "Del" wrote: Hi. I have a spreadsheet that imports data from an Access query. The query has 3 columns - date of purchase, profit & whether the product was new(N) or used(U). It has grouped the information by month, so the spreadsheet looks like this: Column A Column B Column C DealDate Profit NewUsed January 2007 £50,000 N January 2007 £30,000 U February 2007 £60,000 N February 2007 £45,000 U etc... What I want to happen is to be able to key in a date (ie 01/02) in cell G2 and to have 2 other cells showing the New & Used profit for that month: £60,000 & £45,000. If I use SUMIF, it recognises column A as a date, but I can't get it to split the monthly total (I get £105,000) If I use SUMPRODUCT, it doesn't recognise column A as a date but as text and instead of typing in 01/02 in G2, I have to type on 'February 2007. =SUMPRODUCT(--(A1:A500=G2),--(C1:C500="N"),(B1:B500)) Is there anyway of getting SUMPRODUCT to recognise the data in column A as a date instead of text? Many thanks Del. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT not recognising dates
Dave
Many thanks for the prompt reply. Unfortunately, I had tried every combination of the date that I could think of, but none work. It only works when I key the 'date' in as text in G2. When you click on a cell in column A, the forumla bar shows the data as March 2007 and not 01/03/2007. I'm guessing the problem lies in how the data is imported from Access into Excel, but I still can't understand why SUMIF sees it as a date & SUMPRODUCT doesn't. Del. "Dave Breitenbach" wrote: Your formula works perfect for me. I'm guessing that where it's messing up for you is that while the cell shows "January 2007", the date is actually 1/1/07, instead of 1/07. Typing in 1/1/07 into g2, I get the correct answer. hth, Dave "Del" wrote: Hi. I have a spreadsheet that imports data from an Access query. The query has 3 columns - date of purchase, profit & whether the product was new(N) or used(U). It has grouped the information by month, so the spreadsheet looks like this: Column A Column B Column C DealDate Profit NewUsed January 2007 £50,000 N January 2007 £30,000 U February 2007 £60,000 N February 2007 £45,000 U etc... What I want to happen is to be able to key in a date (ie 01/02) in cell G2 and to have 2 other cells showing the New & Used profit for that month: £60,000 & £45,000. If I use SUMIF, it recognises column A as a date, but I can't get it to split the monthly total (I get £105,000) If I use SUMPRODUCT, it doesn't recognise column A as a date but as text and instead of typing in 01/02 in G2, I have to type on 'February 2007. =SUMPRODUCT(--(A1:A500=G2),--(C1:C500="N"),(B1:B500)) Is there anyway of getting SUMPRODUCT to recognise the data in column A as a date instead of text? Many thanks Del. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT not recognising dates
Gotcha. I'm not sure why either. If the March 2007 is in text format, then
the following modification works for me: =SUMPRODUCT(--(A1:A500*1=G2),--(C1:C500="N"),(B1:B500)) "Del" wrote: Dave Many thanks for the prompt reply. Unfortunately, I had tried every combination of the date that I could think of, but none work. It only works when I key the 'date' in as text in G2. When you click on a cell in column A, the forumla bar shows the data as March 2007 and not 01/03/2007. I'm guessing the problem lies in how the data is imported from Access into Excel, but I still can't understand why SUMIF sees it as a date & SUMPRODUCT doesn't. Del. "Dave Breitenbach" wrote: Your formula works perfect for me. I'm guessing that where it's messing up for you is that while the cell shows "January 2007", the date is actually 1/1/07, instead of 1/07. Typing in 1/1/07 into g2, I get the correct answer. hth, Dave "Del" wrote: Hi. I have a spreadsheet that imports data from an Access query. The query has 3 columns - date of purchase, profit & whether the product was new(N) or used(U). It has grouped the information by month, so the spreadsheet looks like this: Column A Column B Column C DealDate Profit NewUsed January 2007 £50,000 N January 2007 £30,000 U February 2007 £60,000 N February 2007 £45,000 U etc... What I want to happen is to be able to key in a date (ie 01/02) in cell G2 and to have 2 other cells showing the New & Used profit for that month: £60,000 & £45,000. If I use SUMIF, it recognises column A as a date, but I can't get it to split the monthly total (I get £105,000) If I use SUMPRODUCT, it doesn't recognise column A as a date but as text and instead of typing in 01/02 in G2, I have to type on 'February 2007. =SUMPRODUCT(--(A1:A500=G2),--(C1:C500="N"),(B1:B500)) Is there anyway of getting SUMPRODUCT to recognise the data in column A as a date instead of text? Many thanks Del. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT not recognising dates
Hi Dave
Finally solved the problem from another direction. In cell H2 I added the formula: =TEXT(G2,"mmmm yyyy") I can now use the resulting text to get the 'N' and 'U' profits for each month. Many thanks again for your time. Del. "Dave Breitenbach" wrote: Your formula works perfect for me. I'm guessing that where it's messing up for you is that while the cell shows "January 2007", the date is actually 1/1/07, instead of 1/07. Typing in 1/1/07 into g2, I get the correct answer. hth, Dave "Del" wrote: Hi. I have a spreadsheet that imports data from an Access query. The query has 3 columns - date of purchase, profit & whether the product was new(N) or used(U). It has grouped the information by month, so the spreadsheet looks like this: Column A Column B Column C DealDate Profit NewUsed January 2007 £50,000 N January 2007 £30,000 U February 2007 £60,000 N February 2007 £45,000 U etc... What I want to happen is to be able to key in a date (ie 01/02) in cell G2 and to have 2 other cells showing the New & Used profit for that month: £60,000 & £45,000. If I use SUMIF, it recognises column A as a date, but I can't get it to split the monthly total (I get £105,000) If I use SUMPRODUCT, it doesn't recognise column A as a date but as text and instead of typing in 01/02 in G2, I have to type on 'February 2007. =SUMPRODUCT(--(A1:A500=G2),--(C1:C500="N"),(B1:B500)) Is there anyway of getting SUMPRODUCT to recognise the data in column A as a date instead of text? Many thanks Del. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT not recognising dates
Copied your formula but result came back as #VALUE!.
What's the significance of the '*1' after the 'A1:A500'. "Dave Breitenbach" wrote: Gotcha. I'm not sure why either. If the March 2007 is in text format, then the following modification works for me: =SUMPRODUCT(--(A1:A500*1=G2),--(C1:C500="N"),(B1:B500)) "Del" wrote: Dave Many thanks for the prompt reply. Unfortunately, I had tried every combination of the date that I could think of, but none work. It only works when I key the 'date' in as text in G2. When you click on a cell in column A, the forumla bar shows the data as March 2007 and not 01/03/2007. I'm guessing the problem lies in how the data is imported from Access into Excel, but I still can't understand why SUMIF sees it as a date & SUMPRODUCT doesn't. Del. "Dave Breitenbach" wrote: Your formula works perfect for me. I'm guessing that where it's messing up for you is that while the cell shows "January 2007", the date is actually 1/1/07, instead of 1/07. Typing in 1/1/07 into g2, I get the correct answer. hth, Dave "Del" wrote: Hi. I have a spreadsheet that imports data from an Access query. The query has 3 columns - date of purchase, profit & whether the product was new(N) or used(U). It has grouped the information by month, so the spreadsheet looks like this: Column A Column B Column C DealDate Profit NewUsed January 2007 £50,000 N January 2007 £30,000 U February 2007 £60,000 N February 2007 £45,000 U etc... What I want to happen is to be able to key in a date (ie 01/02) in cell G2 and to have 2 other cells showing the New & Used profit for that month: £60,000 & £45,000. If I use SUMIF, it recognises column A as a date, but I can't get it to split the monthly total (I get £105,000) If I use SUMPRODUCT, it doesn't recognise column A as a date but as text and instead of typing in 01/02 in G2, I have to type on 'February 2007. =SUMPRODUCT(--(A1:A500=G2),--(C1:C500="N"),(B1:B500)) Is there anyway of getting SUMPRODUCT to recognise the data in column A as a date instead of text? Many thanks Del. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT not recognising dates
No problem. The significance of the "*1" is that if a number is formatted as
text, adding a math function forces it to become a number. If you type in " 'January 2007 " in your cell formatted as text with your old formula and the "*1", then it should also work. But since you would have to retype all of them your solution is more appropriate. "Del" wrote: Hi Dave Finally solved the problem from another direction. In cell H2 I added the formula: =TEXT(G2,"mmmm yyyy") I can now use the resulting text to get the 'N' and 'U' profits for each month. Many thanks again for your time. Del. "Dave Breitenbach" wrote: Your formula works perfect for me. I'm guessing that where it's messing up for you is that while the cell shows "January 2007", the date is actually 1/1/07, instead of 1/07. Typing in 1/1/07 into g2, I get the correct answer. hth, Dave "Del" wrote: Hi. I have a spreadsheet that imports data from an Access query. The query has 3 columns - date of purchase, profit & whether the product was new(N) or used(U). It has grouped the information by month, so the spreadsheet looks like this: Column A Column B Column C DealDate Profit NewUsed January 2007 £50,000 N January 2007 £30,000 U February 2007 £60,000 N February 2007 £45,000 U etc... What I want to happen is to be able to key in a date (ie 01/02) in cell G2 and to have 2 other cells showing the New & Used profit for that month: £60,000 & £45,000. If I use SUMIF, it recognises column A as a date, but I can't get it to split the monthly total (I get £105,000) If I use SUMPRODUCT, it doesn't recognise column A as a date but as text and instead of typing in 01/02 in G2, I have to type on 'February 2007. =SUMPRODUCT(--(A1:A500=G2),--(C1:C500="N"),(B1:B500)) Is there anyway of getting SUMPRODUCT to recognise the data in column A as a date instead of text? Many thanks Del. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct between dates | Excel Worksheet Functions | |||
Excel copied cells not recognising date formats | Excel Discussion (Misc queries) | |||
sumproduct with dates | Excel Worksheet Functions | |||
VLookup not recognising numbers | Excel Worksheet Functions | |||
sumproduct between dates | Excel Worksheet Functions |