DCOUNTA with multiple criteria

[stumped in texas]

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.

 

[Chip Pearson]

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

 

[Dave Peterson]

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Jacob Skaria]

--Using DCOUNTA
http://office.microsoft.com/en-us/excel/HP052090501033.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