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!

Take a look COUNTIFS function in help menu


"Bryan" wrote:

> 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!
>

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Bryan" wrote:

> 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!
>

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Bryan" wrote:

> 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!
>

Wow. Thank you very much!

"Teethless mama" wrote:

> Take a look COUNTIFS function in help menu
>
>
> "Bryan" wrote:
>
> > 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!
> >