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.