Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates of a Day for a month & year cell formulas | Excel Discussion (Misc queries) | |||
Counting dates for a the present month but not future months | Excel Worksheet Functions | |||
countdown dates by month | Excel Worksheet Functions | |||
count no. of dates in a column that falls on certain month & year | Excel Worksheet Functions | |||
How do I sort dates stored as 1-Jun-05 in order of day & month | Excel Worksheet Functions |