how do I count the numbers of row that meet 2 criteria

[Debi]

I am atempting to count how many occurances there are when two criteria are
met in the same row but different cells For example in row b there are
initials and in row j there are two different letter f and h. I would like
the total number of rows that have the initials DR in column b and the letter
f in column j. Sound simple but I can't seem to get it

[Duke Carey]

=sumproduct(--(B1:B1000="DR"),--(J1:J1000="f"))

"Debi" wrote:

> I am atempting to count how many occurances there are when two criteria are
> met in the same row but different cells For example in row b there are
> initials and in row j there are two different letter f and h. I would like
> the total number of rows that have the initials DR in column b and the letter
> f in column j. Sound simple but I can't seem to get it

[Debi]

Thanks for your assistance.
Woud you be able to clarify one thing for me thougjh? What do the double
minus sign represent (--)

"Duke Carey" wrote:

> =sumproduct(--(B1:B1000="DR"),--(J1:J1000="f"))
>
> "Debi" wrote:
>
> > I am atempting to count how many occurances there are when two criteria are
> > met in the same row but different cells For example in row b there are
> > initials and in row j there are two different letter f and h. I would like
> > the total number of rows that have the initials DR in column b and the letter
> > f in column j. Sound simple but I can't seem to get it

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(B:B100="DR"),--(J1:J100="F"))

Better:

A1 = DR
A2 = F

=SUMPRODUCT(--(B:B100=A1),--(J1:J100=A2))

Biff

"Debi" <[email protected]> wrote in message
news:[email protected]...
>I am atempting to count how many occurances there are when two criteria are
> met in the same row but different cells For example in row b there are
> initials and in row j there are two different letter f and h. I would
> like
> the total number of rows that have the initials DR in column b and the
> letter
> f in column j. Sound simple but I can't seem to get it

[Duke Carey]

Debi -

The portion of the formula that reads (B1:B1000="DR") will return an array
of TRUE and FALSE values. The -- operator converts the Trues to 1 and the
Falses to 0. Sumproduct then multiplies each element in the array by the
corresponding element in the other array, and sums the products. The factors
are all 1s and 0s, so the only ones that yield a non-zero product are the
ones where both logical tests are TRUE.

"Debi" wrote:

> Thanks for your assistance.
> Woud you be able to clarify one thing for me thougjh? What do the double
> minus sign represent (--)
>
> "Duke Carey" wrote:
>
> > =sumproduct(--(B1:B1000="DR"),--(J1:J1000="f"))
> >
> > "Debi" wrote:
> >
> > > I am atempting to count how many occurances there are when two criteria are
> > > met in the same row but different cells For example in row b there are
> > > initials and in row j there are two different letter f and h. I would like
> > > the total number of rows that have the initials DR in column b and the letter
> > > f in column j. Sound simple but I can't seem to get it