repost: formula qhich checks mutliple criteria before counting
 

I was using the formula below:

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

and it doesnt actually only return the number of rows which have both a date
in the past in col N AND have a value of "yes" in col BM.
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...

repost: formula qhich checks mutliple criteria before counting
 

If you have empty cells in N1:N100 those would be counted if BM1:BM100 =
yes.

To prevent that:

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


--
Biff
Microsoft Excel MVP


"JHolmes" wrote in message
...
I was using the formula below:

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

and it doesnt actually only return the number of rows which have both a
date
in the past in col N AND have a value of "yes" in col BM.
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...

repost: formula qhich checks mutliple criteria before counting
 

Thanks heaps, have triple checked and this definitely fixes the prob. I had
blanks which were being counted.

Thanks heaps also Tyro :) thanks

"T. Valko" wrote:

If you have empty cells in N1:N100 those would be counted if BM1:BM100 =
yes.

To prevent that:

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


--
Biff
Microsoft Excel MVP


"JHolmes" wrote in message
...
I was using the formula below:

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

and it doesnt actually only return the number of rows which have both a
date
in the past in col N AND have a value of "yes" in col BM.
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...

repost: formula qhich checks mutliple criteria before counting
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JHolmes" wrote in message
...
Thanks heaps, have triple checked and this definitely fixes the prob. I
had
blanks which were being counted.

Thanks heaps also Tyro :) thanks

"T. Valko" wrote:

If you have empty cells in N1:N100 those would be counted if BM1:BM100 =
yes.

To prevent that:

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


--
Biff
Microsoft Excel MVP


"JHolmes" wrote in message
...
I was using the formula below:

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

and it doesnt actually only return the number of rows which have both a
date
in the past in col N AND have a value of "yes" in col BM.
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...