How do I find the month

[Chris]

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((D2188="*01/2008")*(L2:L188>0))

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

 

[Rick Rothstein \(MVP - VB\)]

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

=SUMPRODUCT((MONTH(D2188)=1)*(D2188<>"")*(L2:L188>0))

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

 

[PCLIVE]

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

If you do have values less than zero, then:
=SUMPRODUCT(--(MONTH(D2188)=1),--(YEAR(D2188)=2008),--(L2:L188>0),(L2:L188))

HTH,
Paul

 

[Teethless mama]

=SUMPRODUCT((TEXT(D2188,"mm/yyyy")="01/2008")*(L2:L188>0))

 

[Chris]

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\)]

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