Posts by bloodaxe

    Re: Totalling numbers by names - how?


    Hi There


    Again, always amazed at the fast replies on here.


    See attached. I copied Roberts example and added two further ones of my own below.


    forum.ozgrid.com/index.php?attachment/59888/


    However, I must be doing something wrong as try as I might I can't copy and replicate his formula to calculate the new range! So mine is faked.


    Hope that helps make it clearer.


    Kind regards,




    Ian

    Re: Totalling numbers by names - how?


    Hello again.



    I have been travelling in my time machine again, hence the delay. ;)


    Once again, thanks for the brilliant suggestions. I've tried them all out and Robert Mika's approach seems to be the simplest and does almost what I need... however, I still have to derive the list of names to type into column N? That's OK for this example, but in real life I have 276 winners to trawl through...


    Is it possible to add a further formula to automatically count the occurrences of the name, and then put that in the cell for the SumIf to work on?


    So, using Robert's example file:


    "Missing" formula counts the occurrence of names in columns B, E and H, finds F Bloggs appears most times (6), then displays F Bloggs in cell N2
    SumIf calculation uses the name displayed in N2 to calculate points total of 4, displayed in cell O2
    Then "Missing" formula counts the occurrence of names in columns B, E and H, finds J Smith appears next times (4), then displays F Bloggs in cell N2
    SumIf calculation uses the name displayed in N2 to calculate points total of 3, displayed in cell O2
    And so on...
    Obviously, if there's a tie in number of name occurrences, they'd need to show alphabetically in N2, N3 etc.


    Is this possible?



    Kind regards






    Ian

    Re: Totalling numbers by names - how?


    Wow!


    6 ideas in as many hours. Thanks guys.


    I have to admit to still being confused about SUMIFS and other solutions suggested, but a) it is late and b) wine has been taken. So will have a play through the suggestions at some point and report back.


    Once again, my gratitude for fast help.

    Regards




    Ian

    Hi All



    The last time I had a question, this forum was invaluable. And I've been back many times to lookup the answers to my queries. But again this one has me stumped.


    As before, please be gentle - I'm an excel amateur. Now very new to using 2010/13. No knowledge of VBA. Apologies if I get this a bit wrong. I have read the suggested post on this topic (http://www.ozgrid.com/forum/showthread.php?t=125581) but must admit to not understanding how to apply the answers in my case!


    The output of my magic spreadsheet, developed to run the local flower show, is a column of names - each of which have won a prize valued in points. I need to determine which person in the list of names has won the most points in total, in order to award a trophy.


    Actually, I also need to know how many points each individual has got because - as we all know - points make prizes. And I need to write out one prize voucher for each individual, to the total value of what they won.


    Now I have been sorting each column alphabetically, then manually totting up the points, and adding the three totals together. But surely, there must be a way to do this automatically? I don't really want to have to sort the columns at all... just have the answers appear like magic once the data is in the data entry part of the sheet.


    Here's an example:




    When you tot up the points across the three sets of columns, you see that J Smith has £3.00, B Jones has £1.50, A Nother has 50p but F Bloggs wins overall with £4.00.


    What's that you say? Why don't you keep on doing it manually? Well, that's because my real spreadsheet has 134 rows, and three sets of columns so there are 402 potential names to scan through. Plus different values of points/pounds for prizes.


    I hope someone can help!


    Happy to give more detail or answer queries as you solve my conundrum.



    Kind regards






    Ian

    Hi All



    This forum has been a very helpful source of answers to many "How to..." questions, but try as I might I cannot find the answer to this one. Hence this, my first post. Please be gentle - I'm an excel amateur. Using 2003. No knowledge of VBA. So apologies if I get this a bit wrong.


    Here's what I'm trying to do.


    I have a sheet called Classes 1 - 91. Each cell on this sheet contains a name, typed in as a text string. (The names are entrants in a competition, where points are awarded for first, second and third prizes. I'm using the cell reference to anonymise the entrants).


    For example:
    J Smith is in cell C1
    F Bloggs is in cell D2
    A Jones is in cell E1
    J Brown is in cell F7


    The second sheet in the workbook is called Winners and is set up to record the prizewinners. So the competition judge will give me a reference for the first prize winner, eg: D2. And what I'd like to do is type D2 into a cell on the Winners sheet, and have the name from that cell in sheet Classes 1 - 91 be pulled through into a results cell. In this instance, it will display F Bloggs under my column heading for 1st prize.


    Now the easy way is to use the formula ='Classes 1 - 91'!D2. But I want to idiot proof it, so don't want to have to type, copy or paste, or edit the formula each time. (There are 276 prizewinners to enter in total...)


    Despite lots of looking and playing, I can't seem to find how to insert the !D2 into the formula by using D2 typed into a cell on the Winners sheet.


    I've tried CONCATENATE, but while I can get it to add the two text strings together and return the result in a separate cell, the results cell doesn't use it as a formula, just displays it as text.


    I've played with INDIRECT but don't really understand what it's doing.


    Here's hoping that some of you Excel geniuses will find this easy, and make my day!



    Kind regards





    Ian