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:BB1000>1/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)

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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:BB1000>1/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
>

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)

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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:BB1000>1/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" <bob....@somewhere.com> 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)
>
> <paulquinlan...@hotmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > 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:BB1000>1/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:BB1000>DATE(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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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" <bob....@somewhere.com> 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)
>
> <paulquinlan...@hotmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > 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:BB1000>1/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)

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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" <bob....@somewhere.com> 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)
>
> <paulquinlan...@hotmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > 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:BB1000>1/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
news:(E-Mail Removed)...
> The safest way would be to use an unambiguous definition of the date, so
> =SUMPRODUCT(--(Blackbook!BB3:BB1000>DATE(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
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> 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" <bob....@somewhere.com> 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)
>>
>> <paulquinlan...@hotmail.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>>
>>
>> > 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:BB1000>1/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

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> news:(E-Mail Removed)...
>> The safest way would be to use an unambiguous definition of the date, so
>> =SUMPRODUCT(--(Blackbook!BB3:BB1000>DATE(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
>>
>> <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> 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" <bob....@somewhere.com> 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)
>>>
>>> <paulquinlan...@hotmail.com> wrote in message
>>>
>>> news:(E-Mail Removed)...
>>>
>>>
>>>
>>> > 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:BB1000>1/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 -
>>
>>
>>
>
>
>

> 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

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" <(E-Mail Removed)> wrote in message
news:%23u98%(E-Mail Removed)...
> 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
>
> (E-Mail Removed)
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>> news:(E-Mail Removed)...
>>> The safest way would be to use an unambiguous definition of the date, so
>>> =SUMPRODUCT(--(Blackbook!BB3:BB1000>DATE(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
>>>
>>> <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>> 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" <bob....@somewhere.com> 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)
>>>>
>>>> <paulquinlan...@hotmail.com> wrote in message
>>>>
>>>> news:(E-Mail Removed)...
>>>>
>>>>
>>>>
>>>> > 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:BB1000>1/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 -
>>>
>>>
>>>
>>
>>
>>
>
>
>

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" <(E-Mail Removed)> wrote in message
news:%23u98%(E-Mail Removed)...
> 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
>
> (E-Mail Removed)
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>> news:(E-Mail Removed)...
>>> The safest way would be to use an unambiguous definition of the date, so
>>> =SUMPRODUCT(--(Blackbook!BB3:BB1000>DATE(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
>>>
>>> <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>> 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" <bob....@somewhere.com> 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)
>>>>
>>>> <paulquinlan...@hotmail.com> wrote in message
>>>>
>>>> news:(E-Mail Removed)...
>>>>
>>>>
>>>>
>>>> > 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:BB1000>1/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 -
>>>
>>>
>>>
>>
>>
>>
>
>