Count # of records based on date ranges

  • Thread starter Thread starter Jennifer L
  • Start date Start date
J

Jennifer L

Column A Column B
Return Date Purchase Date
10/25/03 09/15/03
10/31/03 10/23/03
11/06/03 10/26/03
12/16/03 09/11/03
12/16/03 10/14/03
12/17/03 10/24/03

For those items that were returned in December, how many
were purchased in October?

The answer is 2 of course, but I am trying to create a
formula that will automatically tell me this.

Any help would be greatly appreciated!
Thank you in advance,
Jennifer
 
Jennifer,

Setup:
your data in A1:B7
In D1:E2
Returned in: 12
Purchased in: 10
In F1
=SUMPRODUCT((MONTH(A2:A7)=E1)*(MONTH(B2:B7)=E2))

Dan E
 
Jennifer,
You can use the SUMPRODUCT for this:

=SUMPRODUCT(--(MONTH(A2:A7)=E3),--(MONTH(B2:B7=E4)))

Enter the return month in E3 (10 for october) and the Purchase month in E4 (12 for dec).


Good Luck,
Mark Graesser
(e-mail address removed)

----- Jennifer L wrote: -----

Column A Column B
Return Date Purchase Date
10/25/03 09/15/03
10/31/03 10/23/03
11/06/03 10/26/03
12/16/03 09/11/03
12/16/03 10/14/03
12/17/03 10/24/03

For those items that were returned in December, how many
were purchased in October?

The answer is 2 of course, but I am trying to create a
formula that will automatically tell me this.

Any help would be greatly appreciated!
Thank you in advance,
Jennifer
 
Oops, my previous formula didn't work right. You can use:

=SUMPRODUCT(--(MONTH(A2:A7)=$E$1),--(MONTH(B2:B7)=$E$2))

With the purchase month in E1 and the return month in E2.

Good Luck,
Mark Graesser
(e-mail address removed)
 
Thank you everyone for your help with this! I have got
this to work now, but I have realized that I have data in
my list from 2002.
Is there a way that I can retrieve only the results for
2003?

Thanks again - any help is appreciated!
Jennifer

-----Original Message-----
Jennifer,

Setup:
your data in A1:B7
In D1:E2
Returned in: 12
Purchased in: 10
In F1
=SUMPRODUCT((MONTH(A2:A7)=E1)*(MONTH(B2:B7)=E2))

Dan E

"Jennifer L" <[email protected]> wrote
Click to expand...
in message news:[email protected]...
 
You can keep adding conditions:

=SUMPRODUCT((MONTH(A2:A7)=E1)*(year(a2:a7)=2003)*
(MONTH(B2:B7)=E2)*(year(b2:b7)=2003))
(all one cell)

And you could put those 2003's into different cells.
 
Back
Top