How to use the AND and SUMIF Functions in Excel

Two more useful Conditional Functions are AND( ) and SUMIF( ). We'll stay with the students exam results to examine these two functions. If you haven't yet downloaded the spreadsheet we're using for these lessons, click below. (If you've been following along, you can use this new spreadsheet: it has the CountIF already done!)

Download the spreadsheet for this section

The AND Function

Here's the scenario. The government have decided to take a closer look at school averages. If 4 or more students in a subject get a score of 70 or above, then the school gets a cash bonus for that subject. There are 8 subjects, so 8 cash bonuses are up for grabs.

First, we'll use the AND function to work out if 4 or more students have scores of 70 or above.

The AND( ) function checks a list of arguments and determines whether they are true or false. If all the arguments are true then the function gives you an answer of TRUE. If one or more of the arguments are false, then the function gives you an answer of FALSE.

To give you an easy example. You can check whether two plus two does indeed equal 4. Click inside an empty cell of your spreadsheet, then click inside the formula bar. Enter this:

=AND(2 + 2 = 4)

When you press the Return key, Excel gives you the answer TRUE. Now change it to 2 + 3 = 4 and see what happens.

So Excel checked the argument to see whether it was true or false. That's all it will check for, an either or answer. You can have up to 30 arguments between the AND brackets. You could have this, for example:

= AND(2 + 2 = 4, 1 + 2 = 4)

There's two arguments to check there. Excel will check the first one and return an answer of TRUE. When it checks the second one it will return an answer of False. The answer to the whole function will then be FALSE. (It's false because all conditions have to be true before the overall answer is TRUE.)

Our AND function is going to be quite simple. We're going to check the B column, the Number of students who have Below Average scores.

= AND(B15 >= 4)

All we're saying in our function is "IF the cell B15 is greater than or equal to 4 THEN put True in cell C15, else put False". Remember: the government pays out if 4 or more students get above a score of 70 for a lesson

Now that we have some True/False values for our C column, we can check all these True/False values. We want to add up all the cash values associated with our TRUE cells. However, we haven't got any cash values yet, so let's do that now. Enter the same cash values as in the image below:

The values are just potential values. A school only gets them if 4 or more students gain a score of 70 or above in that subject. So there's a potential ten thousand for Maths, five thousand for English, 8 thousand for Science, etc.

OK, we have 2 cells with TRUE in them. There is a different cash value associated with each subject. We have a TRUE for English, so the school will receive 5 thousand pounds for this. The school can expect 10, 000 for Maths. But we need a way to add all the cash values associated with the TRUE values. We can use SUMIF for this.

SumIF

SUMIF is a little bit more complicated than AND, but not much more. This function adds up things depending on the criteria you give it. (Add up the cost of all apples, for example.) The function expects certain arguments. These are:

SUMIF(range, criteria, sum_range)

The first argument, range, is the cell or cells you want to check. For us, this would be the TRUE and FALSE values in the D column. The second argument, criteria, is what you want to check for. In our case this is the value TRUE. The third argument, sum_range, are the cells to add up. The figures we want to add up are all in the E column.

=SUMIF(C15:C22, TRUE, E15:E22)

If everything went well, then you should have a figure of 11 thousand for your SUMIF function. Your spreadsheet might look like the one below:

So our SUMIF function said "Check the cells C15 to C22. If a cell has TRUE in it, make a note of the ammount in the E cell next to it. When you've finished, add them all up."

The SUMIF might be a bit tricky to master, but it can come in quite handy, and it's worth making the effort to understand exactly how it works.

In any case, that concludes are little journey into conditional logic. Hope you're not too disappointed to be leaving the subject behind!

In the next section of the Excel course, we'll take a look at Tables, Scenarios and Goal Seek.

Move on to the next section of the Excel course -->

<--Back to the Excel Contents Page