Formula which checks multiple criteria before counting

[JHolmes]

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.

 

[RagDyer]

Try this:

=Sumproduct((N1 :N100>Today())*(BM1:Bm100="Yes"))

Adjust your ranges as needed.

 

[Tyro]

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:N100>TODAY()),--(BM1:BM100="yes"))

Tyro

 

[JHolmes]

Yes thanks heaps this worked

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:N100>TODAY()),--(BM1:BM100="yes"))

Tyro

 

[Tyro]

You're welcome

Tyro

Yes thanks heaps this worked

 

[JHolmes]

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]

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]

=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

 

[Pete_UK]

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

 

[Pete_UK]

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

Pete

 

[Tyro]

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