Count where criteria in 2 columns are met
 

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

Count where criteria in 2 columns are met
 

=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

Count where criteria in 2 columns are met
 

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

Count where criteria in 2 columns are met
 

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 -

Count where criteria in 2 columns are met
 

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 -

Count where criteria in 2 columns are met
 

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 -

Count where criteria in 2 columns are met
 

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 -

Count where criteria in 2 columns are met
 

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 -

Count where criteria in 2 columns are met
 

out Year -Month - date

Should of course be:

Year -Month - day

--
HTH

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


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


"Sandy Mann" wrote in message
...
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 -

Count where criteria in 2 columns are met
 

Yeah Sandy, that is exactly what I am saying. I would say never, because you
never know when your own won't be <g

--
HTH

Bob

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

"Sandy Mann" wrote in message
...
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 -