dates in one month

[Guest]

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

 

[Bob Phillips]

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

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]

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

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

 

[Guest]

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.

 

[Guest]

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