Dcount or Countif?

  • Thread starter Thread starter Steve
  • Start date Start date
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
B = Address
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
B = Address
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?
Click to expand...

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
your name criteria in another location (like G1) and your state
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))
 
Back
Top