Formula which checks multiple criteria before counting
 

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.

Formula which checks multiple criteria before counting
 

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.

Formula which checks multiple criteria before counting
 

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.

Formula which checks multiple criteria before counting
 

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.

Formula which checks multiple criteria before counting
 

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.

Formula which checks multiple criteria before counting
 

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.

Formula which checks multiple criteria before counting
 

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.

Formula which checks multiple criteria before counting
 

=SUMPRODUCT(--('Outbound Correspondence'!N3:N100<TODAY()),--('Outbound
Correspondence'!BM3:BM100="Yes"))

Yes definitely have dates in col N.
There are 18 instances of "Yes" in col BM, and it returns 18, even when I
blank out one of the dates in col N (so it should return 17) it still returns
18.

Thanx again for your help

"Tyro" wrote:

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.

Formula which checks multiple criteria before counting
 

When you blank out one of those dates that cell is still less than
today's date so it will still be counted - a better test would be to
put a future date in one of the cells, or to test for blanks like
this:

=SUMPRODUCT(--('Outbound Correspondence'!N3:N100<TODAY()),--('Outbound
Correspondence'!N3:N100<""),--('Outbound Correspondence'!
BM3:BM100="Yes"))

Hope this helps.

Pete


On Feb 22, 4:52*am, JHolmes wrote:
=SUMPRODUCT(--('Outbound Correspondence'!N3:N100<TODAY()),--('Outbound
Correspondence'!BM3:BM100="Yes"))

Yes definitely have dates in col N.
There are 18 instances of "Yes" in col BM, and it returns 18, even when I
blank out one of the dates in col N (so it should return 17) it still returns
18.

Thanx again for your help



"Tyro" wrote:
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.- Hide quoted text -

- Show quoted text -

Formula which checks multiple criteria before counting
 

Ah, I see that you have re-posted elsewhere - mine is an alternative
to Biff's.

Pete

On Feb 22, 9:35*am, Pete_UK wrote:
When you blank out one of those dates that cell is still less than
today's date so it will still be counted - a better test would be to
put a future date in one of the cells, or to test for blanks like
this:

=SUMPRODUCT(--('Outbound Correspondence'!N3:N100<TODAY()),--('Outbound
Correspondence'!N3:N100<""),--('Outbound Correspondence'!
BM3:BM100="Yes"))

Hope this helps.

Pete

On Feb 22, 4:52*am, JHolmes wrote:



=SUMPRODUCT(--('Outbound Correspondence'!N3:N100<TODAY()),--('Outbound
Correspondence'!BM3:BM100="Yes"))

Yes definitely have dates in col N.
There are 18 instances of "Yes" in col BM, and it returns 18, even when I
blank out one of the dates in col N (so it should return 17) it still returns
18.

Thanx again for your help

"Tyro" wrote:
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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Formula which checks multiple criteria before counting
 

A blank date is treated as 0 which is Jan 0, 1900 and compares to be less
than TODAY() so it meets the criterion of
=SUMPRODUCT(--(N1:N100<TODAY()),--(BM1:BM100="yes")) . You said you have
dates in column N. You mentioned nothing about blanks.

Tyro



"JHolmes" wrote in message
...
=SUMPRODUCT(--('Outbound Correspondence'!N3:N100<TODAY()),--('Outbound
Correspondence'!BM3:BM100="Yes"))

Yes definitely have dates in col N.
There are 18 instances of "Yes" in col BM, and it returns 18, even when I
blank out one of the dates in col N (so it should return 17) it still
returns
18.

Thanx again for your help

"Tyro" wrote:

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.