IF, COUNTIF & AND STATEMENTS

[Mackem]

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.

 

[Mackem]

Didn't look right when I posted it so hopefully this will be easier to
understand

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

 

[daddylonglegs]

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]

Thanks, it worked. I was told you weren't able to wrap countif statements
within another statement. Will feed it back.

Cheers