How do I use DCOUNT with evaluation of multiple columns?

[Bryan]

I am running Excel 2007.

I have a spreadsheet with 7 fields (columns). I want to count the number of
rows where field 2 is a specific value AND filed 5 is a specific value (or
non-blank). I do not understand the instructions provided in DCOUNT help
under "Multiple criteria in multiple columns where all criteria must be true".

Thanks!

 

[Teethless mama]

Take a look COUNTIFS function in help menu

 

[Luke M]

You do not want to use DCOUNT. Use SUMPRODUCT like this:
=SUMPRODUCT((A2:A100="Condition1")*(D2100="Condition2"))
Or
=SUMPRODUCT((A2:A100="Condition1")*(NOT(ISBLANK(D2100))))

depending on what you want.

 

[Shane Devenshire]

Hi,

First your criteria are inconsistant - you say that field 5 should have a
specified value or be non-blank. No point in testing for a specified value
since a test for non-blank will always incude everything that is non-blank
including your specified value.

For DCOUNT the range you could must be numerics, so you may want to use
DCOUNTA instead.

=DCOUNTA(A1:G100,1,J1:K2)

A1:G100 is your data range and must include unique titles on the first row.
1 is any column you want to count the rows for. This choice is pretty
arbitrary for a counta function, the only point is that the rows in that
column must contain something. J1:K2 is your criteria range and its first
row must contain the titles from Field 2 and Field 5 of your data. Row 2
contains the specified values.

Most answers on this newsgroup to this question would direct you to using a
formula like

=SUMPRODUCT(--(B1:B100=K1),--(E1:E100=K2))

Where the values you wanted to count for are in K1 and K2

 

[Bryan]

Wow. Thank you very much!

Take a look COUNTIFS function in help menu