Count where criteria in 2 columns are met

[paulquinlan100]

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

 

[Bob Phillips]

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

 

[Ron Coderre]

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.

 

[paulquinlan100]

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

 

[David Biddulph]

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

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

 

[Bob Phillips]

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)

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

 

[Bob Phillips]

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)

 

[Sandy Mann]

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 address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

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 address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Bob Phillips]

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)