Forum Discussion

Rabbi Issamar Mandel's avatar
Rabbi Issamar Mandel
Copper Contributor
Nov 30, 2017

Create a Summary Sheet based on Variable Criteria

Hi,

I have a workbook tracking the progress of a school-wide campaign in which students are selling raffle tickets. Each sheet represents a class, and on each sheet, Column 1 is the students' names, and Column 2 is the amount of tickets sold. (See first image below)

I'd like to create a sheet where every student who sold between 5 and 10 tickets, regardless of which class they're in, are displayed in one column, and another column for sellers between 11 and 15, etc. (See second image below)

I've spent hours experimenting with IF's and VLOOKUP's combined with array functions, but haven't succeeded yet. Any input is very much appreciated! 

 

...where the students are being pulled from multiple sheets.

Formulas & Functions
Need Help

1 Reply

Sort By
  • Willy Lau's avatar
    Willy Lau
    Steel Contributor
    Dec 01, 2017

    I hope this is not too late.

     

    Steps:

    1. Make your data as a table, named "DataTable"
    2. Define a name, "Hit_0" 
      =--(DataTable[Tix Sold:]=0)*ROW(DataTable[Tix Sold:])
      This will make a array, and its size is equal to your data volume with zero value, except those row that tix sold is zero, which will the the actual row number in worksheet.
    3. We can use SMALL formula to get them.  HOWEVER, the first thing to do is to tell SMALL formula to ignore those zero data.  What you need to do is to define a name, F_Hit_0 
      =IF(Hit_0=0,"X",Hit_0)
      This will make those zero data to "X", and SMALL formula will ignore it
    4. In the cell which is just under the cell "0 Tix",  
      =IFERROR(INDEX(DataTable[[#All],[Names:]],SMALL(F_Hit_0,ROW(A2)-ROW($A$2)+1),1),"")
      ROW(A2)-ROW($A$2)+1 is for SMALL formula to extract from the 1st matching row
    5. Use Fill-Handle to copy the formula to the cells below that it.
    6. You can create other name for other range of Tix Sold.
    7. e.g. Hit1_5 
      =--(DataTable[Tix Sold:]>0)*--(DataTable[Tix Sold:]<=5)*ROW(DataTable[Tix Sold:])

Resources