IF, COUNTIF & AND STATEMENTS

M

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.
 
M

Mackem

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


Mackem said:
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
 
D

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
 
M

Mackem

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

Cheers
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top