Totalling numbers by names - how?

    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

    Re: Totalling numbers by names - how?


    Hi
    It's late for me now but I will have a look tomorrow night. Hopefully someone else will jump in before then though.

    Re: Totalling numbers by names - how?


    your question is too vague for me, attach please a sample file with data, with problem to solve and desired result

    Re: Totalling numbers by names - how?


    I would suggest you reorganise the data. Then you can use a PivotTable or Sumif(). I would use the PivotTable becuase it is automatic, just needs refreshing by right clicking on it after you haver added data to the Table.


    I have used an Excel Table, VLOOKUP<Data Validation & a PivotTable

    Re: Totalling numbers by names - how?


    Hi,


    I am not excel expert... but sometimes i work with somewhat similar type of data layout
    What i do is... copy down all the names in one column (column A) then... remove duplicate... then
    =SUMIF($B$2:$B$5,A8,$C$2:$C$5)+SUMIF($E$2:$E$5,A8,$F$2:$F$5)+SUMIF($H$2:$H$5,A8,$I$2:$I$5)


    This will save some of your time atleast... however experts here may give you better solution...

    Re: Totalling numbers by names - how?


    As the OP is using the newer versions of Excel then instead of the above formula use SUMIFS, although I don't think it will be the required output.

    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

    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?


    I can't remember all the details now so attach an example workbook showing what you expect to bis displayed, although a PivotTable is more efficient than array formulas, provides ity's own list of names and can be easily sorted by Points.

    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?


    Create a list somewhere of all the possible names, in any order. Then try a formula in N15 like:


    [COLOR="#0000FF"]=INDEX($T$1:$T$4,MATCH(LARGE(COUNTIF($A$15:$I$19,$T$1:$T$4),ROWS($N$15:$N15)),COUNTIF($A$15:$I$19,$T$1:$T$4),0))[/COLOR]


    where T1:T4 is the list of names to reference and A15:I19 is the database range.

    this is an array formula and needs to be confirmed with CTRL+SHIFT+ENTER not just ENTER    , then copied down.


    Then in O15 use regular formula:


    [COLOR="#0000FF"]=SUMIF($B$15:$H$19,N15,$C$15:$I$19)[/COLOR]


    This just needs ENTER as normal. Notice that the sum range (3rd parameter) is offset from the criteria range (1st parameter) by 1 column.


    copied down

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login