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/0601/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. :)


--
AdamPriest
------------------------------------------------------------------------
AdamPriest's Profile: http://www.excelforum.com/member.php...o&userid=37588
View this thread: http://www.excelforum.com/showthread...hreadid=572123

Here is a sample solution:
=COUNTIF(A1:A26,"="&DATE(2006,1,8))-COUNTIF(A1:A26,""&DATE(2006,1,9))
Note the &DATE


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=572123

"AdamPriest" 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/0601/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.



Hi Adam,


Try this:

=SUMPRODUCT(('Resource Summary'!$H$2:$H$108VALUE("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(A138960,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

AdamPriest wrote:
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/0601/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. :)


--
AdamPriest
------------------------------------------------------------------------
AdamPriest's Profile: http://www.excelforum.com/member.php...o&userid=37588
View this thread: http://www.excelforum.com/showthread...hreadid=572123

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 date
tweak)!

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

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

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

Thanks again!!

Adam.


--
AdamPriest
------------------------------------------------------------------------
AdamPriest's Profile: http://www.excelforum.com/member.php...o&userid=37588
View this thread: http://www.excelforum.com/showthread...hreadid=572123

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"


"AdamPriest" wrote:


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 date
tweak)!

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

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

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

Thanks again!!

Adam.


--
AdamPriest
------------------------------------------------------------------------
AdamPriest's Profile: http://www.excelforum.com/member.php...o&userid=37588
View this thread: http://www.excelforum.com/showthread...hreadid=572123

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
------------------------------------------------------------------------
AdamPriest's Profile: http://www.excelforum.com/member.php...o&userid=37588
View this thread: http://www.excelforum.com/showthread...hreadid=572123

"AdamPriest" 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.




Hi Adam,


Try this:

=SUMPRODUCT(('Resource Summary'!$H$2:$H$108VALUE("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.


--
AdamPriest
------------------------------------------------------------------------
AdamPriest's Profile: http://www.excelforum.com/member.php...o&userid=37588
View this thread: http://www.excelforum.com/showthread...hreadid=572123

=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 ""

"AdamPriest" wrote:


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
------------------------------------------------------------------------
AdamPriest's Profile: http://www.excelforum.com/member.php...o&userid=37588
View this thread: http://www.excelforum.com/showthread...hreadid=572123

=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

"bj" wrote:

=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 ""

"AdamPriest" wrote:


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
------------------------------------------------------------------------
AdamPriest's Profile: http://www.excelforum.com/member.php...o&userid=37588
View this thread: http://www.excelforum.com/showthread...hreadid=572123

AdamPriest wrote:
Hi Franz,

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

Cheers,

Adam.


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 he

http://rapidshare.de/files/29831255/...RAFT1.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
------------------------------------------------------------------------
AdamPriest's Profile: http://www.excelforum.com/member.php...o&userid=37588
View this thread: http://www.excelforum.com/showthread...hreadid=572123

Bump!

Some bright spark must have the answer to this!?


--
AdamPriest
------------------------------------------------------------------------
AdamPriest's Profile: http://www.excelforum.com/member.php...o&userid=37588
View this thread: http://www.excelforum.com/showthread...hreadid=572123

AdamPriest wrote:
File available for dowloand he

http://rapidshare.de/files/29831255/...RAFT1.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.

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 he

http://rapidshare.de/files/29848902/...RAFT1.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


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=572123