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