And(countif(

  • Thread starter Thread starter AdamPriest
  • Start date Start date
A

AdamPriest

Hi,

I am currently trying to use the AND operator as part of a countif
function and haven't been able to get it to work. I want to COUNT only
if a date is between 01/08/06 and 01/09/06, but can't work out how to.

My best attempt has been to use the AND operator to specify two
conditions (eg. is >01/08/06 and <01/09/06) but this hasn't worked.
Alternatively I have tried not using the AND operator at all and have
instead tried to create a 'between' function (eg. >01/08/06>01/09/06)
but this also didn't work.

To help explain further, here is the function I wrote which doesn't
work (I'm aware that the "*" symbol is wrong, but don't know what to do
to get both of these conditions to hold):

=AND(COUNTIF('Resource
Summary'!$H$2:$H$108,">01/08/06"))*(COUNTIF('Resource
Summary'!$H$2:$H$108,"<01/09/06"))

Any ideas? If I haven't been clear then please ask and I'll attempt to
explain further.

Thanks,

Adam. :)
 
Here is a sample solution:
=COUNTIF(A1:A26,">="&DATE(2006,1,8))-COUNTIF(A1:A26,">"&DATE(2006,1,9))
Note the &DATE
 
"AdamPriest" <[email protected]> ha
scritto nel messaggio
Hi,

I am currently trying to use the AND operator as part of a countif
function and haven't been able to get it to work. I want to COUNT only
if a date is between 01/08/06 and 01/09/06, but can't work out how to.

My best attempt has been to use the AND operator to specify two
conditions (eg. is >01/08/06 and <01/09/06) but this hasn't worked.
Alternatively I have tried not using the AND operator at all and have
instead tried to create a 'between' function (eg. >01/08/06>01/09/06)
but this also didn't work.

To help explain further, here is the function I wrote which doesn't
work (I'm aware that the "*" symbol is wrong, but don't know what to do
to get both of these conditions to hold):

=AND(COUNTIF('Resource
Summary'!$H$2:$H$108,">01/08/06"))*(COUNTIF('Resource
Summary'!$H$2:$H$108,"<01/09/06"))

Any ideas? If I haven't been clear then please ask and I'll attempt to
explain further.
Click to expand...


Hi Adam,


Try this:

=SUMPRODUCT(('Resource Summary'!$H$2:$H$108>VALUE("01/08/06")))*('Resource
Summary'!$H$2:$H$108<VALUE("01/09/06")))


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
This could be done by an array formula but you could follow this method
Find out the number value for the date.To do this rightclik->format
cells->general, a number would show up, right down the number and press
CANCEL.Do this to get the numbers for dates 1/8 and 1/9. The numbers
are for 1/8-38930 and for 1/9-38961.
Now ,insert a column and write the following formula
=AND(A1>38960,A1<38961).Drag the formula. Now in an another cell enter
the formula =countif(range,"true").Specify the range wher you have
entered the first formula. You would get the result. Though this is not
a optimized solution, this would do well.
Do take time to visit my new Excel blog under development
http://xlmaster.blogspot.com
 
Ray payette's formula should be
=COUNTIF(A1:A313,">="&DATE(2006,8,1))-COUNTIF(A1:A31,">"&DATE(2006,9,1))
 
Wow, thanks for the quick response guys. I went with Raypayette/ Mr
Cool's sample solution in the end and it worked (with a little dat
tweak)!

Now that I've done this, I have a further complexity which I'd like t
build in. I would like to only COUNT if the word "Services" is found i
a column which is 2 to the left of the date column. So in other word
COUNT IF DATE IN RANGE1 = BETWEEN 01/08/06 AND 01/09/06 AND COUNT DAT
IN RANGE 1 IF TEXT IN RANGE 2 = "SERVICES".

Below is the function as it currently stands. I need to build in th
second part (i.e. to only count if there is a word in a column 2 to th
left that says "Services"). Will I need a VLOOKUP or another I
statement?

=COUNTIF('Resource Summary'!H2:H108
">="&DATE(2006,8,1))-COUNTIF('Resource Summary'!H2:H108
">"&DATE(2006,8,31))

Thanks again!!

Adam
 
I would suggest using the sumproduct function for this
=sumproduct(--('Resource Summary'!H2:H108>=DATE(2006,8,1)),--('Resource
Summary'!H2:H108,>DATE(2006,8,31)),--('Resource Summary'!J2:J108="SERVICES"))

the "--(" changes the logical true false to a numeric "1,0"
 
Thanks BJ - that formula works but it doesn't do what I want it to do.
What it returns is all of the entries with "Services" which ARE NOT
between 01/08/06 and 31/08/06 (i.e. it counts the wrong thing because I
want it to return those that DO sit within these dates). It may be
something simple which needs to be changed to sort that out...advice!?

Cheers again.
 
"AdamPriest" <[email protected]> ha
scritto nel messaggio
Thanks BJ - that formula works but it doesn't do what I want it to do.
What it returns is all of the entries with "Services" which ARE NOT
between 01/08/06 and 31/08/06 (i.e. it counts the wrong thing because I
want it to return those that DO sit within these dates). It may be
something simple which needs to be changed to sort that out...advice!?

Cheers again.
Click to expand...



Hi Adam,


Try this:

=SUMPRODUCT(('Resource Summary'!$H$2:$H$108>VALUE("01/08/06")))*('Resource
Summary'!$H$2:$H$108<VALUE("01/09/06"))*('Resource
Summary'!J2:J108="SERVICES")))



--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
Hi Franz,

Thanks for the suggestion. Unfortunately this does not return the value
that I'm after still. Any other ideas?

Cheers,

Adam.
 
=sumproduct(--('Resource Summary'!H2:H108>=DATE(2006,8,1)),--('Resource
Summary'!H2:H108,<DATE(2006,8,31)),--('Resource Summary'!J2:J108="SERVICES"))

(I had a "<" set as a ">"
 
=sumproduct(--('Resource Summary'!H2:H108>=DATE(2006,8,1)),--('Resource
Summary'!H2:H108<DATE(2006,8,31)),--('Resource Summary'!J2:J108="SERVICES"))

also had a comma out of place
 
AdamPriest said:
Hi Franz,

Thanks for the suggestion. Unfortunately this does not return the
value that I'm after still. Any other ideas?

Cheers,

Adam.
Click to expand...


Hi Adam,

I think you could upload your file to www.savefile.com, so we can see why
the formula doesn't work properly...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
File available for dowloand here:

http://rapidshare.de/files/29831255/OrganisationDRAFT1.xls.html

Just to recap, in cell B7 on the Recruitment Scorecard sheet I want to
count only those instances where the date in column D of Resource
Summary is between 01/08/06 and 01/09/06 *and* where the corresponding
text in column B of Resource Summary is "Consulting". Thus giving me
the number of people in the consulting workforce who have a roll-off
date in August. I'll then want to replicate this across all months and
workforces but that shouldn't be a problem once the final function is
working.

BJ's best attempt is currently returning "0" which is incorrect as the
number is in fact 13.

=sumproduct(--('Resource
Summary'!H2:H108>=DATE(2006,8,1)),--('Resource
Summary'!H2:H108<DATE(2006,8,31)),--('Resource
Summary'!F2:F108="Consulting"))

Thanks! Hopefully we'll get there in the end!!

Adam.
 
AdamPriest said:
File available for dowloand here:

http://rapidshare.de/files/29831255/OrganisationDRAFT1.xls.html

Just to recap, in cell B7 on the Recruitment Scorecard sheet I want to
count only those instances where the date in column D of Resource
Summary is between 01/08/06 and 01/09/06 *and* where the corresponding
text in column B of Resource Summary is "Consulting". Thus giving me
the number of people in the consulting workforce who have a roll-off
date in August. I'll then want to replicate this across all months and
workforces but that shouldn't be a problem once the final function is
working.

BJ's best attempt is currently returning "0" which is incorrect as the
number is in fact 13.

=sumproduct(--('Resource
Summary'!H2:H108>=DATE(2006,8,1)),--('Resource
Summary'!H2:H108<DATE(2006,8,31)),--('Resource
Summary'!F2:F108="Consulting"))

Thanks! Hopefully we'll get there in the end!!

Adam.
Click to expand...

Hi Adam,

I've got it.

In my formula there was a wrong parenthesys...

But you cannot have 13 from any of the formula including also ('Resource
Summary'!F2:F108="Consulting"): 13 is the total number of people with a date
in August, then you have 9 Consulting, 2 Contractor and 2 Temporary, as you
can see here:

http://rapidshare.de/files/29848902/OrganisationDRAFT1.xls.html


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
=SUMPRODUCT(($G$1:$G$7>$K$1)*($H$1:$H$7="services"))-SUMPRODUCT(($G$1:$G$7<$K$3)*($H$1:$H$7="services"))-SUMPRODUCT(($G$1:$G$7>$K$2)*($H$1:$H$7="services"))

this works cus i tested it first

your dates are in col g
your word services occurs sometimes in column h

k1 k2 k3 contain dates
k1 is 1/1/03 (to catch all your dates in col g)
k2 is sept 1st
k3 is august 1st

in other words you count all dates with services in next column, and
then subtract all dates before august 1st that have services next to
them and then subtract all dates after September 1st that have services
next to them
 

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

Similar Threads

Counting Cells with Multiple Range Criteria (Excel 2003) 7
Count Dates between Dates exclude Text 3
KPI Data Chart Display 1
Countif Function 3
SUMIF Formula Help 8
UNABLE TO SORT DATE 1
Complicated Look-Up 1
macro to add numbers based on set criteria 6

Back
Top