Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,091
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,091
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,091
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,091
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula that checks several criteria before returing the value??? Apinun Excel Discussion (Misc queries) 4 September 19th 06 10:17 AM
Counting Multiple Criteria lmeg Excel Worksheet Functions 3 June 28th 06 06:40 PM
COUNTIF statement with 3 criteria checks Malvaro Excel Discussion (Misc queries) 4 June 4th 06 11:40 PM
Counting Cells with multiple criteria.One criteria supporting wild Azhar Saleem Excel Worksheet Functions 1 January 12th 05 11:54 AM
Counting Cells with multiple criteria.One criteria supporting wild Azhar Arain Excel Worksheet Functions 1 January 12th 05 09:33 AM


All times are GMT +1. The time now is 10:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"