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?

Check out Chip Pearson's website on Array Formulas....
http://www.cpearson.com/excel/array.htm

He addresses multiple criteria Count Ifs and Sum Ifs.

I believe this is exactly what you are looking for.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"DJS" wrote:

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?

=SUMPRODUCT(--(NOT(ISBLANK(B1:B1000))),--(A1:A10002005))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DJS" wrote in message
...
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?

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.


"Bob Phillips" wrote:

=SUMPRODUCT(--(NOT(ISBLANK(B1:B1000))),--(A1:A10002005))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DJS" wrote in message
...
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?

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)

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


"Bob Phillips" wrote:

=SUMPRODUCT(--(NOT(ISBLANK(B1:B1000))),--(A1:A10002005))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DJS" wrote in message
...
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?

Thanks Bob, that fixed it.
Much appreciated!

"Bob Phillips" wrote:

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)

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


"Bob Phillips" wrote:

=SUMPRODUCT(--(NOT(ISBLANK(B1:B1000))),--(A1:A10002005))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DJS" wrote in message
...
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?