Help Please! CountIf Function using 2 criteria?

  • Thread starter Thread starter AlexisLovesLife
  • Start date Start date
A

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! :)
 
Use SUMPRODUCT()

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

etc.
 
To get the hang of it ..
.. to "countif" Column D says "Detox" *AND*
Column E says "Client was admitted."
Click to expand...

=sumproduct((Col_D="Detox")*(Col_E="Client was admitted."))
.. Or when Column D says "Rehab" *AND*
Column E says "Client was not admitted."
Click to expand...

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

Col_D, Col_E needs to be explicit ranges of equal size,
eg: D2:D100, 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
---
 
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
 
Hi,

If you are using 2007

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

If you are using 2003

=SUMPRODUCT(--(D$1:D$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.
 
Back
Top