Returning 1 row per unique ID (the Rep in this case), the count of each, and the sum of amounts, all in a single formula. GROUP BY is used to only return 1 of each ID. CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguidADe

=RIGHT(A1,LEN(A1)-FIND(":",A1)) removes everything up to and including a particular character. This works for any character and using SEARCH() instead of FIND(). CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars

Google Sheets: Track Time Overnight. 24 hours is equal to the number 1, so just add 1 to the Clock Out time whenever the out time is earlier than the in time. CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide

Google Sheets: Offset Surrounding Cells.—. Use negative offset_rows and _columns along with [height] and [width] to return a rectangular array of values. CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars

A Link to Copy the Spreadsheet.—. Sometimes you just need to send a link for someone to make a copy of the spreadsheet. To do so, just replace /edit with /copy!. CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars

When referencing a cell using sheet.getRange() the parameters are Row and then Column. So cell B1 is referenced as (1,2) in Apps Script. Music courtesy of CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #googleappsscript

Conditional Formatting includes an option to format cells if the date is after a certain date, which date can be created by formula such as =TODAY()+30. Music courtesy of CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide

Google Sheets: Filter Every Nth Row.-----.Use MOD(ROW(),n) inside the FILTER() function to return every N rows. Music courtesy of CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars

Google Sheets: JOIN() vs TEXTJOIN(). The optional ignore_empty parameter in TEXTJOIN() makes it useful in some cases where JOIN() alone isn't. CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars

Get Usable Numbers from Text. REGEXEXTRACT() can get numbers out of a text cell, but the output remains text. Wrap it in VALUE() to make it usable as a number. CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars

FILTER() enables arrays itself without the need of ARRAYFORMULA() or any other array-enabling function. Music courtesy of CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars

Use IF(e.range.getA1Notation() != "cell reference") return; to stop an onEdit script executing if the wrong cell is edited. CORTEXX. #googlesheets #googleappscript #googlesheetstutorial #googlesheetstips #googlesheetsguide

IMPORTRANGE can now directly pull a single column from a Table. Music courtesy of CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars

IMPORTRANGE now supports table names. Recent update: table names directly in IMPORTRANGE()! No more convoluted work arounds!. CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide

Use the label clause of the QUERY function to add, adjust, or remove column headers in the output. Music courtesy of CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide

TOCOL sings ♫ Anything you can do I can do better ♪ to FLATTEN. Music courtesy of CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide

EOMONTH() returns the last day of a given month, but you can also get the *first* day of a month by adding 1 to the function output!. Music courtesy of CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide

Use the ROUNDUP() function to quickly put numbers into equal-sized buckets. Music courtesy of CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide

Generate column letters for QUERY() with JOIN(SUBSTITUTE(ADDRESS(SEQUENCE()))) - less tedious and error-prone!. CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide #starwars

Generate Column Letters With a Formula.-----. Use ARRAYFORMULA(REGEXREPLACE(ADDRESS(SEQUENCE())) to generate sequential letters with a formula. Music courtesy of CORTEXX. #googlesheets #googlesheetstutorial #googlesheetstips #googlesheetsguide