How do I find the month
 

Help !!! I need to find out how many sales someone did in say Jan. I've got
this so far but it won't work with the date field..
=SUMPRODUCT((D2:D188="*01/2008")*(L2:L1880))

Many thanks in advance..

Opened Sales Person Jan Feb
08-Jan-08 Gary £1,189.11 £2,437.42
19-Dec-07 Anthony £792.81 £2,082.08
19-Dec-07 Anthony £414.06 £409.35
23-Jan-08 Stephen £276.00 £419.38
29-Jan-08 Bobby £298.15 £529.62

How do I find the month
 

You could use this instead of what you posted...

=SUMPRODUCT((MONTH(D2:D188)=1)*(D2:D188<"")*(L2:L 1880))

but I would note that this does not include a conditional test for an
individual sales person (which your original question seemed to indicate
might be your goal).

Rick


"Chris" wrote in message
...
Help !!! I need to find out how many sales someone did in say Jan. I've
got
this so far but it won't work with the date field..
=SUMPRODUCT((D2:D188="*01/2008")*(L2:L1880))

Many thanks in advance..

Opened Sales Person Jan Feb
08-Jan-08 Gary £1,189.11 £2,437.42
19-Dec-07 Anthony £792.81 £2,082.08
19-Dec-07 Anthony £414.06 £409.35
23-Jan-08 Stephen £276.00 £419.38
29-Jan-08 Bobby £298.15 £529.62

How do I find the month
 

Unless you have some values in L2 that are less than zero, then you don't
have to inclued the "0" part.
=SUMPRODUCT(--(MONTH(D2:D188)=1),--(YEAR(D2:D188)=2008),--(L2:L188))

If you do have values less than zero, then:
=SUMPRODUCT(--(MONTH(D2:D188)=1),--(YEAR(D2:D188)=2008),--(L2:L1880),(L2:L188))

HTH,
Paul


--

"Chris" wrote in message
...
Help !!! I need to find out how many sales someone did in say Jan. I've
got
this so far but it won't work with the date field..
=SUMPRODUCT((D2:D188="*01/2008")*(L2:L1880))

Many thanks in advance..

Opened Sales Person Jan Feb
08-Jan-08 Gary £1,189.11 £2,437.42
19-Dec-07 Anthony £792.81 £2,082.08
19-Dec-07 Anthony £414.06 £409.35
23-Jan-08 Stephen £276.00 £419.38
29-Jan-08 Bobby £298.15 £529.62

How do I find the month
 

=SUMPRODUCT((TEXT(D2:D188,"mm/yyyy")="01/2008")*(L2:L1880))


"Chris" wrote:

Help !!! I need to find out how many sales someone did in say Jan. I've got
this so far but it won't work with the date field..
=SUMPRODUCT((D2:D188="*01/2008")*(L2:L1880))

Many thanks in advance..

Opened Sales Person Jan Feb
08-Jan-08 Gary £1,189.11 £2,437.42
19-Dec-07 Anthony £792.81 £2,082.08
19-Dec-07 Anthony £414.06 £409.35
23-Jan-08 Stephen £276.00 £419.38
29-Jan-08 Bobby £298.15 £529.62

How do I find the month
 

Thanks for your quick responses, I will put in the salespersons name
J2:J188="Anthony", the 0 argument is to check that there's an amount in the
field...

"Rick Rothstein (MVP - VB)" wrote:

You could use this instead of what you posted...

=SUMPRODUCT((MONTH(D2:D188)=1)*(D2:D188<"")*(L2:L 1880))

but I would note that this does not include a conditional test for an
individual sales person (which your original question seemed to indicate
might be your goal).

Rick


"Chris" wrote in message
...
Help !!! I need to find out how many sales someone did in say Jan. I've
got
this so far but it won't work with the date field..
=SUMPRODUCT((D2:D188="*01/2008")*(L2:L1880))

Many thanks in advance..

Opened Sales Person Jan Feb
08-Jan-08 Gary £1,189.11 £2,437.42
19-Dec-07 Anthony £792.81 £2,082.08
19-Dec-07 Anthony £414.06 £409.35
23-Jan-08 Stephen £276.00 £419.38
29-Jan-08 Bobby £298.15 £529.62

How do I find the month
 

You might want to consider one of the other two formulas instead of mine...
those both account for the year as well as the month (it might not important
now, but may be when you get to next January).

Rick


"Chris" wrote in message
...
Thanks for your quick responses, I will put in the salespersons name
J2:J188="Anthony", the 0 argument is to check that there's an amount in
the
field...

"Rick Rothstein (MVP - VB)" wrote:

You could use this instead of what you posted...

=SUMPRODUCT((MONTH(D2:D188)=1)*(D2:D188<"")*(L2:L 1880))

but I would note that this does not include a conditional test for an
individual sales person (which your original question seemed to indicate
might be your goal).

Rick


"Chris" wrote in message
...
Help !!! I need to find out how many sales someone did in say Jan.
I've
got
this so far but it won't work with the date field..
=SUMPRODUCT((D2:D188="*01/2008")*(L2:L1880))

Many thanks in advance..

Opened Sales Person Jan Feb
08-Jan-08 Gary £1,189.11 £2,437.42
19-Dec-07 Anthony £792.81 £2,082.08
19-Dec-07 Anthony £414.06 £409.35
23-Jan-08 Stephen £276.00 £419.38
29-Jan-08 Bobby £298.15 £529.62