Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count numbers of entries
I need to count a numbers of entries in a given month and then I have to
compare with another set of entries that has to be greater than a certain month. I also has to count the numbers of empty cells. Can I use a nested IF function ? It looks like this : Date in Date finished 01-11-2004 03-01-2005 30-11-2004 I now want to calculate how many entries entered in November but not finished in November. I want this for every month of the year. Susanne |
#2
|
|||
|
|||
Hi Susanne,
Assuming that your data are in range [A2:B10] try the following: =SUMPRODUCT((MONTH(A2:A10)=11)*(YEAR(A2:A10)=2004) *((MONTH(B2:B10)<11)+(YEAR(B2:B10)<2004)0)) Regards, KL "Susanne" wrote in message ... I need to count a numbers of entries in a given month and then I have to compare with another set of entries that has to be greater than a certain month. I also has to count the numbers of empty cells. Can I use a nested IF function ? It looks like this : Date in Date finished 01-11-2004 03-01-2005 30-11-2004 I now want to calculate how many entries entered in November but not finished in November. I want this for every month of the year. Susanne |
#3
|
|||
|
|||
Hi KL
It does not work. Here is a little more of what my worksheet looks like A is the date when the project started and K is when it finished, if it is blank the project is not finished yet. A ........... K 1 20-01-2005 20-01-2005 2 21-01-2005 24-01-2005 3 21-01-2005 4 21-01-2005 5 21-01-2005 21-02-2005 6 27-01-2005 01-02-2005 7 31-01-2005 25-02-2005 8 02-02-2005 02-02-2005 9 04-02-2005 10 28-02-2005 01-03-2005 I want to know the numbers of projects started in January but not finished in January, the numbers of projects started in January and finished in January, the numbers of projects started in February but not finished in February and last the numbers of projects started in February and finished in February. Susanne "KL" wrote: Hi Susanne, Assuming that your data are in range [A2:B10] try the following: =SUMPRODUCT((MONTH(A2:A10)=11)*(YEAR(A2:A10)=2004) *((MONTH(B2:B10)<11)+(YEAR(B2:B10)<2004)0)) Regards, KL "Susanne" wrote in message ... I need to count a numbers of entries in a given month and then I have to compare with another set of entries that has to be greater than a certain month. I also has to count the numbers of empty cells. Can I use a nested IF function ? It looks like this : Date in Date finished 01-11-2004 03-01-2005 30-11-2004 I now want to calculate how many entries entered in November but not finished in November. I want this for every month of the year. Susanne |
#4
|
|||
|
|||
Hi Susanne,
It does not work. What do you mean it does not work? What error do you get? Here is a little more of what my worksheet looks like One possible cause of the problem I see is that have totally chaged both the data and the conditions since your last post: The sample data provided previously showed: 1) Dates in 2004 Vs. 2005 in the new example 2) Date Start & Date Finish looked like contiguous columns Vs. [A] & [K] in the new example 3) The condition was to find dates in November Vs. January in the new example. Obviously, the formula needs to be adjusted to these changes. Based on the new sample, try the following formula to produce the count for dates in January. =SUMPRODUCT((MONTH($A$2:$A$10)=1)*(YEAR($A$2:$A$10 )=2005)*((MONTH($K$2:$K$10)<1)+(YEAR($K$2:$K$10)< 2005)0)) If you want to do the same for several months/years just substitute the values with the reference to cells that contain the parameters: =SUMPRODUCT((MONTH($A$2:$A$10)=A14)*(YEAR($A$2:$A$ 10)=B14)*((MONTH($K$2:$K$10)<A14)+(YEAR($K$2:$K$1 0)<B14)0)) where [A14] contains the number of month and [B14] the year. Noow you can fill the cells from [A15] to [A25] with rest of month numbers, [B15] to [B25] with the year and copy the formula down. Regards, KL |
#5
|
|||
|
|||
Hi KL
It works now. Thanks. By the way my first data was just an ex. and I tried to use your tip according to my own data but that did not come out right. Susanne "KL" wrote: Hi Susanne, It does not work. What do you mean it does not work? What error do you get? Here is a little more of what my worksheet looks like One possible cause of the problem I see is that have totally chaged both the data and the conditions since your last post: The sample data provided previously showed: 1) Dates in 2004 Vs. 2005 in the new example 2) Date Start & Date Finish looked like contiguous columns Vs. [A] & [K] in the new example 3) The condition was to find dates in November Vs. January in the new example. Obviously, the formula needs to be adjusted to these changes. Based on the new sample, try the following formula to produce the count for dates in January. =SUMPRODUCT((MONTH($A$2:$A$10)=1)*(YEAR($A$2:$A$10 )=2005)*((MONTH($K$2:$K$10)<1)+(YEAR($K$2:$K$10)< 2005)0)) If you want to do the same for several months/years just substitute the values with the reference to cells that contain the parameters: =SUMPRODUCT((MONTH($A$2:$A$10)=A14)*(YEAR($A$2:$A$ 10)=B14)*((MONTH($K$2:$K$10)<A14)+(YEAR($K$2:$K$1 0)<B14)0)) where [A14] contains the number of month and [B14] the year. Noow you can fill the cells from [A15] to [A25] with rest of month numbers, [B15] to [B25] with the year and copy the formula down. Regards, KL |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions | |||
Count and Sum Total occurrances of two specific numbers | Excel Worksheet Functions | |||
count a group of numbers but do not count duplicates | Excel Worksheet Functions | |||
count entries within a row | Excel Worksheet Functions | |||
count data but avoid double entries | Excel Worksheet Functions |