excel - How to count same values in a column for certain users -

i have column appear different users , other 1 appear value. each user can have several of these values , can repeated. know how count same values in same column need know quantity of identical values each user. example:

  user            value  user1            100  user1            300  user1            100  user1            200  user1            300  user1            100  user1            100  user1            400  user2            100  user2            100  user2            100  user2            400  user2            100  user2            200  user2            200 

then should appear like:

 user          value     count user1          100         4 user1          200         1 user1          300         2 user1          400         1 

and same second user , on.

edit - sorry misunderstood had do... had more values , though had focus in 1 value apparently had take in count of them: need following:

  user       v1      v2     v3     v4   user1            c            b   user2      b       d            b   user1            f            c   user3      c            c       d   user3      d       b            

what should appear user1

   user1      v1           2     b       0     c       0     d       0      v2           1     b       0     c       0     d       0     e       0     f       1      v3           2     b       0     c       0     d       0      v4           0     b       1     c       1     d       0 

any ideas that? anyway help!

edit

added new answer below based on new data structure original poster.

so couple things have happen work. reccomend converting data range table. makes easy dynamic column , value references. highlight data range , hit ctrl+t.

now data in cell a1:e6 have above

table1=a1:e6=

user    v1  v2  v3  v4 user1     c     b user2   b   d     b user1     f     c user3   c     c   d user3   d   b     

• in cell f2 enter
• =iferror(index(table1[user],match(0,index(countif($f$1:f1,table1[user]),0,0),0)),"-")
• this return first unique user in "user" column. show rest drag down copy formula in cells below f2.
• then in cell g2 made drop down list of range f2:f4, unique users.
• in cell g3 made drop down list of range b1:e1, headers v1,v2...
• in cell g4 enter
• =iferror(index(indirect("table1["&$g$3&"]"),match(0,index(countif($g$3:g3,indirect("table1["&$g$3&"]")),0,0),0)),"-")
• you can copy formula down cells below until see "-" means there no more unique values column "v1"
• in cell h4 enter
• =countifs(table1[user],$g$2,indirect("table1["&$g$3&"]"),g4)

this give looks this... hope helps,

end edit

first answer...

here how cell formulas.

• range of users header in a:a
• range of values header in b:b
• range a:b sorted users , values
• in d1:f1 type in new headers, unique users, unique values, user values count.
• there 3 formulas, 1 each column.
• d2 = =index(a:a,row($f$2)+sum(f$1:f1))
• e2 = =index(b:b,row($f$2)+sum(f$1:f1))
• f2 = =countifs(a:a,d2,b:b,e2)

with 3 in second row should able drag down formula fill list out more , more showing unique users , unique values count of values. if formula returns 0 means @ end of unique data.

-scheballs

edit- typo, jerry