Trigger row count when data changes & store count in lookup table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list of salespeople and the states where they're assigned, sorted by state. I hoped to come up with a formula, something like 'if state changes from this row to the next, count rows in this state's range, and store the count elsewhere in a lookup table by state name'. I've done some pretty complex formulas, but can't come up with anything for this case. Any way to do this without getting into VB or other programming techniques

THANKS!
 
Ken,

One word (Okay, two words): Pivot Table

Select your list, then use Data | Pivot Table, then drag in the wizard drag
the state name button to both the row and data fields and you will get a
list of states with the count for each state.

HTH,
Bernie
MS Excel MVP

kenp said:
I have a list of salespeople and the states where they're assigned, sorted
Click to expand...
by state. I hoped to come up with a formula, something like 'if state
changes from this row to the next, count rows in this state's range, and
store the count elsewhere in a lookup table by state name'. I've done some
pretty complex formulas, but can't come up with anything for this case. Any
way to do this without getting into VB or other programming techniques?
 
This works. First 3 cols your database. RunCount starts
at 1 and increments or starts over at 1 depending on value
of next State. Next 3 cols your lookup table.
StateRunTot is =DCOUNT($A$1:$C$17,$C$1,$E$1:$E2). Note
NO "$" in $e2. Copy this down for each state row in
lookup table. StateCount is diff btwn StateRunTot values.
This will work even if a state in the lookup table is not
in the database.
Hope I'm clear. If not I can send sample by email.
hcj at a dotnavydotmil address

State Name RunCount State StateRunTot StateCount
a joe 1 a 1 1
b mary 1 b 8 7
b etc. 2 c 9 1
b 3 d 16 7
b 4
b 5
b 6
b 7
c 1
d 1
d 2
d 3
d 4
d 5
d 6
d 7


-----Original Message-----
I have a list of salespeople and the states where they're
Click to expand...
assigned, sorted by state. I hoped to come up with a
formula, something like 'if state changes from this row to
the next, count rows in this state's range, and store the
count elsewhere in a lookup table by state name'. I've
done some pretty complex formulas, but can't come up with
anything for this case. Any way to do this without
getting into VB or other programming techniques?
 
Forgot to mention - Col D left blank as separator in my
solution. Data in Cols a-c; lookup in cols e-g
-----Original Message-----
I have a list of salespeople and the states where they're
Click to expand...
assigned, sorted by state. I hoped to come up with a
formula, something like 'if state changes from this row to
the next, count rows in this state's range, and store the
count elsewhere in a lookup table by state name'. I've
done some pretty complex formulas, but can't come up with
anything for this case. Any way to do this without
getting into VB or other programming techniques?
 
Back
Top