Hi

if I want to count the number of rown where B4:B65535 31/07/06 and <
01/08/07 and C4:C65535 = "No" how would I express this please

Thanks
A

This should work:

=SUMPRODUCT((B4:B65535DATE(2006,7,31))*(B4:B65535 <DATE(2007,8,1))*(C4:C65535="Yes"))

Have a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a
full explanation.

Regards,
Darren.

"Alex H" wrote:

Hi

if I want to count the number of rown where B4:B65535 31/07/06 and <
01/08/07 and C4:C65535 = "No" how would I express this please

Thanks
A

OK, subject to changing Yes to No. :-)
--
David Biddulph

"Darren Bartrup" wrote in message
...
This should work:

=SUMPRODUCT((B4:B65535DATE(2006,7,31))*(B4:B65535 <DATE(2007,8,1))*(C4:C65535="Yes"))

Have a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a
full explanation.

Regards,
Darren.

"Alex H" wrote:

Hi

if I want to count the number of rown where B4:B65535 31/07/06 and <
01/08/07 and C4:C65535 = "No" how would I express this please

Thanks
A

Ok, give me a break it's Monday morning! :p
I wasn't far off though.

"David Biddulph" wrote:

OK, subject to changing Yes to No. :-)
--
David Biddulph

"Darren Bartrup" wrote in message
...
This should work:

=SUMPRODUCT((B4:B65535DATE(2006,7,31))*(B4:B65535 <DATE(2007,8,1))*(C4:C65535="Yes"))

Have a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a
full explanation.

Regards,
Darren.

"Alex H" wrote:

Hi

if I want to count the number of rown where B4:B65535 31/07/06 and <
01/08/07 and C4:C65535 = "No" how would I express this please

Thanks
A

=SUMPRODUCT((B4:B65535--("31/07/06"))*(B4:B65535<--("1/08/07"))*(C4:C65535
= "No"))
--
David Biddulph

"Alex H" wrote in message
...
Hi

if I want to count the number of rown where B4:B65535 31/07/06 and <
01/08/07 and C4:C65535 = "No" how would I express this please

Thanks
A

Hi David,

As Bob Philips often points out --("1/08/07") is ambiguous in that it can be
interpreted as August 1 2007 or January 8 2007 depending on your settings.
It is therefore better to use Date(2007,8,1) which, (as I am sure you
already know), works regardless of the regional setting you have

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=SUMPRODUCT((B4:B65535--("31/07/06"))*(B4:B65535<--("1/08/07"))*(C4:C65535
= "No"))
--
David Biddulph

"Alex H" wrote in message
...
Hi

if I want to count the number of rown where B4:B65535 31/07/06 and <
01/08/07 and C4:C65535 = "No" how would I express this please

Thanks
A

Thanks you both so very much -

A

"Sandy Mann" wrote in message
...
Hi David,

As Bob Philips often points out --("1/08/07") is ambiguous in that it can
be interpreted as August 1 2007 or January 8 2007 depending on your
settings. It is therefore better to use Date(2007,8,1) which, (as I am
sure you already know), works regardless of the regional setting you have

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=SUMPRODUCT((B4:B65535--("31/07/06"))*(B4:B65535<--("1/08/07"))*(C4:C65535
= "No"))
--
David Biddulph

"Alex H" wrote in message
...
Hi

if I want to count the number of rown where B4:B65535 31/07/06 and <
01/08/07 and C4:C65535 = "No" how would I express this please

Thanks
A