Count where criteria in 2 columns are met

  • Thread starter Thread starter paulquinlan100
  • Start date Start date
P

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
 
=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)
 
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.
 
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
 
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
 
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
 
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)
 
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
 
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
 
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)
 
Back
Top