repost: formula qhich checks mutliple criteria before counting

  • Thread starter Thread starter JHolmes
  • Start date Start date
J

JHolmes

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...
 
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"))
 
Thanks heaps, have triple checked and this definitely fixes the prob. I had
blanks which were being counted.

Thanks heaps also Tyro :) thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula which checks multiple criteria before counting 10
Number of rows over 30 days after creation 1
Cumulative total with date criteria 2
Help please with formula 2
How do I calculate FMLA hours used in the last rolling 12 months 2
cell returning totals of 2 cols+ sending this value to another col 5
Counting number of days excluding certain values 3
NESTED IF or LOOKUP formula? 3

Back
Top