Countifs and date comparison
 

Okay, trying to figure one out here (although Dave Peterson could probably
do it in his sleep :D).

I have two sheets, Sheet_2 and dB_File. I want to use an employee number to
count the number of sales an employee has made for a particular month.

for example, Cell G13 on Sheet_2 has the employee number and Cell G1 has the
reporting period date (mm/dd/yyyy - i.e. 3/1/2010). In cell G17 on Sheet_2,
I want to count the number of sales that employee number 203 made 3 months
before the reporting period date (i.e. in 12/2009). The employee number is
in Column B of the dB_File sheet and the date the employee made the sale is
in Column BQ of the dB_File sheet. But I don't know how to constrain it to
just the month 3 months ago. This is what I have so far, but don't know how
to finish it.
countifs ( range 1 criteria 1
range2(the date col)
"=COUNTIFS(dB_File!$B$2:$B$99999,Sheet_2!$G$13,dB_ File!$BQ$2:$BQ$99999, ?)

I just can't seem to figure out the date criteria. Would it be something
like ...
Date(Year($G$1),MONTH($G$1)-4)? ($G$1 is the date of the current reporting
period.)
--
Nothing in life is ever easy - just get used to that fact.

Countifs and date comparison
 

Oh, almost forgot two things.

The date that the employee made the sale is in the format MM/DD/YYYY and has
to remain that way so we know what pay period the employee needs to get paid.

I am using Exl 2007.
--
Nothing in life is ever easy - just get used to that fact.


"KennyD" wrote:

Okay, trying to figure one out here (although Dave Peterson could probably
do it in his sleep :D).

I have two sheets, Sheet_2 and dB_File. I want to use an employee number to
count the number of sales an employee has made for a particular month.

for example, Cell G13 on Sheet_2 has the employee number and Cell G1 has the
reporting period date (mm/dd/yyyy - i.e. 3/1/2010). In cell G17 on Sheet_2,
I want to count the number of sales that employee number 203 made 3 months
before the reporting period date (i.e. in 12/2009). The employee number is
in Column B of the dB_File sheet and the date the employee made the sale is
in Column BQ of the dB_File sheet. But I don't know how to constrain it to
just the month 3 months ago. This is what I have so far, but don't know how
to finish it.
countifs ( range 1 criteria 1
range2(the date col)
"=COUNTIFS(dB_File!$B$2:$B$99999,Sheet_2!$G$13,dB_ File!$BQ$2:$BQ$99999, ?)

I just can't seem to figure out the date criteria. Would it be something
like ...
Date(Year($G$1),MONTH($G$1)-4)? ($G$1 is the date of the current reporting
period.)
--
Nothing in life is ever easy - just get used to that fact.

Countifs and date comparison
 

I'm not quite sure what 3 months ago means.

Maybe the date criteria should be:
"="&Date(Year($G$1),MONTH($G$1)-3,1)
or
"="&Date(Year($G$1),MONTH($G$1)-3, day($g$1))

If G1 contains May 8, 2010 (no matter how it's formatted), do you want to go
back to:
February 1, 2010
or
February 8, 2010


And if you're looking back in time (say you want to find the number of sales
from Sept 1 to Dec 1, 2008, you may find that you want to exclude the entries
that were made (on or after) Dec 1, 2008

"<"&Date(Year($G$1),MONTH($G$1), 1)
or
"<"&$g$1

=COUNTIFS(dB_File!$B$2:$B$99999,Sheet_2!$G$13,
dB_File!$BQ$2:$BQ$99999, "="&Date(Year($G$1),MONTH($G$1)-3,1),
dB_File!$BQ$2:$BQ$99999, "<"&Date(Year($G$1),MONTH($G$1),1))

I don't have xl2007 running on this pc, so watch out for typos!


KennyD wrote:

Okay, trying to figure one out here (although Dave Peterson could probably
do it in his sleep :D).

I have two sheets, Sheet_2 and dB_File. I want to use an employee number to
count the number of sales an employee has made for a particular month.

for example, Cell G13 on Sheet_2 has the employee number and Cell G1 has the
reporting period date (mm/dd/yyyy - i.e. 3/1/2010). In cell G17 on Sheet_2,
I want to count the number of sales that employee number 203 made 3 months
before the reporting period date (i.e. in 12/2009). The employee number is
in Column B of the dB_File sheet and the date the employee made the sale is
in Column BQ of the dB_File sheet. But I don't know how to constrain it to
just the month 3 months ago. This is what I have so far, but don't know how
to finish it.
countifs ( range 1 criteria 1
range2(the date col)
"=COUNTIFS(dB_File!$B$2:$B$99999,Sheet_2!$G$13,dB_ File!$BQ$2:$BQ$99999, ?)

I just can't seem to figure out the date criteria. Would it be something
like ...
Date(Year($G$1),MONTH($G$1)-4)? ($G$1 is the date of the current reporting
period.)

--

Dave Peterson

Countifs and date comparison
 

While COUNTIFS and its cousins were very welcome additions, there are
something that still require SUMPRODUCT
=SUMPRODUCT(--(dB_File!B:B=G13),--(DATE(YEAR(dB_File!C:C),MONTH(dB_File!C:C),1)=DATE (YEAR(G1),MONTH(G1)-4,1)))
Note you can use full column references with SUMPRODUCT in Excel2007+
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"KennyD" wrote in message
...
Okay, trying to figure one out here (although Dave Peterson could
probably
do it in his sleep :D).

I have two sheets, Sheet_2 and dB_File. I want to use an employee number
to
count the number of sales an employee has made for a particular month.

for example, Cell G13 on Sheet_2 has the employee number and Cell G1 has
the
reporting period date (mm/dd/yyyy - i.e. 3/1/2010). In cell G17 on
Sheet_2,
I want to count the number of sales that employee number 203 made 3 months
before the reporting period date (i.e. in 12/2009). The employee number
is
in Column B of the dB_File sheet and the date the employee made the sale
is
in Column BQ of the dB_File sheet. But I don't know how to constrain it
to
just the month 3 months ago. This is what I have so far, but don't know
how
to finish it.
countifs ( range 1 criteria 1
range2(the date col)
"=COUNTIFS(dB_File!$B$2:$B$99999,Sheet_2!$G$13,dB_ File!$BQ$2:$BQ$99999, ?)

I just can't seem to figure out the date criteria. Would it be something
like ...
Date(Year($G$1),MONTH($G$1)-4)? ($G$1 is the date of the current reporting
period.)
--
Nothing in life is ever easy - just get used to that fact.