Embedded Files
The tutorials listed below are created by John Stevens, Google Certified Innovator.
Great video tutorial for the Query function
SUMIFS, COUNTIFS & AVERAGEIFS
Query from multiple tabs in a sheet based on criteria
Query & Array from multiple tabs in a sheet and "matches" for varying cell content
FANCY FORMULAS I'VE USED AND I WANT TO REMEMBER FOR THE FUTURE
FANCY FORMULAS I'VE USED AND I WANT TO REMEMBER FOR THE FUTURE
IF SOMETHING APPEARS IN A LIST MORE THAN ONCE, COLOUR THE CELL
IF SOMETHING APPEARS IN A LIST MORE THAN ONCE, COLOUR THE CELL
Conditional formatting
=countif(C:C,C1)>1, where C is the column you are searching for duplicates
IF A NAME APPEARS IN A LIST MORE THAN ONCE, PULL IT
IF A NAME APPEARS IN A LIST MORE THAN ONCE, PULL IT
=FILTER(UNIQUE(A2:A5000), ARRAYFORMULA( COUNTIF(A2:A5000,UNIQUE(A2:A5000))>1 ))
EXAMPLE: https://docs.google.com/spreadsheets/d/1Y92Y727T4_xpJ6NPXiCnX3XmyoeBxO4iaF5Gm_NMeFY/edit#gid=0
QUERY WITH MULTIPLE CRITERIA
QUERY WITH MULTIPLE CRITERIA
=query('All Registrations'!1:100000, "Select A,B,C Where A contains 'Stepping up your PowerPoint Game' or A contains 'Sway' or A contains 'Assessment in Microsoft Teams'",1)
PULLING DATA FROM OTHER SHEETS WITH SPECIFIC CRITERIA
PULLING DATA FROM OTHER SHEETS WITH SPECIFIC CRITERIA
Below is a Query (with an Array bringing in data from multiple tabs within a sheet) and filtered by 1 specific criteria in column B. When you use an Array, you have to reference columns by 'Col1' instead of its letter.
=QUERY({'Emily Carr S.S.'!A:R;'Maple H.S.'!A:R;'Stephen Lewis S.S.'!A:R;'Thornhill S.S.'!A:R;'Thornlea S.S.'!A:R;'Tommy Douglas S.S.'!A:R;'Vaughan S.S.'!A:R;'Westmount C.I.'!A:R;'Woodbridge C.'!A:R},"SELECT*WHERE Col2='FIRST'",0)
In the second formula below, the criteria says 'matches' because it's not always the same in each cell so the period followed by the * means take whatever is varied after 'SECOND'.
=QUERY({'Emily Carr S.S.'!A:R;'Maple H.S.'!A:R;'Stephen Lewis S.S.'!A:R;'Thornhill S.S.'!A:R;'Thornlea S.S.'!A:R;'Tommy Douglas S.S.'!A:R;'Vaughan S.S.'!A:R;'Westmount C.I.'!A:R;'Woodbridge C.'!A:R},"SELECT*WHERE Col2 MATCHES 'SECOND.*'",0)
If there are more than two criteria that you want to count in one column:
COUNTIF(range,criteria) + COUNTIF(range, criteria) +COUNTIF(range, criteria)+…
or even more easy - =SUMPRODUCT(COUNTIF(A2:A11,{"KTE";"KTO"}))
RUNNER DISTANCE TIME FORMULAS
RUNNER DISTANCE TIME FORMULAS
Running times are recorded in a sheet. Each student gets a row and the scores are recorded in each new column to the right.
Version 1:
Have 2 columns Previous PR and Current PR (PR = Personal Record)
formula for Previous PR =small (E2:Y2,2)
This means the second smallest number will appear in this cell
formula for Current PR =small(E2:Y2,1)
This means the smallest number will appear in this cell
Version 2:
Using conditional formatting, highlight the lowest number in the row as the times come in
Make sure the range of data includes the first cell with numbers all the way to the right to allow for more running times to be entered.
Conditional Formatting: =B2=MIN($B2:$Y2)
Copy the conditional formatting in that row and then paste special (conditional formatting only) to each subsequent row
Google Sites
Report abuse