Is there any way for a formula to check more than one criteria before
counting? So if I have dates in Column N - I have the formula to check that
date is past today's date, then in column BM I have a Yes or No value, and
for the formula to only count the row when it contains a "Yes" in BM? I have
tried using nested countif and doesnt seem to work.

Try this:

=Sumproduct((N1 :N100Today())*(BM1:Bm100="Yes"))

Adjust your ranges as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JHolmes" wrote in message
...
Is there any way for a formula to check more than one criteria before
counting? So if I have dates in Column N - I have the formula to check
that
date is past today's date, then in column BM I have a Yes or No value, and
for the formula to only count the row when it contains a "Yes" in BM? I
have
tried using nested countif and doesnt seem to work.

If your dates are in N1:N100 and the "yes", "no" values are in BM1:BM100
then if you mean by "past today's date" as in the past then

=SUMPRODUCT(--(N1:N100<TODAY()),--(BM1:BM100="yes"))

or if you mean the date is in the future then

=SUMPRODUCT(--(N1:N100TODAY()),--(BM1:BM100="yes"))

Tyro

"JHolmes" wrote in message
...
Is there any way for a formula to check more than one criteria before
counting? So if I have dates in Column N - I have the formula to check
that
date is past today's date, then in column BM I have a Yes or No value, and
for the formula to only count the row when it contains a "Yes" in BM? I
have
tried using nested countif and doesnt seem to work.

Yes thanks heaps this worked :)

"Tyro" wrote:

If your dates are in N1:N100 and the "yes", "no" values are in BM1:BM100
then if you mean by "past today's date" as in the past then

=SUMPRODUCT(--(N1:N100<TODAY()),--(BM1:BM100="yes"))

or if you mean the date is in the future then

=SUMPRODUCT(--(N1:N100TODAY()),--(BM1:BM100="yes"))

Tyro

"JHolmes" wrote in message
...
Is there any way for a formula to check more than one criteria before
counting? So if I have dates in Column N - I have the formula to check
that
date is past today's date, then in column BM I have a Yes or No value, and
for the formula to only count the row when it contains a "Yes" in BM? I
have
tried using nested countif and doesnt seem to work.

You're welcome

Tyro

"JHolmes" wrote in message
...
Yes thanks heaps this worked :)

"Tyro" wrote:

If your dates are in N1:N100 and the "yes", "no" values are in BM1:BM100
then if you mean by "past today's date" as in the past then

=SUMPRODUCT(--(N1:N100<TODAY()),--(BM1:BM100="yes"))

or if you mean the date is in the future then

=SUMPRODUCT(--(N1:N100TODAY()),--(BM1:BM100="yes"))

Tyro

"JHolmes" wrote in message
...
Is there any way for a formula to check more than one criteria before
counting? So if I have dates in Column N - I have the formula to check
that
date is past today's date, then in column BM I have a Yes or No value,
and
for the formula to only count the row when it contains a "Yes" in BM? I
have
tried using nested countif and doesnt seem to work.

I was using the top one from your answer:

=SUMPRODUCT(--(N1:N100<TODAY()),--(BM1:BM100="yes"))

and AGH sorry just realised it doesnt actually work - was just coincidence.
It is checking the date in column N correctly, but it is then returning the
total amount of times "Yes" is in BM - not just the amount of times that it
occurs in a row where the date (col N) is before today's date. Any idea how i
can restrict it to just where the date is in the past AND col BM ="Yes"?
thankx again...

"Tyro" wrote:

You're welcome

Tyro

"JHolmes" wrote in message
...
Yes thanks heaps this worked :)

"Tyro" wrote:

If your dates are in N1:N100 and the "yes", "no" values are in BM1:BM100
then if you mean by "past today's date" as in the past then

=SUMPRODUCT(--(N1:N100<TODAY()),--(BM1:BM100="yes"))

or if you mean the date is in the future then

=SUMPRODUCT(--(N1:N100TODAY()),--(BM1:BM100="yes"))

Tyro

"JHolmes" wrote in message
...
Is there any way for a formula to check more than one criteria before
counting? So if I have dates in Column N - I have the formula to check
that
date is past today's date, then in column BM I have a Yes or No value,
and
for the formula to only count the row when it contains a "Yes" in BM? I
have
tried using nested countif and doesnt seem to work.

That is exactly what the formula does. It returns the number of times the
date is in the past in column N AND the corresponding column BM entry =
"yes".
Are you sure you have dates in column N? Show me your formula as you entered
it.

Tyro

"JHolmes" wrote in message
...
I was using the top one from your answer:

=SUMPRODUCT(--(N1:N100<TODAY()),--(BM1:BM100="yes"))

and AGH sorry just realised it doesnt actually work - was just
coincidence.
It is checking the date in column N correctly, but it is then returning
the
total amount of times "Yes" is in BM - not just the amount of times that
it
occurs in a row where the date (col N) is before today's date. Any idea
how i
can restrict it to just where the date is in the past AND col BM ="Yes"?
thankx again...

"Tyro" wrote:

You're welcome

Tyro

"JHolmes" wrote in message
...
Yes thanks heaps this worked :)

"Tyro" wrote:

If your dates are in N1:N100 and the "yes", "no" values are in
BM1:BM100
then if you mean by "past today's date" as in the past then

=SUMPRODUCT(--(N1:N100<TODAY()),--(BM1:BM100="yes"))

or if you mean the date is in the future then

=SUMPRODUCT(--(N1:N100TODAY()),--(BM1:BM100="yes"))

Tyro

"JHolmes" wrote in message
...
Is there any way for a formula to check more than one criteria
before
counting? So if I have dates in Column N - I have the formula to
check
that
date is past today's date, then in column BM I have a Yes or No
value,
and
for the formula to only count the row when it contains a "Yes" in
BM? I
have
tried using nested countif and doesnt seem to work.