DCOUNTA Complex Criteria Question

[Elliot Colbert]

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.

 

[Biff]

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]

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!

 

[Elliot Colbert]

Alright, I've gotten it to work by just doing a "=IF(L2>0,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.

 

[Biff]

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]

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