Hi

I need to fill a cell with a count of the number of rows that meet
certain criteria based on 2 columns.

e.g. i want to know the number of rows where Column1 01/01/2005 and
Column2 = "Site Dead"

Looking at previous posts i tried the following:

=SUMPRODUCT(--(Blackbook!BB3:BB10001/1/2005),--(Blackbook!
BM3:BM1000="Site Dead"))

However, i couldnt get this to work. I also tried using a Database
query which gave the correct result, but i need it to automatically
update when the data is altered.

Any ideas how i should go about this?

Thanks
Paul

=SUMPRODUCT(--(Blackbook!BB3:BB1000--"2005-01-01"),--(Blackbook!BM3:BM1000="Site
Dead"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message
ps.com...
Hi

I need to fill a cell with a count of the number of rows that meet
certain criteria based on 2 columns.

e.g. i want to know the number of rows where Column1 01/01/2005 and
Column2 = "Site Dead"

Looking at previous posts i tried the following:

=SUMPRODUCT(--(Blackbook!BB3:BB10001/1/2005),--(Blackbook!
BM3:BM1000="Site Dead"))

However, i couldnt get this to work. I also tried using a Database
query which gave the correct result, but i need it to automatically
update when the data is altered.

Any ideas how i should go about this?

Thanks
Paul

Thanks for that Bob, worked a treat. Just one more question, does the
date in that formula need to be in US or UK format?

Paul

On 13 Sep, 16:47, "Bob Phillips" wrote:
=SUMPRODUCT(--(Blackbook!BB3:BB1000--"2005-01-01"),--(Blackbook!BM3:BM1000*="Site
Dead"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message

ps.com...



Hi

I need to fill a cell with a count of the number of rows that meet
certain criteria based on 2 columns.

e.g. i want to know the number of rows where Column1 01/01/2005 and
Column2 = "Site Dead"

Looking at previous posts i tried the following:

=SUMPRODUCT(--(Blackbook!BB3:BB10001/1/2005),--(Blackbook!
BM3:BM1000="Site Dead"))

However, i couldnt get this to work. I also tried using a Database
query which gave the correct result, but i need it to automatically
update when the data is altered.

Any ideas how i should go about this?

Thanks
Paul- Hide quoted text -

- Show quoted text -

The safest way would be to use an unambiguous definition of the date, so
=SUMPRODUCT(--(Blackbook!BB3:BB1000DATE(2005,01,01)),--(Blackbook!BM3:BM1000*="Site
Dead"))
Otherwise if you use a construct like --"2005-01-01" I would expect it to
interpret the date according to the date format as defined in your Windows
Regional Options.
--
David Biddulph

wrote in message
oups.com...
Thanks for that Bob, worked a treat. Just one more question, does the
date in that formula need to be in US or UK format?

Paul

On 13 Sep, 16:47, "Bob Phillips" wrote:
=SUMPRODUCT(--(Blackbook!BB3:BB1000--"2005-01-01"),--(Blackbook!BM3:BM1000*="Site
Dead"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

ps.com...



Hi

I need to fill a cell with a count of the number of rows that meet
certain criteria based on 2 columns.

e.g. i want to know the number of rows where Column1 01/01/2005 and
Column2 = "Site Dead"

Looking at previous posts i tried the following:

=SUMPRODUCT(--(Blackbook!BB3:BB10001/1/2005),--(Blackbook!
BM3:BM1000="Site Dead"))

However, i couldnt get this to work. I also tried using a Database
query which gave the correct result, but i need it to automatically
update when the data is altered.

Any ideas how i should go about this?

Thanks
Paul- Hide quoted text -

- Show quoted text -

That is not so David, it is immaterial AFAIAA.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
The safest way would be to use an unambiguous definition of the date, so
=SUMPRODUCT(--(Blackbook!BB3:BB1000DATE(2005,01,01)),--(Blackbook!BM3:BM1000*="Site
Dead"))
Otherwise if you use a construct like --"2005-01-01" I would expect it to
interpret the date according to the date format as defined in your Windows
Regional Options.
--
David Biddulph

wrote in message
oups.com...
Thanks for that Bob, worked a treat. Just one more question, does the
date in that formula need to be in US or UK format?

Paul

On 13 Sep, 16:47, "Bob Phillips" wrote:
=SUMPRODUCT(--(Blackbook!BB3:BB1000--"2005-01-01"),--(Blackbook!BM3:BM1000*="Site
Dead"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

ps.com...



Hi

I need to fill a cell with a count of the number of rows that meet
certain criteria based on 2 columns.

e.g. i want to know the number of rows where Column1 01/01/2005 and
Column2 = "Site Dead"

Looking at previous posts i tried the following:

=SUMPRODUCT(--(Blackbook!BB3:BB10001/1/2005),--(Blackbook!
BM3:BM1000="Site Dead"))

However, i couldnt get this to work. I also tried using a Database
query which gave the correct result, but i need it to automatically
update when the data is altered.

Any ideas how i should go about this?

Thanks
Paul- Hide quoted text -

- Show quoted text -

Bob,

Just to clarify the matter, (for me as much as any one else), I assume that
you are saying that --"2005-01-01" is an unambiguous date because it is laid
out Year -Month - date but that --"10 - 1 - 2007" is ambiguous and so it is
not advisable to be used in workbooks other than your own.

Is that right or are you saying something else?

--
Regards,

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


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


"Bob Phillips" wrote in message
...
That is not so David, it is immaterial AFAIAA.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
The safest way would be to use an unambiguous definition of the date, so
=SUMPRODUCT(--(Blackbook!BB3:BB1000DATE(2005,01,01)),--(Blackbook!BM3:BM1000*="Site
Dead"))
Otherwise if you use a construct like --"2005-01-01" I would expect it to
interpret the date according to the date format as defined in your
Windows Regional Options.
--
David Biddulph

wrote in message
oups.com...
Thanks for that Bob, worked a treat. Just one more question, does the
date in that formula need to be in US or UK format?

Paul

On 13 Sep, 16:47, "Bob Phillips" wrote:
=SUMPRODUCT(--(Blackbook!BB3:BB1000--"2005-01-01"),--(Blackbook!BM3:BM1000*="Site
Dead"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

ps.com...



Hi

I need to fill a cell with a count of the number of rows that meet
certain criteria based on 2 columns.

e.g. i want to know the number of rows where Column1 01/01/2005 and
Column2 = "Site Dead"

Looking at previous posts i tried the following:

=SUMPRODUCT(--(Blackbook!BB3:BB10001/1/2005),--(Blackbook!
BM3:BM1000="Site Dead"))

However, i couldnt get this to work. I also tried using a Database
query which gave the correct result, but i need it to automatically
update when the data is altered.

Any ideas how i should go about this?

Thanks
Paul- Hide quoted text -

- Show quoted text -

I put it in ISO standard format so that it doesn't matter.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message
oups.com...
Thanks for that Bob, worked a treat. Just one more question, does the
date in that formula need to be in US or UK format?

Paul

On 13 Sep, 16:47, "Bob Phillips" wrote:
=SUMPRODUCT(--(Blackbook!BB3:BB1000--"2005-01-01"),--(Blackbook!BM3:BM1000*="Site
Dead"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

ps.com...



Hi

I need to fill a cell with a count of the number of rows that meet
certain criteria based on 2 columns.

e.g. i want to know the number of rows where Column1 01/01/2005 and
Column2 = "Site Dead"

Looking at previous posts i tried the following:

=SUMPRODUCT(--(Blackbook!BB3:BB10001/1/2005),--(Blackbook!
BM3:BM1000="Site Dead"))

However, i couldnt get this to work. I also tried using a Database
query which gave the correct result, but i need it to automatically
update when the data is altered.

Any ideas how i should go about this?

Thanks
Paul- Hide quoted text -

- Show quoted text -

I think you need to explicitly force Excel to recognize your "date" AS a
date, instead of peforming division.

Try this:
=SUMPRODUCT(--(Blackbook!BB3:BB1000(--"1/1/2005")),--(Blackbook!
BM3:BM1000="Site Dead"))

This part: (--"1/1/2005") performs an arithmetic operation on the string
"1/1/2005", causing Excel to convert the string to an actual date.

Post back if you have more questions.

--
Regards,

Ron (xl2003, Win Pro)
Microsoft MVP (Excel)

wrote in message
ps.com...
Hi

I need to fill a cell with a count of the number of rows that meet
certain criteria based on 2 columns.

e.g. i want to know the number of rows where Column1 01/01/2005 and
Column2 = "Site Dead"

Looking at previous posts i tried the following:

=SUMPRODUCT(--(Blackbook!BB3:BB10001/1/2005),--(Blackbook!
BM3:BM1000="Site Dead"))

However, i couldnt get this to work. I also tried using a Database
query which gave the correct result, but i need it to automatically
update when the data is altered.

Any ideas how i should go about this?

Thanks
Paul