Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula that checks several criteria before returing the value??? | Excel Discussion (Misc queries) | |||
Counting Multiple Criteria | Excel Worksheet Functions | |||
COUNTIF statement with 3 criteria checks | Excel Discussion (Misc queries) | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions |