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...
Formulas & Functions
Need Help
Willy Lau's avatar
Willy Lau
Iron 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