×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

XL97 formula for counting

XL97 formula for counting

XL97 formula for counting

(OP)
I have 2 columns. The first with words, the other with numbers.

EX:

sean....4
jane....6
jim.....1
alice...2
jim.....4
jane....7
ryan....2
jim.....1

I need to total the numbers in relation to the names.

EX
alice...2
sean....4
jane....13
jim.....6
ryan....2

how can I do this. i'm trying with Vlookup, but I'm having some trouble....

TIA



Rotschrck's Eclectic Page

RE: XL97 formula for counting

Do you want to "count" or do you want to return the corresponding number to a particular name? For example, if you type in "alice", do you want the number 2 to be returned?

RE: XL97 formula for counting

(OP)
As I was writing a response, I realised that I do want to count....though I made a mistake in my previous post as to what I wanted to count.

In the above example, if I wanted to count how many times Jim showed up, it would say '3'.

*sigh* that's what happens when you try to get an early start on your day and forget the tea. *laugh*



Rotschrck's Eclectic Page

RE: XL97 formula for counting

If you want to count instances, I would not use vlookup. vlookup is used to reference tabulated items. For example, if you have a list of employees, each with an ID #, vlookup can return the name given the ID or return the ID given the name. I would recommend either using a pivot table or a macro to count instances of occurance.

RE: XL97 formula for counting

If you have your names in A1:A8, numbers in B1:B8

Put this formula in cell E1. It is an array formula so you must use Ctl+Shift+Enter for it to work.

=SUM(IF(A1:A8=D1,B1:B8))

If you then type one of your names in cell D1, their total will display in cell E1.

You can also hard code the name in place of D1 in the formula. =SUM(IF(A1:A8="jim",B1:B8)) would give your jim's total without typing the name in cell D1.

If you miss spell a name anywhere your count will be off so you may want to do some validation.

Bob Crawford

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close