Help Please! CountIf Function using 2 criteria?

[AlexisLovesLife]

Allright...I tried to work through this using all the other posts as
references, but was unable to make it work for my situation.

I need to be able to "countif" Column D says "Detox" *AND* Column E says
"Client was admitted."

Or when Column D says "Rehab" *AND* Column E says "Client was not admitted."

Any help from anyone!? You guys make this stuff seem so simple! It's
*amazing* to me!

Thanks so much in advance!

 

[Gary''s Student]

Use SUMPRODUCT()

=SUMPRODUCT((D1100="Detox")*(E1:E100="Client was admitted"))

etc.

 

[Max]

To get the hang of it ..

.. to "countif" Column D says "Detox" *AND*
Column E says "Client was admitted."

=sumproduct((Col_D="Detox")*(Col_E="Client was admitted."))

.. Or when Column D says "Rehab" *AND*
Column E says "Client was not admitted."

=sumproduct((Col_D="Rehab")*(Col_E="Client was not admitted."))

Col_D, Col_E needs to be explicit ranges of equal size,
eg: D2100, E2:E100

And you can strap on more COUNTIF conditions beyond 2 conditions,
indicatively: =sumproduct((Cond1)*(Cond2)*(Cond3)*(Cond4))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---

 

[Ashish Mathur]

Hi,

You may also try using the following array formula (Ctrl+Shift+Enter)

=sum(if((range1="Detox")*(range2=Client was admitted"),1))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[ShaneDevenshire]

Hi,

If you are using 2007

=COUNTIFS(D$1$7,"Detox",E$1:E$7,"Client was admitted.")

If you are using 2003

=SUMPRODUCT(--(D$1$7=B$1),--(E$1:E$7=C$1))

where B1 contains "Detox" and C1 contains "Client was admitted." Both
without quotes. I could have used cell references in the 2007 formula, and
it is generally more flexible.