exceltoxl.com

Hi there,

One of the Excel Lover tried summing a list using check box. This is something like thinking out of box. Are you eager to know how to do this. Here we go:

PRE WORK:

Set the raw data to do this. Here, I am using the Vegetables bought from the market today. Here, I have to disclose you how typically our villagers market their products.

In my village we will have market open on Wednesday, which means sellers from different place comes to our village to sell their goods. Goods interms of Vegetables, leafy vegetables, Stationary, Groceries and all kinds of petty shops for that matter. Every one comes to one village, Wednesday is in our village, and sells their goods and go back in the evenings. Next day will be the other village which is closer to our village. This is how the routine work of the sellers in the villages.

...

So, here is my data, I am using the simple and common data to work with the subject line.

 

HOW TO ENABLE DEVELOPER TAB?

To prepare a check box, first you need to enable the "DEVELOPER" tab using "Excel Options" and click on "CUSTOMIZE RIBBON" to get DEVELOPER tab which is unchecked. Check mark it and click on OK to reflect in the ribbon list next to VIEW tab.

 

HOW TO PREPARE A CHECK BOX?

In the DEVELOPER tab, goto CONTROLS group and click on INSERT button. Now, from Form Controls select the check box. Here the cursor changes to + plus symbol. Drag the cursor on the cell to appear the check box button on the worksheet. Here it is cell E3.

Right click on the check box and goto FORMAT CONTROL option. Last tab, we have "Control", here we have cell link either you can type the cell address as $D$3 or click on the button next to it and place the target cell ($D$3) which takes the cell address automatically.

 

Now, try to check the box and uncheck the box. This will show you TRUE when checked and FALSE when unchecked in cell $D$3, right?

Yes, with this you have come to half of the work... Yehhhhh!

 

To learn more about ActiveX Controls and Form Controls, check this PART1 and PART2 here.

Let's move on further...

 

Now is the challenge work. Most Excel users are thinking "Come on Man! Reveal the Shit First !!!" right? :)

 

Ok, we are there now, Use the below formula in $F$3.

 

=SUMIF(D3:D3,TRUE,C3:C3)

 

which means sum if D3:D3 is True then sum the values of C3:C3

 

This will display the value of B3 in E3. Are you in the same page?

Good !

Now, apply the same procedure for all the cells below. Apply the check boxes to each cell and also use the formula like above.

 

The simple way to do this is select the cells from D3:F3 till D8:F8 and use CTRL + D which fills downwards. It does it for us. Very simple right! :)

 

Don't leave it here, now you need to change the cell references to the check boxes to work effectively.

 

Now, use the regular SUM function below the table and select the range as F3:F8 and see the magic.

 

Check mark to add the totals and uncheck for not adding the totals.

 

COooooool right!

Yes, finally either you can hide the columns D and F or you can change the font color to background color.

That's it Magic works!!!

 

Download the working file .xls from here.

 

Alternative link to watch this video.

 

DID YOU EVER SUM USING CHECK BOXES?

If you have tried this in your work and you have different experiences sharing with us, please do use our space in the below comments section to post your views.

If you like this post, please do share it on your social networking sites just by clicking on the icons placed around the blog.

If you want to support me and to join into our team to receive monthly newsletters, then REGISTER here, its free and secure.

 

SOME IMPORTANT LINKS:

 

• Convert an Excel Chart into .GIF file
  
  
• Volatile Functions in Excel
  
  
• Cooked Dynamic Charts - 3 flavors
  
  
• Double Click Tricks - Very Cool ah!

Thanks,