CountIF Formula

[Guest]

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,

 

[Biff]

Hi!

Try this:

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

Biff

 

[smw226 via OfficeKB.com]

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

 

[Bernard Liengme]

=sumproduct(--(D2146="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)

 

[Guest]

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

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

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

Thanks a Bunch!!

 

[Carim]

Hi,


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

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

Carim

 

[Biff]

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