I currently have a spreadsheet were Column D is set to Status and Column M is
set to "YES" or "NO." Column D shows me if a Class is OPEN, CLOSED, OR
PENDING and Column M shows me if the Class is Required "YES" "NO"

I want to count the number of classes that are open and are not required.
Which means column D set to "OPEN" and Column M set to "NO"

I tried this formula but it is counting all the "NO" in column M:
=IF(AND(D2="open",M2="no"),COUNTIF(M2:M146,"no")," ")

I need a formula that will count between column D and M based on the
information.

Any Ideas?????

Thanks,

Hi!

Try this:

=SUMPRODUCT(--(D2:D146="open"),--(M2:M146="no"))

Biff

"Marilyn" wrote in message
...
I currently have a spreadsheet were Column D is set to Status and Column M
is
set to "YES" or "NO." Column D shows me if a Class is OPEN, CLOSED, OR
PENDING and Column M shows me if the Class is Required "YES" "NO"

I want to count the number of classes that are open and are not required.
Which means column D set to "OPEN" and Column M set to "NO"

I tried this formula but it is counting all the "NO" in column M:
=IF(AND(D2="open",M2="no"),COUNTIF(M2:M146,"no")," ")

I need a formula that will count between column D and M based on the
information.

Any Ideas?????

Thanks,

HI Marilyn,

In your first empy comumn enter the following:

=(D2&M2)

Then paste it down to the bottom of your data

Then, I will assume you have used column N,

=countif(Nx:Ny,"Open No")

Where x is the first rom and y the last.

Thanks,

Simon


Marilyn wrote:
I currently have a spreadsheet were Column D is set to Status and Column M is
set to "YES" or "NO." Column D shows me if a Class is OPEN, CLOSED, OR
PENDING and Column M shows me if the Class is Required "YES" "NO"

I want to count the number of classes that are open and are not required.
Which means column D set to "OPEN" and Column M set to "NO"

I tried this formula but it is counting all the "NO" in column M:
=IF(AND(D2="open",M2="no"),COUNTIF(M2:M146,"no"), "")

I need a formula that will count between column D and M based on the
information.

Any Ideas?????

Thanks,

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200610/1

=sumproduct(--(D2:D146="open"),--(M2:M146="no"))
see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

happy Thanksgiving (in the true north)
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Marilyn" wrote in message
...
I currently have a spreadsheet were Column D is set to Status and Column M
is
set to "YES" or "NO." Column D shows me if a Class is OPEN, CLOSED, OR
PENDING and Column M shows me if the Class is Required "YES" "NO"

I want to count the number of classes that are open and are not required.
Which means column D set to "OPEN" and Column M set to "NO"

I tried this formula but it is counting all the "NO" in column M:
=IF(AND(D2="open",M2="no"),COUNTIF(M2:M146,"no")," ")

I need a formula that will count between column D and M based on the
information.

Any Ideas?????

Thanks,

It seemed to have worked!!!! Thank you so much!!!

Can you explain to me what the (-- means in the formula?

=SUMPRODUCT(--(D2:D146="open"),--(M2:M146="no"))

Thanks a Bunch!!


"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(D2:D146="open"),--(M2:M146="no"))

Biff

"Marilyn" wrote in message
...
I currently have a spreadsheet were Column D is set to Status and Column M
is
set to "YES" or "NO." Column D shows me if a Class is OPEN, CLOSED, OR
PENDING and Column M shows me if the Class is Required "YES" "NO"

I want to count the number of classes that are open and are not required.
Which means column D set to "OPEN" and Column M set to "NO"

I tried this formula but it is counting all the "NO" in column M:
=IF(AND(D2="open",M2="no"),COUNTIF(M2:M146,"no")," ")

I need a formula that will count between column D and M based on the
information.

Any Ideas?????

Thanks,

Hi,


Best explanation of the double unary operator is available at Bob's
site :

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Carim

You're welcome. Thanks for the feedback!

Can you explain to me what the (-- means in the formula?

See the link posted by Carim for a detailed explanation.

Biff

"Marilyn" wrote in message
...
It seemed to have worked!!!! Thank you so much!!!

Can you explain to me what the (-- means in the formula?

=SUMPRODUCT(--(D2:D146="open"),--(M2:M146="no"))

Thanks a Bunch!!


"Biff" wrote:

Hi!

Try this:

=SUMPRODUCT(--(D2:D146="open"),--(M2:M146="no"))

Biff

"Marilyn" wrote in message
...
I currently have a spreadsheet were Column D is set to Status and Column
M
is
set to "YES" or "NO." Column D shows me if a Class is OPEN, CLOSED, OR
PENDING and Column M shows me if the Class is Required "YES" "NO"

I want to count the number of classes that are open and are not
required.
Which means column D set to "OPEN" and Column M set to "NO"

I tried this formula but it is counting all the "NO" in column M:
=IF(AND(D2="open",M2="no"),COUNTIF(M2:M146,"no")," ")

I need a formula that will count between column D and M based on the
information.

Any Ideas?????

Thanks,