And(countif(

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

[raypayette]

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

[Franz Verga]

"AdamPriest" <[email protected]> ha
scritto nel messaggio
news:[email protected]...
>
> 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.
>


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

[Mr.Cools]

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

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

[Mr.Cools]

Ray payette's formula should be
=COUNTIF(A1:A313,">="&DATE(2006,8,1))-COUNTIF(A1:A31,">"&DATE(2006,9,1))

[AdamPriest]

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.

[bj]

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

[AdamPriest]

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.

[Franz Verga]

"AdamPriest" <[email protected]> ha
scritto nel messaggio
news:[email protected]...
>
> 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$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

[AdamPriest]

Hi Franz,

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

Cheers,

Adam.

[bj]

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

[bj]

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

[Franz Verga]

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

[AdamPriest]

File available for dowloand here:

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]

Bump!

Some bright spark must have the answer to this!?

[Franz Verga]

AdamPriest wrote:
> File available for dowloand here:
>
> 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 here:

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


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

[robert111]

=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