| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct 3 criteria inc. date
byI have set up successful SUMPRODUCT to count the number of people in a
particular job, by depot, and where the 'leave date' column is empty. I would like to count the number of people in a particular job, by depot, by month. The value in the 'startdate' column is a date dd/mm/yyyy. I have tried to follow the answers given in previous posts, with no luck. Many thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct 3 criteria inc. date
If the dates are in column I, then create a "helper" column with formulas like:
=MONTH(I1) and then use this column in your SUMPRODUCT. -- Gary''s Student - gsnu200730 "D" wrote: byI have set up successful SUMPRODUCT to count the number of people in a particular job, by depot, and where the 'leave date' column is empty. I would like to count the number of people in a particular job, by depot, by month. The value in the 'startdate' column is a date dd/mm/yyyy. I have tried to follow the answers given in previous posts, with no luck. Many thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct 3 criteria inc. date
Thanks for speedy re;y! If I use the helper column, how do I get the
Sumproduct to filter out only one month? D "Gary''s Student" wrote: If the dates are in column I, then create a "helper" column with formulas like: =MONTH(I1) and then use this column in your SUMPRODUCT. -- Gary''s Student - gsnu200730 "D" wrote: byI have set up successful SUMPRODUCT to count the number of people in a particular job, by depot, and where the 'leave date' column is empty. I would like to count the number of people in a particular job, by depot, by month. The value in the 'startdate' column is a date dd/mm/yyyy. I have tried to follow the answers given in previous posts, with no luck. Many thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct 3 criteria inc. date
When I enter =MONTH(H3) the date in H3 shows as Jan-00 when the actual date
is 27/04/2007 - any ideas? "D" wrote: byI have set up successful SUMPRODUCT to count the number of people in a particular job, by depot, and where the 'leave date' column is empty. I would like to count the number of people in a particular job, by depot, by month. The value in the 'startdate' column is a date dd/mm/yyyy. I have tried to follow the answers given in previous posts, with no luck. Many thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct 3 criteria inc. date
=SUMPRODUCT(--(A2:A200="dept"),--(B2:B200="job"),--(TEXT(C2:C200,"mmm")="Jun"))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "D" wrote in message ... byI have set up successful SUMPRODUCT to count the number of people in a particular job, by depot, and where the 'leave date' column is empty. I would like to count the number of people in a particular job, by depot, by month. The value in the 'startdate' column is a date dd/mm/yyyy. I have tried to follow the answers given in previous posts, with no luck. Many thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct 3 criteria inc. date
That is because the result field is formatted as a date, day1 is 1st Jan
1900 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "D" wrote in message ... When I enter =MONTH(H3) the date in H3 shows as Jan-00 when the actual date is 27/04/2007 - any ideas? "D" wrote: byI have set up successful SUMPRODUCT to count the number of people in a particular job, by depot, and where the 'leave date' column is empty. I would like to count the number of people in a particular job, by depot, by month. The value in the 'startdate' column is a date dd/mm/yyyy. I have tried to follow the answers given in previous posts, with no luck. Many thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct 3 criteria inc. date
As always I am in awe of your knowledge...
I am getting a result, but not the correct one. I am now using a column for the start date which is dd/mm/yyyy and I need to filter out by month and year, ie.e find all starters for a job and depot for April 2007. Many, many thanks for your generous help. "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A200="dept"),--(B2:B200="job"),--(TEXT(C2:C200,"mmm")="Jun")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "D" wrote in message ... byI have set up successful SUMPRODUCT to count the number of people in a particular job, by depot, and where the 'leave date' column is empty. I would like to count the number of people in a particular job, by depot, by month. The value in the 'startdate' column is a date dd/mm/yyyy. I have tried to follow the answers given in previous posts, with no luck. Many thanks |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct 3 criteria inc. date
=SUMPRODUCT(--(A2:A200="dept"),--(B2:B200="job"),--(TEXT(C2:C200,"mmmyyyy")="Apr2007"))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "D" wrote in message ... As always I am in awe of your knowledge... I am getting a result, but not the correct one. I am now using a column for the start date which is dd/mm/yyyy and I need to filter out by month and year, ie.e find all starters for a job and depot for April 2007. Many, many thanks for your generous help. "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A200="dept"),--(B2:B200="job"),--(TEXT(C2:C200,"mmm")="Jun")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "D" wrote in message ... byI have set up successful SUMPRODUCT to count the number of people in a particular job, by depot, and where the 'leave date' column is empty. I would like to count the number of people in a particular job, by depot, by month. The value in the 'startdate' column is a date dd/mm/yyyy. I have tried to follow the answers given in previous posts, with no luck. Many thanks |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct 3 criteria inc. date
A thousand thanks - that works, even when the date cells are formatted
d/mmm/yyyy. D "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A200="dept"),--(B2:B200="job"),--(TEXT(C2:C200,"mmmyyyy")="Apr2007")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "D" wrote in message ... As always I am in awe of your knowledge... I am getting a result, but not the correct one. I am now using a column for the start date which is dd/mm/yyyy and I need to filter out by month and year, ie.e find all starters for a job and depot for April 2007. Many, many thanks for your generous help. "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A200="dept"),--(B2:B200="job"),--(TEXT(C2:C200,"mmm")="Jun")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "D" wrote in message ... byI have set up successful SUMPRODUCT to count the number of people in a particular job, by depot, and where the 'leave date' column is empty. I would like to count the number of people in a particular job, by depot, by month. The value in the 'startdate' column is a date dd/mm/yyyy. I have tried to follow the answers given in previous posts, with no luck. Many thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct and IF criteria?? | Excel Discussion (Misc queries) | |||
| SUMPRODUCT, two criteria (date and product type) | Excel Worksheet Functions | |||
| Sumproduct 4 criteria | Excel Worksheet Functions | |||
| SUMIF/SUMPRODUCT/IF - 2 criteria - Date and Text | Excel Discussion (Misc queries) | |||
| Sumproduct w/date criteria not working | Excel Worksheet Functions | |||
Linear Mode
