Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF, COUNTIF & AND STATEMENTS
I have a COUNTIF formula which is searching for the number of activities
which occur between 2 dates. This formulae works but I need to add to it. I have a reporting date (which is variable), I need the addition to only do the existing COUNTIF if the date above it it equal to or less than the reporting date and if not return a value of 0. This sounds simple and to be honest probably is but just cant seem to get it to work. Example below: May need to copy out the example and paste into excel. reporting date 28/09/2007 29/06/2007 27/07/2007 31/08/2007 28/09/2007 26/10/2007 30/11/2007 con a 7 2 0 1 1 con b 0 0 6 27 13 con c 4 19 28 4 22 con d 29 104 39 121 77 con e 44 45 32 79 5 con f 3 1 1 0 0 the existing COUNTIF created the values aove. =COUNTIF($K$3:$K$2170,""&V1)-COUNTIF($K$3:$K$2170,""&W1) where K3:K2170 is the range to search , v1 is 29/06/2007 and w1 is 27/07/2007. Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF, COUNTIF & AND STATEMENTS
Didn't look right when I posted it so hopefully this will be easier to
understand "Mackem" wrote: I have a COUNTIF formula which is searching for the number of activities which occur between 2 dates. This formulae works but I need to add to it. I have a reporting date (which is variable), I need the addition to only do the existing COUNTIF if the date above it it equal to or less than the reporting date and if not return a value of 0. This sounds simple and to be honest probably is but just cant seem to get it to work. Example below: May need to copy out the example and paste into excel. reporting date 28/09/2007 29/06/2007 27/07/2007 31/08/2007 28/09/2007 26/10/2007 30/11/2007 con a 7 2 0 1 1 con b 0 0 6 27 13 con c 4 19 28 4 22 con d 29 104 39 121 77 con e 44 45 32 79 5 con f 3 1 1 0 0 the existing COUNTIF created the values aove. =COUNTIF($K$3:$K$2170,""&V1)-COUNTIF($K$3:$K$2170,""&W1) where K3:K2170 is the range to search , v1 is 29/06/2007 and w1 is 27/07/2007. Any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF, COUNTIF & AND STATEMENTS
I'm not sure which date you mean when you say "the date above it" but it
sunds like you can just wrap your existing COUNTIF in an IF function, i.e. =IF(A1<=$B$1,your_original_formula,0) where A1 is "the date above it" and B1 your reporting date "Mackem" wrote: Didn't look right when I posted it so hopefully this will be easier to understand "Mackem" wrote: I have a COUNTIF formula which is searching for the number of activities which occur between 2 dates. This formulae works but I need to add to it. I have a reporting date (which is variable), I need the addition to only do the existing COUNTIF if the date above it it equal to or less than the reporting date and if not return a value of 0. This sounds simple and to be honest probably is but just cant seem to get it to work. Example below: May need to copy out the example and paste into excel. reporting date 28/09/2007 29/06/2007 27/07/2007 31/08/2007 28/09/2007 26/10/2007 30/11/2007 con a 7 2 0 1 1 con b 0 0 6 27 13 con c 4 19 28 4 22 con d 29 104 39 121 77 con e 44 45 32 79 5 con f 3 1 1 0 0 the existing COUNTIF created the values aove. =COUNTIF($K$3:$K$2170,""&V1)-COUNTIF($K$3:$K$2170,""&W1) where K3:K2170 is the range to search , v1 is 29/06/2007 and w1 is 27/07/2007. Any help would be greatly appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF, COUNTIF & AND STATEMENTS
Thanks, it worked. I was told you weren't able to wrap countif statements
within another statement. Will feed it back. Cheers "daddylonglegs" wrote: I'm not sure which date you mean when you say "the date above it" but it sunds like you can just wrap your existing COUNTIF in an IF function, i.e. =IF(A1<=$B$1,your_original_formula,0) where A1 is "the date above it" and B1 your reporting date "Mackem" wrote: Didn't look right when I posted it so hopefully this will be easier to understand "Mackem" wrote: I have a COUNTIF formula which is searching for the number of activities which occur between 2 dates. This formulae works but I need to add to it. I have a reporting date (which is variable), I need the addition to only do the existing COUNTIF if the date above it it equal to or less than the reporting date and if not return a value of 0. This sounds simple and to be honest probably is but just cant seem to get it to work. Example below: May need to copy out the example and paste into excel. reporting date 28/09/2007 29/06/2007 27/07/2007 31/08/2007 28/09/2007 26/10/2007 30/11/2007 con a 7 2 0 1 1 con b 0 0 6 27 13 con c 4 19 28 4 22 con d 29 104 39 121 77 con e 44 45 32 79 5 con f 3 1 1 0 0 the existing COUNTIF created the values aove. =COUNTIF($K$3:$K$2170,""&V1)-COUNTIF($K$3:$K$2170,""&W1) where K3:K2170 is the range to search , v1 is 29/06/2007 and w1 is 27/07/2007. Any help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use nested countif statements? | Excel Worksheet Functions | |||
INDEX / MATCH with SUMIF/COUNTIF/SUMPRODUCT Statements? | Excel Worksheet Functions | |||
IF Statements (Mutliple Statements) | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions |