Attendance Analysis

    Hi Folks


    I'm trying to set up an analysis sheet that looks at the number of people who have attended an event. They are marked with an "x" on a sheet as they arrive and an IF formula looks at whether that "x" exists and then copies the name across if it does. However as it works its way down the list it obviously skips the ones where there is no "x" and moves to the next giving a load of blank lines.


    Is there a way of modifying the formula to ignore the ignore the blanks and keep all the results together please?


    I've attached a sample of what I'm trying to do


    Many thanks as usual.


    Kind regards


    DezB


    [ATTACH]n1198893[/ATTACH]

    In G5 add a helper formula:


    =COUNTIF(E$5:E5,"X")


    copied down


    then in I5 use:


    =IFERROR(INDEX($C$5:$C$14,MATCH(H5,$G$5:$G$14,0)),"")


    copied down.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Hi NBVC


    Excellent, works a treat.


    Thank you again.... I need to understand this "helper" system don't I?


    Kind regards


    DezB

    You can do it without helper, but will need an array formula and if you have a lot of data it becomes inefficient.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

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

Register Yourself Login