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.

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.

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.

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.

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.

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.

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.