First of all hello, Excellers. A warm welcome back today for another #Excel Tip in my #FormulaFriday series. I will share today a neat way to use the conditional SUM function. I am basing this condition today on cells being ticked or unticked. This tip is based on a question from a subscriber who wanted to sum only the lines on their expenses sheet that’s have been paid. These are identified by being ticked on the Excel worksheet. So, let’s write the solution to this question.
The sample extract of the lists above demonstrates a list of expenses. A CheckBox in Column C will determine the status of the expenses.
How The Excel CheckBox Works.
The CheckBox in Excel simply has a status of TRUE (ticked) or FALSE (un-ticked). This is pretty simple. So, once this is linked to a cell, use the conditional SUMIF function to SUM the expenses. It is based on being TRUE – Paid or FALSE-Unpaid. The tick box is linked to cell C3. Below is how the TRUE or FALSE is displayed.
Adding A CheckBox In Excel.
The CheckBox control is available in the Developer Tab. If you cannot see the Develope Tab you will need to enable it. You can watch my YouTube Video on how to do this using the link below.
Now, I can insert the CheckBox on the Excel Worksheet.
- Developer Tab
- Insert | Form Controls
- Select CheckBox and place where you want to on your Excel Worksheet
Next stage is to customize your CheckBox using the Format Control Option. Access this by right-clicking on the CheckBox. This is where we select the cell link option that will be used on our conditional SUM or SUMIF Formula.
I have set up the cell links to this expenses sheet in Column G to the right of the CheckBox. So, if the Paid column cell is ticked the corresponding cell in Column G will display TRUE. Once unticked Exel will change this value to FALSE.
So, TRUE and FALSE are then used to SUM the number of expenses. Only those that have been paid. Those that are TRUE. Let’s get writing that formula!.
Using The SUMIF Function.
Now, let’s get back to basics really quickly and look at the syntax of SUMIF.
=SUMIF(range, criteria, sum_range)
range – It is the data range you want Excel to evaluate.
criteria – Required. It needs to be in the form of a number, expression, a cell reference, text, or a function which defines which cells will be summed. NB any text criteria or criteria that includes any mathematical or logical symbols MUST be enclosed in double quotation marks (“). For Example 31, “<31" ,A32, “Apples" etc.
Subsequently I can go ahead and write the formula as below.
So, the CheckBox has changed the cell in Column C. Finally, Excel adds the corresponding value to the total amount of Expenses Paid. How easy is that?. SUMIF is a really flexible function and combined with the CheckBox it makes a really nice solution in the example. have you used this method in an Excel template?. Share with other subscribers below.
What Next? Want More Tips?
So, if you want more top tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.