Hey besties!
Today, I want to share an incredible trick with you on how to use the SUMIFS function in Google Sheets. If you’re a teacher like me and you’re trying to track data from tests and their retakes, this trick will be a game-changer for you.
Imagine you have a spreadsheet with different columns: Column A for student numbers, Column B for scores on the original tests, Column C for scores on the test retakes, and Column D for score changes between the original test and the retake.
Well, let me tell you, in my school, we use a grading system based on standards. Students receive a grade of 1, 2, 3, or 4 on each test. My goal is to calculate the total number of students who scored a 4, 3, 2, or 1 on the original tests. For example, if I have 30 students and 16 of them scored a 4, 10 scored a 3, and 4 scored a 2, then the total of “Received a 4” will be 16, the total of “Received a 3” will be 10, and the total of “Received a 2” will be 4.
Now, here’s where things get interesting. If a student scores a 1 or 2 on the original test, they are required to retake the test after a new explanation of the lesson, to have the opportunity to improve their score. Similarly, if a student scores a 3 on the original test, they can also participate in the retake to improve their score. I have a Column C in my spreadsheet to track the retake scores, and I want to calculate the total number of students who scored a 4, 3, 2, or 1 on the retakes in the same way as the original tests.
The challenge lies in the formula to use for calculating the scores. If a student’s score in Column B is higher than the score in Column C, I want to use the score from Column B to calculate the totals of 4, 3, 2, and 1 in Column B. However, if a student’s score in Column C is higher than that in Column B, I want to use the score from Column C for the totals in Column B.
For example, let’s take two students, #1 and #2. Student #1 scores a 4 on the original test, and student #2 scores a 2 on the original test. The total of “Received a 4” will be 1, and the total of “Received a 2” will be 1. Student #2 retakes the test and scores a 3. I want my formula to compare the scores in Column B and Column C and use the higher score to calculate the totals. In this case, after the retake, I will get the following results: the total of “Received a 4” will be 1, and the total of “Received a 3” will be 1. The score for “Received a 2” will no longer be present because the system did not include the score of student #2 from Column B since it was lower than that in Column C. Since Column C had a higher score, the formula used the total from Column C to calculate the overall totals.
I hope I explained what I’m looking for clearly, as it’s crystal clear in my mind. I’m convinced that there’s a formula that can do this, and I hope you can help me out. Thank you for your time and effort because this task has been giving me a hard time.
If you want to learn more about Google Sheets and other useful tips, feel free to visit Crawlan.com. It’s a treasure trove of information for all your marketing and online tools needs.
See you soon with more awesome tips!
Image Source: Crawlan.com
