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