Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Please! CountIf Function using 2 criteria?
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! :) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Please! CountIf Function using 2 criteria?
Use SUMPRODUCT()
=SUMPRODUCT((D1:D100="Detox")*(E1:E100="Client was admitted")) etc. -- Gary''s Student - gsnu200810 "AlexisLovesLife" wrote: 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! :) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Please! CountIf Function using 2 criteria?
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: 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 --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Please! CountIf Function using 2 criteria?
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 "AlexisLovesLife" wrote in message ... 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! :) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Please! CountIf Function using 2 criteria?
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. -- Thanks, Shane Devenshire "AlexisLovesLife" wrote: 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! :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using the CountIf function with multiple criteria? | Excel Worksheet Functions | |||
Two Criteria with the COUNTIF function or somthing else? | Excel Worksheet Functions | |||
COUNTIF or SUM function (Multiple criteria) HELP!! | Excel Worksheet Functions | |||
countif function with multiple criteria | Excel Discussion (Misc queries) | |||
How do I create a COUNTIF function with more than one criteria? | Excel Worksheet Functions |