software

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,gt;MONTH($C$1)) (Count if((Tracker!$H$2:$H$2000)lt;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 quot;lt;quot; or quot;gt;quot; in the
criteria. Does anyone have a solution?

Many thanks!
Vibeke

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Vibekequot; gt; wrote in message
...
gt; I am trying to isolate data in a spreadsheet used to track requests for
gt; information. To produce cummulative report, I have used the formula
gt; =COUNTIF(Tracker!$G$2:$G$2000,A2)
gt; where Column G has the person to whom the request has been assigned, and
gt; A2:A12 are the names of the person. This provides me with a running total
gt; throughout the year.
gt; However, I now need to produce a monthly report to check how many requests
gt; have been assigned in a particular month. I've tried:
gt;
=SUM(COUNTIF(Tracker!$G$2:$G$2000,A6)-(Countif(Tracker!$H$2:$H$2000,gt;MONTH($
C$1)) (Countif((Tracker!$H$2:$H$2000)lt;MONTH($F$1)) )
gt; where Colomn H is the date when request was allocated and C1 and F1 are
the
gt; perimetres for the report (ie start and finish dates)
gt;
gt; It looks like the Countif function doesn't like the quot;lt;quot; or quot;gt;quot; in the
gt; criteria. Does anyone have a solution?
gt;
gt; Many thanks!
gt; Vibeke
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,
Vibekequot;Bob Phillipsquot; wrote:

gt; =SUMPRODUCT(--(Tracker!$G$2:$G$2000,A6),
gt; --(Tracker!$H$2:$H$2000gt;MONTH($C$1)),
gt; --(Tracker!$H$2:$H$2000lt;MONTH($F$1)))
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;Vibekequot; gt; wrote in message
gt; ...
gt; gt; I am trying to isolate data in a spreadsheet used to track requests for
gt; gt; information. To produce cummulative report, I have used the formula
gt; gt; =COUNTIF(Tracker!$G$2:$G$2000,A2)
gt; gt; where Column G has the person to whom the request has been assigned, and
gt; gt; A2:A12 are the names of the person. This provides me with a running total
gt; gt; throughout the year.
gt; gt; However, I now need to produce a monthly report to check how many requests
gt; gt; have been assigned in a particular month. I've tried:
gt; gt;
gt; =SUM(COUNTIF(Tracker!$G$2:$G$2000,A6)-(Countif(Tracker!$H$2:$H$2000,gt;MONTH($
gt; C$1)) (Countif((Tracker!$H$2:$H$2000)lt;MONTH($F$1)) )
gt; gt; where Colomn H is the date when request was allocated and C1 and F1 are
gt; the
gt; gt; perimetres for the report (ie start and finish dates)
gt; gt;
gt; gt; It looks like the Countif function doesn't like the quot;lt;quot; or quot;gt;quot; in the
gt; gt; criteria. Does anyone have a solution?
gt; gt;
gt; gt; Many thanks!
gt; gt; Vibeke
gt;
gt;
gt;

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

=SUMPRODUCT(--(Tracker!$G$2:$G$2000=A6),
--(Tracker!$H$2:$H$2000gt;MONTH($C$1)),
--(Tracker!$H$2:$H$2000lt;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)

quot;Vibekequot; gt; wrote in message
...
gt; Hi Bob, and many thanks for your time. However, your suggestion returns
gt; #VALUE!.
gt;
gt; I've toyed with variations on SUMPRODUCT, but am not sure it is the
function
gt; I need. I'm looking for a formula that will count all the instances of
gt; 'Vibeke' (or A6) in Tracker-Column G, and then subtract from that total
any
gt; instances that don't occur in a particular month (Tracker-Column H,
d-mmm-yy
gt; format) - hence my attempt to COUNTIF(Vibeke) minus COUNTIF(doesn't occur
in
gt; end/start dates)
gt;
gt; My apologies for not articulating my query clearly.
gt; Regards,
gt; Vibeke
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; =SUMPRODUCT(--(Tracker!$G$2:$G$2000,A6),
gt; gt; --(Tracker!$H$2:$H$2000gt;MONTH($C$1)),
gt; gt; --(Tracker!$H$2:$H$2000lt;MONTH($F$1)))
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from the email address if mailing direct)
gt; gt;
gt; gt; quot;Vibekequot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I am trying to isolate data in a spreadsheet used to track requests
for
gt; gt; gt; information. To produce cummulative report, I have used the formula
gt; gt; gt; =COUNTIF(Tracker!$G$2:$G$2000,A2)
gt; gt; gt; where Column G has the person to whom the request has been assigned,
and
gt; gt; gt; A2:A12 are the names of the person. This provides me with a running
total
gt; gt; gt; throughout the year.
gt; gt; gt; However, I now need to produce a monthly report to check how many
requests
gt; gt; gt; have been assigned in a particular month. I've tried:
gt; gt; gt;
gt; gt;
=SUM(COUNTIF(Tracker!$G$2:$G$2000,A6)-(Countif(Tracker!$H$2:$H$2000,gt;MONTH($
gt; gt; C$1)) (Countif((Tracker!$H$2:$H$2000)lt;MONTH($F$1)) )
gt; gt; gt; where Colomn H is the date when request was allocated and C1 and F1
are
gt; gt; the
gt; gt; gt; perimetres for the report (ie start and finish dates)
gt; gt; gt;
gt; gt; gt; It looks like the Countif function doesn't like the quot;lt;quot; or quot;gt;quot; in the
gt; gt; gt; criteria. Does anyone have a solution?
gt; gt; gt;
gt; gt; gt; Many thanks!
gt; gt; gt; Vibeke
gt; gt;
gt; gt;
gt; gt;
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.

quot;Bob Phillipsquot; wrote:

gt; The formula does what you want, or would do without my typo
gt;
gt; =SUMPRODUCT(--(Tracker!$G$2:$G$2000=A6),
gt; --(Tracker!$H$2:$H$2000gt;MONTH($C$1)),
gt; --(Tracker!$H$2:$H$2000lt;MONTH($F$1)))
gt;
gt; Is H2:H2000 month numbers or true dates, this formula checks month numbers?
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;Vibekequot; gt; wrote in message
gt; ...
gt; gt; Hi Bob, and many thanks for your time. However, your suggestion returns
gt; gt; #VALUE!.
gt; gt;
gt; gt; I've toyed with variations on SUMPRODUCT, but am not sure it is the
gt; function
gt; gt; I need. I'm looking for a formula that will count all the instances of
gt; gt; 'Vibeke' (or A6) in Tracker-Column G, and then subtract from that total
gt; any
gt; gt; instances that don't occur in a particular month (Tracker-Column H,
gt; d-mmm-yy
gt; gt; format) - hence my attempt to COUNTIF(Vibeke) minus COUNTIF(doesn't occur
gt; in
gt; gt; end/start dates)
gt; gt;
gt; gt; My apologies for not articulating my query clearly.
gt; gt; Regards,
gt; gt; Vibeke
gt; gt;
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; =SUMPRODUCT(--(Tracker!$G$2:$G$2000,A6),
gt; gt; gt; --(Tracker!$H$2:$H$2000gt;MONTH($C$1)),
gt; gt; gt; --(Tracker!$H$2:$H$2000lt;MONTH($F$1)))
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from the email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Vibekequot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; I am trying to isolate data in a spreadsheet used to track requests
gt; for
gt; gt; gt; gt; information. To produce cummulative report, I have used the formula
gt; gt; gt; gt; =COUNTIF(Tracker!$G$2:$G$2000,A2)
gt; gt; gt; gt; where Column G has the person to whom the request has been assigned,
gt; and
gt; gt; gt; gt; A2:A12 are the names of the person. This provides me with a running
gt; total
gt; gt; gt; gt; throughout the year.
gt; gt; gt; gt; However, I now need to produce a monthly report to check how many
gt; requests
gt; gt; gt; gt; have been assigned in a particular month. I've tried:
gt; gt; gt; gt;
gt; gt; gt;
gt; =SUM(COUNTIF(Tracker!$G$2:$G$2000,A6)-(Countif(Tracker!$H$2:$H$2000,gt;MONTH($
gt; gt; gt; C$1)) (Countif((Tracker!$H$2:$H$2000)lt;MONTH($F$1)) )
gt; gt; gt; gt; where Colomn H is the date when request was allocated and C1 and F1
gt; are
gt; gt; gt; the
gt; gt; gt; gt; perimetres for the report (ie start and finish dates)
gt; gt; gt; gt;
gt; gt; gt; gt; It looks like the Countif function doesn't like the quot;lt;quot; or quot;gt;quot; in the
gt; gt; gt; gt; criteria. Does anyone have a solution?
gt; gt; gt; gt;
gt; gt; gt; gt; Many thanks!
gt; gt; gt; gt; Vibeke
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;

THIS WORKS!!=SUMPRODUCT(--(Tracker!$G$2:$G$2000=A6),--(Tracker!$H$2:$H$2000gt;($C$1)),--(Tracker!$H$2:$H$2000lt;($F$1)))

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

Vibeke

quot;Bob Phillipsquot; wrote:

gt; The formula does what you want, or would do without my typo
gt;
gt; =SUMPRODUCT(--(Tracker!$G$2:$G$2000=A6),
gt; --(Tracker!$H$2:$H$2000gt;MONTH($C$1)),
gt; --(Tracker!$H$2:$H$2000lt;MONTH($F$1)))
gt;
gt; Is H2:H2000 month numbers or true dates, this formula checks month numbers?
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;Vibekequot; gt; wrote in message
gt; ...
gt; gt; Hi Bob, and many thanks for your time. However, your suggestion returns
gt; gt; #VALUE!.
gt; gt;
gt; gt; I've toyed with variations on SUMPRODUCT, but am not sure it is the
gt; function
gt; gt; I need. I'm looking for a formula that will count all the instances of
gt; gt; 'Vibeke' (or A6) in Tracker-Column G, and then subtract from that total
gt; any
gt; gt; instances that don't occur in a particular month (Tracker-Column H,
gt; d-mmm-yy
gt; gt; format) - hence my attempt to COUNTIF(Vibeke) minus COUNTIF(doesn't occur
gt; in
gt; gt; end/start dates)
gt; gt;
gt; gt; My apologies for not articulating my query clearly.
gt; gt; Regards,
gt; gt; Vibeke
gt; gt;
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; =SUMPRODUCT(--(Tracker!$G$2:$G$2000,A6),
gt; gt; gt; --(Tracker!$H$2:$H$2000gt;MONTH($C$1)),
gt; gt; gt; --(Tracker!$H$2:$H$2000lt;MONTH($F$1)))
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from the email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Vibekequot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; I am trying to isolate data in a spreadsheet used to track requests
gt; for
gt; gt; gt; gt; information. To produce cummulative report, I have used the formula
gt; gt; gt; gt; =COUNTIF(Tracker!$G$2:$G$2000,A2)
gt; gt; gt; gt; where Column G has the person to whom the request has been assigned,
gt; and
gt; gt; gt; gt; A2:A12 are the names of the person. This provides me with a running
gt; total
gt; gt; gt; gt; throughout the year.
gt; gt; gt; gt; However, I now need to produce a monthly report to check how many
gt; requests
gt; gt; gt; gt; have been assigned in a particular month. I've tried:
gt; gt; gt; gt;
gt; gt; gt;
gt; =SUM(COUNTIF(Tracker!$G$2:$G$2000,A6)-(Countif(Tracker!$H$2:$H$2000,gt;MONTH($
gt; gt; gt; C$1)) (Countif((Tracker!$H$2:$H$2000)lt;MONTH($F$1)) )
gt; gt; gt; gt; where Colomn H is the date when request was allocated and C1 and F1
gt; are
gt; gt; gt; the
gt; gt; gt; gt; perimetres for the report (ie start and finish dates)
gt; gt; gt; gt;
gt; gt; gt; gt; It looks like the Countif function doesn't like the quot;lt;quot; or quot;gt;quot; in the
gt; gt; gt; gt; criteria. Does anyone have a solution?
gt; gt; gt; gt;
gt; gt; gt; gt; Many thanks!
gt; gt; gt; gt; Vibeke
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;