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.

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.

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.

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.