# Dcount or Countif?

S

#### Steve

I have a spreadsheet with 5 columns and need to count the number of
responses between a range based on the criteria of two cells.

For instance the range is A1:E16

Columns
A = Name
C = City
D = State
E = Age

Criteria
A1 = Jack
D2 = Michigan

Count Brackets:
Age = 20-29, 30-39, 40-49, 50-59

I need an equation that will count the number of records with a name
of Jack and a state of Michigan and filter that count to a range. I
will drop the count for each range in a different cell so the equation
would be a static age bracket.

Can anymore make any suggestions?

I have a spreadsheet with 5 columns and need to count the number of
responses between a range based on the criteria of two cells.

For instance the range is A1:E16

Columns
A = Name
C = City
D = State
E = Age

Criteria
A1 = Jack
D2 = Michigan

Count Brackets:
Age = 20-29, 30-39, 40-49, 50-59

I need an equation that will count the number of records with a name
of Jack and a state of Michigan and filter that count to a range. I
will drop the count for each range in a different cell so the equation
would be a static age bracket.

Can anymore make any suggestions?

Assuming that you really have 15 rows of data and your first row is a
heading, such that the actual range you are interrogating is A2:E16,
and assuming you didn't mean to put your criteria ("Jack") in a cell
that is really a heading and assuming that you didn't mean to put your
criteria ("Michigan") in a cell which is part of your data, then put
criteria in another location (like G2) and then put this in someplace
that you aren't currently using, like H1:

=SUM((\$E\$2:\$E\$16>=20)*(\$E\$2:\$E\$16<30) * (\$A\$2:\$A\$16=G1) *
(\$D\$2:\$D\$16=G2))

And then enter it as an array formula (hold the cntl-shift down and
hit enter)

Changing it so that you get your other ranges is left to the user.

=SUMPRODUCT(--(\$A\$1:\$A\$16="Jack"),--(\$D\$1:\$D\$16="Michigan"),--(\$E\$1:\$E\$16>=20),--(\$E\$1:\$E\$16<30))