Extending Spreadsheet Using Excel Sumif And If Conditions

Excel function tutorials

Today I received an interesting email from a reader that had been playing around with a spreadsheet that you might remember seeing. First off, take a look at the spreadsheet. Basically, the reader wanted to add a few different functionalities which gave me some ideas to add even more. I always like to go over such problems because it helps me to add more examples of simple excel functions that become much easier to work with when used in easy to understand worksheets.

Task #1-Giving A Prize In Courses Where 4 Students (or more) Got A Score Of 70%

This was done by first writing down the courses and then using the countif function. You can find out more about the countif function here. Here is what I ended up using for maths:

=COUNTIF(B2:I2, “>=70”)

I dragged the formula down and you can see the result here:

Then, for each subject, I will simply verify that the number of students is 4 or more and write “Prize” if so or “False” if not. Here goes:

=IF(B15>=4,”Prize”,”False”)

And the new result:

Now, another interesting thing would be for students with one of the 2 best grades in 3 or more subjects to be awarded a “distinction”. How could this be done? I think the easiest would be to use the rank function. For example, to get Steven’s rank in Maths, I would use:

=RANK(B2,$B$2:$I$2)

The result is 3. So I would need to verify how many such rankings are 1 or 2. Unfortunately, in this case, I will likely be forced to use a longer formula:

=IF(RANK(B2,$B$2:$I$2)<3,1,0) This would give me a 1 if the condition is accomplished and a 0 if not. I will then do this for each subject so: =IF(RANK(B2,$B$2:$I$2)<3,1,0)+IF(RANK(B3,$B$3:$I$3)<3,1,0)+IF(RANK(B4,$B$4:$I$4)<3,1,0)++IF(RANK(B5,$B$5:$I$5)<3,1,0)++IF(RANK(B6,$B$6:$I$6)<3,1,0)++IF(RANK(B7,$B$7:$I$7)<3,1,0)++IF(RANK(B8,$B$8:$I$8)<3,1,0)++IF(RANK(B9,$B$9:$I$9)<3,1,0) Then, if all of that is 3 or more, you would get a "Yes", if not "No" so: =IF(IF(RANK(B2,$B$2:$I$2)<3,1,0)+IF(RANK(B3,$B$3:$I$3)<3,1,0)+IF(RANK(B4,$B$4:$I$4)<3,1,0)++IF(RANK(B5,$B$5:$I$5)<3,1,0)++IF(RANK(B6,$B$6:$I$6)<3,1,0)++IF(RANK(B7,$B$7:$I$7)<3,1,0)++IF(RANK(B8,$B$8:$I$8)<3,1,0)++IF(RANK(B9,$B$9:$I$9)<3,1,0)>=3,”Yes”,”No”)

This gives me 3 students with a high distinction as you can see here:

I also invite you to download the spreadsheet here.

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

One Feedback on "Extending Spreadsheet Using Excel Sumif And If Conditions"

manoj

Really interesting , need to learn more