Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DCOUNTA with multiple criteria
Can anyone explain how to do a DCOUNTA formula with multiple criteria? I need
to count the number of people that have PPO insurance that live in Ft. Worth or Bedford on a worksheet. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DCOUNTA with multiple criteria
Assume the PPO and City info exists in A1:B10 with A1 = "PPO" and B1 =
"City". Enter the following in F15:G17: PPO City TRUE Fort Worth TRUE Bedford Then, use the following DCOUNTA formula: =DCOUNTA(A1:B10,2,F15:G17) This will return the number of values in A1:A10 where column A is TRUE and column B is either 'Fort Worth' or 'Bedford'. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 1 Nov 2009 13:14:01 -0800, stumped in texas <stumped in wrote: Can anyone explain how to do a DCOUNTA formula with multiple criteria? I need to count the number of people that have PPO insurance that live in Ft. Worth or Bedford on a worksheet. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DCOUNTA with multiple criteria
There are other worksheet functions that may be easier to use. If you're using
xl2007, look at =sumifs(). In all versions, you could use =sumproduct(). =SUMPRODUCT(--(A1:A10="PPO"), --ISNUMBER(MATCH(B1:B10,{"bedford","Ft. Wayne"},0))) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html stumped in texas wrote: Can anyone explain how to do a DCOUNTA formula with multiple criteria? I need to count the number of people that have PPO insurance that live in Ft. Worth or Bedford on a worksheet. -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DCOUNTA with multiple criteria
--Using DCOUNTA
http://office.microsoft.com/en-us/ex...090501033.aspx Specify your criterias in Row 2 --Using SUMPRODUCT() for 2 criterias =SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2)) =SUMPRODUCT((A1:A10=F1)*(B1:B10=F2)) --If you are using Excel 2007 check out help on COUNTIFS() If this post helps click Yes --------------- Jacob Skaria "stumped in texas" wrote: Can anyone explain how to do a DCOUNTA formula with multiple criteria? I need to count the number of people that have PPO insurance that live in Ft. Worth or Bedford on a worksheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula in DCounta Criteria | Excel Worksheet Functions | |||
DCOUNTA WITH MULTIPLE FIELD CRITERIA | Excel Worksheet Functions | |||
DCOUNTA Complex Criteria Question | Excel Worksheet Functions | |||
Specify DCOUNTA Criteria in the Formula | Excel Discussion (Misc queries) | |||
Sum Multiple Criteria or DcountA | Excel Worksheet Functions |