dates in one month
 

I am trying to isolate data in a spreadsheet used to track requests for
information. To produce cummulative report, I have used the formula
=COUNTIF(Tracker!$G$2:$G$2000,A2)
where Column G has the person to whom the request has been assigned, and
A2:A12 are the names of the person. This provides me with a running total
throughout the year.
However, I now need to produce a monthly report to check how many requests
have been assigned in a particular month. I've tried:
=SUM(COUNTIF(Tracker!$G$2:$G$2000,A6)-(Countif(Tracker!$H$2:$H$2000,MONTH($C$1))+(Count if((Tracker!$H$2:$H$2000)<MONTH($F$1)))
where Colomn H is the date when request was allocated and C1 and F1 are the
perimetres for the report (ie start and finish dates)

It looks like the Countif function doesn't like the "<" or "" in the
criteria. Does anyone have a solution?

Many thanks!
Vibeke

dates in one month
 

=SUMPRODUCT(--(Tracker!$G$2:$G$2000,A6),
--(Tracker!$H$2:$H$2000MONTH($C$1)),
--(Tracker!$H$2:$H$2000<MONTH($F$1)))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Vibeke" wrote in message
...
I am trying to isolate data in a spreadsheet used to track requests for
information. To produce cummulative report, I have used the formula
=COUNTIF(Tracker!$G$2:$G$2000,A2)
where Column G has the person to whom the request has been assigned, and
A2:A12 are the names of the person. This provides me with a running total
throughout the year.
However, I now need to produce a monthly report to check how many requests
have been assigned in a particular month. I've tried:

=SUM(COUNTIF(Tracker!$G$2:$G$2000,A6)-(Countif(Tracker!$H$2:$H$2000,MONTH($
C$1))+(Countif((Tracker!$H$2:$H$2000)<MONTH($F$1)) )
where Colomn H is the date when request was allocated and C1 and F1 are
the
perimetres for the report (ie start and finish dates)

It looks like the Countif function doesn't like the "<" or "" in the
criteria. Does anyone have a solution?

Many thanks!
Vibeke

dates in one month
 

Hi Bob, and many thanks for your time. However, your suggestion returns
#VALUE!.

I've toyed with variations on SUMPRODUCT, but am not sure it is the function
I need. I'm looking for a formula that will count all the instances of
'Vibeke' (or A6) in Tracker-Column G, and then subtract from that total any
instances that don't occur in a particular month (Tracker-Column H, d-mmm-yy
format) - hence my attempt to COUNTIF(Vibeke) minus COUNTIF(doesn't occur in
end/start dates)

My apologies for not articulating my query clearly.
Regards,
Vibeke


"Bob Phillips" wrote:

=SUMPRODUCT(--(Tracker!$G$2:$G$2000,A6),
--(Tracker!$H$2:$H$2000MONTH($C$1)),
--(Tracker!$H$2:$H$2000<MONTH($F$1)))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Vibeke" wrote in message
...
I am trying to isolate data in a spreadsheet used to track requests for
information. To produce cummulative report, I have used the formula
=COUNTIF(Tracker!$G$2:$G$2000,A2)
where Column G has the person to whom the request has been assigned, and
A2:A12 are the names of the person. This provides me with a running total
throughout the year.
However, I now need to produce a monthly report to check how many requests
have been assigned in a particular month. I've tried:

=SUM(COUNTIF(Tracker!$G$2:$G$2000,A6)-(Countif(Tracker!$H$2:$H$2000,MONTH($
C$1))+(Countif((Tracker!$H$2:$H$2000)<MONTH($F$1)) )
where Colomn H is the date when request was allocated and C1 and F1 are
the
perimetres for the report (ie start and finish dates)

It looks like the Countif function doesn't like the "<" or "" in the
criteria. Does anyone have a solution?

Many thanks!
Vibeke

dates in one month
 

The formula does what you want, or would do without my typo

=SUMPRODUCT(--(Tracker!$G$2:$G$2000=A6),
--(Tracker!$H$2:$H$2000MONTH($C$1)),
--(Tracker!$H$2:$H$2000<MONTH($F$1)))

Is H2:H2000 month numbers or true dates, this formula checks month numbers?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Vibeke" wrote in message
...
Hi Bob, and many thanks for your time. However, your suggestion returns
#VALUE!.

I've toyed with variations on SUMPRODUCT, but am not sure it is the
function
I need. I'm looking for a formula that will count all the instances of
'Vibeke' (or A6) in Tracker-Column G, and then subtract from that total
any
instances that don't occur in a particular month (Tracker-Column H,
d-mmm-yy
format) - hence my attempt to COUNTIF(Vibeke) minus COUNTIF(doesn't occur
in
end/start dates)

My apologies for not articulating my query clearly.
Regards,
Vibeke


"Bob Phillips" wrote:

=SUMPRODUCT(--(Tracker!$G$2:$G$2000,A6),
--(Tracker!$H$2:$H$2000MONTH($C$1)),
--(Tracker!$H$2:$H$2000<MONTH($F$1)))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Vibeke" wrote in message
...
I am trying to isolate data in a spreadsheet used to track requests
for
information. To produce cummulative report, I have used the formula
=COUNTIF(Tracker!$G$2:$G$2000,A2)
where Column G has the person to whom the request has been assigned,
and
A2:A12 are the names of the person. This provides me with a running
total
throughout the year.
However, I now need to produce a monthly report to check how many
requests
have been assigned in a particular month. I've tried:


=SUM(COUNTIF(Tracker!$G$2:$G$2000,A6)-(Countif(Tracker!$H$2:$H$2000,MONTH($
C$1))+(Countif((Tracker!$H$2:$H$2000)<MONTH($F$1)) )
where Colomn H is the date when request was allocated and C1 and F1
are
the
perimetres for the report (ie start and finish dates)

It looks like the Countif function doesn't like the "<" or "" in the
criteria. Does anyone have a solution?

Many thanks!
Vibeke

dates in one month
 

Yes - now I see it WOULD work, but I neglected to mention that H2:H2000 are
true dates, not month numbers. I should have mentioned that the Tracker
worksheet is used to calculate the number of working days between a request
for info being allocated to a person, and the date of response (with
conditional formatting to highlight whenever a request if overdue).

Your suggestion has given me some ideas to work on, but if this information
inspires any new ideas for you, I'd appreciate hearing them.

Again, many thanks.

"Bob Phillips" wrote:

The formula does what you want, or would do without my typo

=SUMPRODUCT(--(Tracker!$G$2:$G$2000=A6),
--(Tracker!$H$2:$H$2000MONTH($C$1)),
--(Tracker!$H$2:$H$2000<MONTH($F$1)))

Is H2:H2000 month numbers or true dates, this formula checks month numbers?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Vibeke" wrote in message
...
Hi Bob, and many thanks for your time. However, your suggestion returns
#VALUE!.

I've toyed with variations on SUMPRODUCT, but am not sure it is the
function
I need. I'm looking for a formula that will count all the instances of
'Vibeke' (or A6) in Tracker-Column G, and then subtract from that total
any
instances that don't occur in a particular month (Tracker-Column H,
d-mmm-yy
format) - hence my attempt to COUNTIF(Vibeke) minus COUNTIF(doesn't occur
in
end/start dates)

My apologies for not articulating my query clearly.
Regards,
Vibeke


"Bob Phillips" wrote:

=SUMPRODUCT(--(Tracker!$G$2:$G$2000,A6),
--(Tracker!$H$2:$H$2000MONTH($C$1)),
--(Tracker!$H$2:$H$2000<MONTH($F$1)))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Vibeke" wrote in message
...
I am trying to isolate data in a spreadsheet used to track requests
for
information. To produce cummulative report, I have used the formula
=COUNTIF(Tracker!$G$2:$G$2000,A2)
where Column G has the person to whom the request has been assigned,
and
A2:A12 are the names of the person. This provides me with a running
total
throughout the year.
However, I now need to produce a monthly report to check how many
requests
have been assigned in a particular month. I've tried:


=SUM(COUNTIF(Tracker!$G$2:$G$2000,A6)-(Countif(Tracker!$H$2:$H$2000,MONTH($
C$1))+(Countif((Tracker!$H$2:$H$2000)<MONTH($F$1)) )
where Colomn H is the date when request was allocated and C1 and F1
are
the
perimetres for the report (ie start and finish dates)

It looks like the Countif function doesn't like the "<" or "" in the
criteria. Does anyone have a solution?

Many thanks!
Vibeke

dates in one month
 

THIS WORKS!!


=SUMPRODUCT(--(Tracker!$G$2:$G$2000=A6),--(Tracker!$H$2:$H$2000($C$1)),--(Tracker!$H$2:$H$2000<($F$1)))

Many thanks! (Your help means I've stuck to my non-smoking vow...but it was
getting close to breach)

Vibeke

"Bob Phillips" wrote:

The formula does what you want, or would do without my typo

=SUMPRODUCT(--(Tracker!$G$2:$G$2000=A6),
--(Tracker!$H$2:$H$2000MONTH($C$1)),
--(Tracker!$H$2:$H$2000<MONTH($F$1)))

Is H2:H2000 month numbers or true dates, this formula checks month numbers?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Vibeke" wrote in message
...
Hi Bob, and many thanks for your time. However, your suggestion returns
#VALUE!.

I've toyed with variations on SUMPRODUCT, but am not sure it is the
function
I need. I'm looking for a formula that will count all the instances of
'Vibeke' (or A6) in Tracker-Column G, and then subtract from that total
any
instances that don't occur in a particular month (Tracker-Column H,
d-mmm-yy
format) - hence my attempt to COUNTIF(Vibeke) minus COUNTIF(doesn't occur
in
end/start dates)

My apologies for not articulating my query clearly.
Regards,
Vibeke


"Bob Phillips" wrote:

=SUMPRODUCT(--(Tracker!$G$2:$G$2000,A6),
--(Tracker!$H$2:$H$2000MONTH($C$1)),
--(Tracker!$H$2:$H$2000<MONTH($F$1)))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Vibeke" wrote in message
...
I am trying to isolate data in a spreadsheet used to track requests
for
information. To produce cummulative report, I have used the formula
=COUNTIF(Tracker!$G$2:$G$2000,A2)
where Column G has the person to whom the request has been assigned,
and
A2:A12 are the names of the person. This provides me with a running
total
throughout the year.
However, I now need to produce a monthly report to check how many
requests
have been assigned in a particular month. I've tried:


=SUM(COUNTIF(Tracker!$G$2:$G$2000,A6)-(Countif(Tracker!$H$2:$H$2000,MONTH($
C$1))+(Countif((Tracker!$H$2:$H$2000)<MONTH($F$1)) )
where Colomn H is the date when request was allocated and C1 and F1
are
the
perimetres for the report (ie start and finish dates)

It looks like the Countif function doesn't like the "<" or "" in the
criteria. Does anyone have a solution?

Many thanks!
Vibeke