XL97 formula for counting

[rotschreck]

I have 2 columns. The first with words, the other with numbers.<br><br>EX:<br><br>sean....4<br>jane....6<br>jim.....1<br>alice...2<br>jim.....4<br>jane....7<br>ryan....2<br>jim.....1<br><br>I need to total the numbers in relation to the names.<br><br>EX<br>alice...2<br>sean....4<br>jane....13<br>jim.....6<br>ryan....2<br><br>how can I do this. i'm trying with Vlookup, but I'm having some trouble....<br><br>TIA <p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/siltharin>Rotschrck's

Eclectic Page</a><br>

 

[dsi]

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

 

[rotschreck]

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.<br><br>In the above example, if I wanted to count how many times Jim showed up, it would say '3'.<br><br>*sigh* that's what happens when you try to get an early start on your day and forget the tea. *laugh* <p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/siltharin>Rotschrck's

Eclectic Page</a><br>

 

[dsi]

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.

 

[BobCrawf]

If you have your names in A1:A8, numbers in B1:B8<br><br>Put this formula in cell E1. It is an array formula so you must use Ctl+Shift+Enter for it to work.<br><br>=SUM(IF(A1:A8=D1,B1:B8))<br><br>If you then type one of your names in cell D1, their total will display in cell E1.<br><br>You can also hard code the name in place of D1 in the formula. =SUM(IF(A1:A8=&quot;jim&quot;,B1:B8)) would give your jim's total without typing the name in cell D1.<br><br>If you miss spell a name anywhere your count will be off so you may want to do some validation.<br><br>Bob Crawford