Count Cells if diff cell meets conditions

D

DJS

Hello~
I need to count all rows of Column B if they are not blank and only if the
contents of the same row in column A are >=2005.
Is this possible without the use of a macro, and if so could you provide me
an example?
 
B

Bob Phillips

=SUMPRODUCT(--(NOT(ISBLANK(B1:B1000))),--(A1:A1000>2005))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
D

DJS

Thanks Bob~
I tried the following formula but I get a #NUM! error and can't seem to
figure out what I am doing wrong. Here is waht I have so far:
=SUMPRODUCT(--(NOT(ISBLANK('OBD-only'!FL:FL))),--('OBD-only'!AF:AF>=2005))

Column FL of the OBD-only sheet has most fields populated with a 17
character alpha-numeric val and the other cells are blank.

Column AF of the OBD-only sheet has all fields populated with years ranging
between 1996-2006.
 
B

Bob Phillips

SUMPRODUCT does not permit full columns, you must specify a range

=SUMPRODUCT(--(NOT(ISBLANK('OBD-only'!FL1:FL10000))),--('OBD-only'!AF1:AF100
00>=2005))

for example

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
D

DJS

Thanks Bob, that fixed it.
Much appreciated!

Bob Phillips said:
SUMPRODUCT does not permit full columns, you must specify a range

=SUMPRODUCT(--(NOT(ISBLANK('OBD-only'!FL1:FL10000))),--('OBD-only'!AF1:AF100
00>=2005))

for example

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
Click to expand...
 
Top