DCOUNTA Complex Criteria Question
 

I've got a workbook with two sheets - one is a list of names, and another is
a two-column array in which every name has multiple entries, and each entry
consists of the name and a percentage.

On the first sheet, I'm trying to create a tally next to each name of how
many of their entries have a percentage above or below a given number, using
the DCOUNTA function. In the past, when I would do this, I would create a
different set of criteria for each name. This works, but it takes forever,
becomes very hard to modify (if names need to be swapped in/out), and is just
generally unpleasant and unruly (each DCOUNTA function needs to be edited
individually to point to the right criteria, for example). I'd like to learn
how to accomplish this task with only one set of criteria, whereby the
DCOUNTA function (which is in the cell next to the name) can pull the name
from the field to the left (probably through the criteria).

I've tried putting all sorts of Lookup-type functions (HLOOKUP(), ROW())
into the criteria, but the main problem I can't get around is that they
always end up referring to the cell that the criteria is in, not the cell
(and thus the row) that the DCOUNTA function is in. Does anyone have any
bright ideas for a formula/criteria combo that would let me do what I want
with only one criteria set, and just drag the DCOUNTA formula straight down
the list of names.

DCOUNTA Complex Criteria Question
 

Hi!

Use the Sumproduct function.

Sheet1 = names in column A
Sheet2 = names in column A, % in column B

Sheet1 A1 = John

=SUMPRODUCT(--(Sheet2!A1:A10=A1),--(Sheet2!B1:B10<=0.5))

That will count the number of entries where John had a % that was less than
or equal to 50.

If there might be empty cells in column B Sheet2 associated with a name
then:

=SUMPRODUCT(--(Sheet2!A1:A10=A1),--(ISNUMBER(Sheet!2B1:B10),--(Sheet2!B1:B10<=0.5))

I'd like to learn
how to accomplish this task with only one set of criteria, whereby the
DCOUNTA function (which is in the cell next to the name) can pull the name
from the field to the left (probably through the criteria).

"Pull" the name based on what criteria?

Biff

"Elliot Colbert" <Elliot wrote in message
...
I've got a workbook with two sheets - one is a list of names, and another
is
a two-column array in which every name has multiple entries, and each
entry
consists of the name and a percentage.

On the first sheet, I'm trying to create a tally next to each name of how
many of their entries have a percentage above or below a given number,
using
the DCOUNTA function. In the past, when I would do this, I would create a
different set of criteria for each name. This works, but it takes
forever,
becomes very hard to modify (if names need to be swapped in/out), and is
just
generally unpleasant and unruly (each DCOUNTA function needs to be edited
individually to point to the right criteria, for example). I'd like to
learn
how to accomplish this task with only one set of criteria, whereby the
DCOUNTA function (which is in the cell next to the name) can pull the name
from the field to the left (probably through the criteria).

I've tried putting all sorts of Lookup-type functions (HLOOKUP(), ROW())
into the criteria, but the main problem I can't get around is that they
always end up referring to the cell that the criteria is in, not the cell
(and thus the row) that the DCOUNTA function is in. Does anyone have any
bright ideas for a formula/criteria combo that would let me do what I want
with only one criteria set, and just drag the DCOUNTA formula straight
down
the list of names.

DCOUNTA Complex Criteria Question
 

Biff, many thanks. Definitely the function I'm looking for.

However, I'm having a new problem. Many of the percentages in column B are
#DIV/0! errors, and I'm finding that even when I filter with the ISNUMBER
function, I'm getting a #DIV/0! for the SUMPRODUCT as well. I'll keep
looking for a fix myself, but if anyone's got the answer of the top of their
head, I'd love to hear it. Thanks again!

"Biff" wrote:

Hi!

Use the Sumproduct function.

Sheet1 = names in column A
Sheet2 = names in column A, % in column B

Sheet1 A1 = John

=SUMPRODUCT(--(Sheet2!A1:A10=A1),--(Sheet2!B1:B10<=0.5))

That will count the number of entries where John had a % that was less than
or equal to 50.

If there might be empty cells in column B Sheet2 associated with a name
then:

=SUMPRODUCT(--(Sheet2!A1:A10=A1),--(ISNUMBER(Sheet!2B1:B10),--(Sheet2!B1:B10<=0.5))

I'd like to learn
how to accomplish this task with only one set of criteria, whereby the
DCOUNTA function (which is in the cell next to the name) can pull the name
from the field to the left (probably through the criteria).

"Pull" the name based on what criteria?

Biff

"Elliot Colbert" <Elliot wrote in message
...
I've got a workbook with two sheets - one is a list of names, and another
is
a two-column array in which every name has multiple entries, and each
entry
consists of the name and a percentage.

On the first sheet, I'm trying to create a tally next to each name of how
many of their entries have a percentage above or below a given number,
using
the DCOUNTA function. In the past, when I would do this, I would create a
different set of criteria for each name. This works, but it takes
forever,
becomes very hard to modify (if names need to be swapped in/out), and is
just
generally unpleasant and unruly (each DCOUNTA function needs to be edited
individually to point to the right criteria, for example). I'd like to
learn
how to accomplish this task with only one set of criteria, whereby the
DCOUNTA function (which is in the cell next to the name) can pull the name
from the field to the left (probably through the criteria).

I've tried putting all sorts of Lookup-type functions (HLOOKUP(), ROW())
into the criteria, but the main problem I can't get around is that they
always end up referring to the cell that the criteria is in, not the cell
(and thus the row) that the DCOUNTA function is in. Does anyone have any
bright ideas for a formula/criteria combo that would let me do what I want
with only one criteria set, and just drag the DCOUNTA formula straight
down
the list of names.

DCOUNTA Complex Criteria Question
 

Alright, I've gotten it to work by just doing a "=IF(L20,K2/L2,"")" (to keep
the cell blank if it will be a #DIV/0!). That said, if anyone knows a way to
make SUMPRODUCT work even in the presence of #DIV/0!'s, it might be useful
for future reference. Thanks.

"Elliot Colbert" wrote:

Biff, many thanks. Definitely the function I'm looking for.

However, I'm having a new problem. Many of the percentages in column B are
#DIV/0! errors, and I'm finding that even when I filter with the ISNUMBER
function, I'm getting a #DIV/0! for the SUMPRODUCT as well. I'll keep
looking for a fix myself, but if anyone's got the answer of the top of their
head, I'd love to hear it. Thanks again!

"Biff" wrote:

Hi!

Use the Sumproduct function.

Sheet1 = names in column A
Sheet2 = names in column A, % in column B

Sheet1 A1 = John

=SUMPRODUCT(--(Sheet2!A1:A10=A1),--(Sheet2!B1:B10<=0.5))

That will count the number of entries where John had a % that was less than
or equal to 50.

If there might be empty cells in column B Sheet2 associated with a name
then:

=SUMPRODUCT(--(Sheet2!A1:A10=A1),--(ISNUMBER(Sheet!2B1:B10),--(Sheet2!B1:B10<=0.5))

I'd like to learn
how to accomplish this task with only one set of criteria, whereby the
DCOUNTA function (which is in the cell next to the name) can pull the name
from the field to the left (probably through the criteria).

"Pull" the name based on what criteria?

Biff

"Elliot Colbert" <Elliot wrote in message
...
I've got a workbook with two sheets - one is a list of names, and another
is
a two-column array in which every name has multiple entries, and each
entry
consists of the name and a percentage.

On the first sheet, I'm trying to create a tally next to each name of how
many of their entries have a percentage above or below a given number,
using
the DCOUNTA function. In the past, when I would do this, I would create a
different set of criteria for each name. This works, but it takes
forever,
becomes very hard to modify (if names need to be swapped in/out), and is
just
generally unpleasant and unruly (each DCOUNTA function needs to be edited
individually to point to the right criteria, for example). I'd like to
learn
how to accomplish this task with only one set of criteria, whereby the
DCOUNTA function (which is in the cell next to the name) can pull the name
from the field to the left (probably through the criteria).

I've tried putting all sorts of Lookup-type functions (HLOOKUP(), ROW())
into the criteria, but the main problem I can't get around is that they
always end up referring to the cell that the criteria is in, not the cell
(and thus the row) that the DCOUNTA function is in. Does anyone have any
bright ideas for a formula/criteria combo that would let me do what I want
with only one criteria set, and just drag the DCOUNTA formula straight
down
the list of names.

DCOUNTA Complex Criteria Question
 

make SUMPRODUCT work even in the presence of #DIV/0!'s, it might be useful
for future reference.

It won't. The best solution is to fix the errors as you did.

Biff

"Elliot Colbert" wrote in message
...
Alright, I've gotten it to work by just doing a "=IF(L20,K2/L2,"")" (to
keep
the cell blank if it will be a #DIV/0!). That said, if anyone knows a way
to
make SUMPRODUCT work even in the presence of #DIV/0!'s, it might be useful
for future reference. Thanks.

"Elliot Colbert" wrote:

Biff, many thanks. Definitely the function I'm looking for.

However, I'm having a new problem. Many of the percentages in column B
are
#DIV/0! errors, and I'm finding that even when I filter with the ISNUMBER
function, I'm getting a #DIV/0! for the SUMPRODUCT as well. I'll keep
looking for a fix myself, but if anyone's got the answer of the top of
their
head, I'd love to hear it. Thanks again!

"Biff" wrote:

Hi!

Use the Sumproduct function.

Sheet1 = names in column A
Sheet2 = names in column A, % in column B

Sheet1 A1 = John

=SUMPRODUCT(--(Sheet2!A1:A10=A1),--(Sheet2!B1:B10<=0.5))

That will count the number of entries where John had a % that was less
than
or equal to 50.

If there might be empty cells in column B Sheet2 associated with a name
then:

=SUMPRODUCT(--(Sheet2!A1:A10=A1),--(ISNUMBER(Sheet!2B1:B10),--(Sheet2!B1:B10<=0.5))

I'd like to learn
how to accomplish this task with only one set of criteria, whereby
the
DCOUNTA function (which is in the cell next to the name) can pull the
name
from the field to the left (probably through the criteria).

"Pull" the name based on what criteria?

Biff

"Elliot Colbert" <Elliot wrote in
message
...
I've got a workbook with two sheets - one is a list of names, and
another
is
a two-column array in which every name has multiple entries, and each
entry
consists of the name and a percentage.

On the first sheet, I'm trying to create a tally next to each name of
how
many of their entries have a percentage above or below a given
number,
using
the DCOUNTA function. In the past, when I would do this, I would
create a
different set of criteria for each name. This works, but it takes
forever,
becomes very hard to modify (if names need to be swapped in/out), and
is
just
generally unpleasant and unruly (each DCOUNTA function needs to be
edited
individually to point to the right criteria, for example). I'd like
to
learn
how to accomplish this task with only one set of criteria, whereby
the
DCOUNTA function (which is in the cell next to the name) can pull the
name
from the field to the left (probably through the criteria).

I've tried putting all sorts of Lookup-type functions (HLOOKUP(),
ROW())
into the criteria, but the main problem I can't get around is that
they
always end up referring to the cell that the criteria is in, not the
cell
(and thus the row) that the DCOUNTA function is in. Does anyone have
any
bright ideas for a formula/criteria combo that would let me do what I
want
with only one criteria set, and just drag the DCOUNTA formula
straight
down
the list of names.

DCOUNTA Complex Criteria Question
 

Thanks for clearing that up - I should be good to go then!

"Biff" wrote:

make SUMPRODUCT work even in the presence of #DIV/0!'s, it might be useful
for future reference.

It won't. The best solution is to fix the errors as you did.

Biff

"Elliot Colbert" wrote in message
...
Alright, I've gotten it to work by just doing a "=IF(L20,K2/L2,"")" (to
keep
the cell blank if it will be a #DIV/0!). That said, if anyone knows a way
to
make SUMPRODUCT work even in the presence of #DIV/0!'s, it might be useful
for future reference. Thanks.

"Elliot Colbert" wrote:

Biff, many thanks. Definitely the function I'm looking for.

However, I'm having a new problem. Many of the percentages in column B
are
#DIV/0! errors, and I'm finding that even when I filter with the ISNUMBER
function, I'm getting a #DIV/0! for the SUMPRODUCT as well. I'll keep
looking for a fix myself, but if anyone's got the answer of the top of
their
head, I'd love to hear it. Thanks again!

"Biff" wrote:

Hi!

Use the Sumproduct function.

Sheet1 = names in column A
Sheet2 = names in column A, % in column B

Sheet1 A1 = John

=SUMPRODUCT(--(Sheet2!A1:A10=A1),--(Sheet2!B1:B10<=0.5))

That will count the number of entries where John had a % that was less
than
or equal to 50.

If there might be empty cells in column B Sheet2 associated with a name
then:

=SUMPRODUCT(--(Sheet2!A1:A10=A1),--(ISNUMBER(Sheet!2B1:B10),--(Sheet2!B1:B10<=0.5))

I'd like to learn
how to accomplish this task with only one set of criteria, whereby
the
DCOUNTA function (which is in the cell next to the name) can pull the
name
from the field to the left (probably through the criteria).

"Pull" the name based on what criteria?

Biff

"Elliot Colbert" <Elliot wrote in
message
...
I've got a workbook with two sheets - one is a list of names, and
another
is
a two-column array in which every name has multiple entries, and each
entry
consists of the name and a percentage.

On the first sheet, I'm trying to create a tally next to each name of
how
many of their entries have a percentage above or below a given
number,
using
the DCOUNTA function. In the past, when I would do this, I would
create a
different set of criteria for each name. This works, but it takes
forever,
becomes very hard to modify (if names need to be swapped in/out), and
is
just
generally unpleasant and unruly (each DCOUNTA function needs to be
edited
individually to point to the right criteria, for example). I'd like
to
learn
how to accomplish this task with only one set of criteria, whereby
the
DCOUNTA function (which is in the cell next to the name) can pull the
name
from the field to the left (probably through the criteria).

I've tried putting all sorts of Lookup-type functions (HLOOKUP(),
ROW())
into the criteria, but the main problem I can't get around is that
they
always end up referring to the cell that the criteria is in, not the
cell
(and thus the row) that the DCOUNTA function is in. Does anyone have
any
bright ideas for a formula/criteria combo that would let me do what I
want
with only one criteria set, and just drag the DCOUNTA formula
straight
down
the list of names.