Tag: countif

Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values.

Google Sheets: COUNTIF, ARRAYFOMULA, FILTER, REGEXMATCH, REGULAR EXPRESSIONS (RE2)

Okay, wait! Stop!…

…I know the byline contained regular expressions, but I promise you I won’t just leave you with the formula for you to figure out your own jumbled mass of jibberish characters or inundate you with a list of commands and leave to attempt to piece it all together.

I promise to give you some clear examples with an explanation of each to you can apply it to your project.

Take a deep breath, mate, and let’s get cracking.

We are going to look at two related scenarios:

Scenario 1:

Imagine that you have a huge list of items. You have a hunch that some of the cells contain certain values of interest for you. You want to build a new list with only those values in them.

Imagine that you have a list of full names, and you want to use Google Sheets to create a new list of full names that only contain John.

Scenario 2:

You have that same huge list again, but this time you only want the total count of all the rows that contain certain values in each cell.

Imagining that list of full names again, you are now going to get a total count of all full names that contain John in it.

 

We’ll first go through how to create these formulas and then provide a number of clear examples on some common criteria for searching cell for values using REGEXMATCH and regular expressions.

Continue reading “Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values.”

Google Sheets – Remove The Lowest Grade for Each Student on a Course

Google Sheets – MIN, FILTER, INDEX, MATCH, SUM, COUNTIF

In the region of the world that I work in, it is a pretty common occurrence for university courses to run weekly assessment. At the end of the course, all the weekly assessment is then added together minus the lowest piece of assessment.

For lecturers with small course sizes, this is a pretty simple task that you could simply eyeball if you have a small enough group, but what if your course runs into the thousands with half a dozen tests to choose from. Eyeballing is just not going to do it.

Recently I was asked to do the same thing for the program that I manage. Over an 8 week term, we run 7 assessment at the end of each week for our students.  My job was to find the lowest grade out of the 7 assessment and drop it, taking note of the assessment unit that I dropped for each student. 

I use Google Sheets for this purpose for its ease of use and sharability.

This is an example dataset of the 7 assessment (in this case, weekly tests) in Google Sheets. We need to remove the lowest grade from each student. As you can see not all students have their lowest grade in the same Unit test. 

List of 7 grades for each student - Google Sheets

Continue reading “Google Sheets – Remove The Lowest Grade for Each Student on a Course”

How do I lock certain cells in a formula in Google Sheets?

You know, I’m pretty confident in my use of Google Sheets. I use a lot of its advanced features and formulas in my daily work.

But one thing was bugging me. Every time I created a formula and then grabbed and dragged (or double-clicked) that little box in the bottom right of the cell to have it repeat, the cells would change.

The little square down the bottom of the cell

This normally is great, because it moves by a cell each time and makes my life easier.

What if I want to keep a certain cell constant in a formula?

Enter the Absolute Reference.

Continue reading “How do I lock certain cells in a formula in Google Sheets?”

%d bloggers like this: