Count Cells if diff cell meets conditions

[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?

[Gary L Brown]

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
gary_brown@ge_NOSPAM.com
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?

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DJS" <DJS@discussions.microsoft.com> wrote in message
news:65A112B7-9DF7-479A-8306-2D7498EE75F7@microsoft.com...
> 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?

[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.


"Bob Phillips" wrote:

> =SUMPRODUCT(--(NOT(ISBLANK(B1:B1000))),--(A1:A1000>2005))
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "DJS" <DJS@discussions.microsoft.com> wrote in message
> news:65A112B7-9DF7-479A-8306-2D7498EE75F7@microsoft.com...
> > 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?
>
>
>

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

"DJS" <DJS@discussions.microsoft.com> wrote in message
news:DF74BE64-17F4-4882-B540-A3A16B76F840@microsoft.com...
> 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:A1000>2005))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "DJS" <DJS@discussions.microsoft.com> wrote in message
> > news:65A112B7-9DF7-479A-8306-2D7498EE75F7@microsoft.com...
> > > 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?
> >
> >
> >

[DJS]

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" <DJS@discussions.microsoft.com> wrote in message
> news:DF74BE64-17F4-4882-B540-A3A16B76F840@microsoft.com...
> > 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:A1000>2005))
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "DJS" <DJS@discussions.microsoft.com> wrote in message
> > > news:65A112B7-9DF7-479A-8306-2D7498EE75F7@microsoft.com...
> > > > 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?
> > >
> > >
> > >
>
>
>