Sum multiple sheets with check boxes

tmmcentyre

New Member
Joined
Oct 30, 2018
Messages
5
I'm trying to sum quantities (C7) from multiple sheets (Sheet2:Sheet11) onto a summary sheet (Sheet1) using check boxes. I have the check boxes (G6:G15) set up on Sheet1 but when I check more than one box it only shows the topmost checked value. I need it to sum the values whether I check one or more or all boxes. This is my formula so far...

=
IF(G6,(Sheet2!C7),
IF(G7,(Sheet3!C7),
IF(G8,(Sheet4!C7),
IF(G9,(Sheet5!C7),
IF(G10,(Sheet6!C7),
IF(G11,(Sheet7!C7),
IF(G12,(Sheet8!C7),
IF(G13,(Sheet9!C7),
IF(G14,(Sheet10!C7),
IF(G15,(Sheet11!C7),
""))))))))))

I think I broke my Google button on this one!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
try changing all the commas between the ifs to + signs
you can do this with a global edit ctrl H the enter ), tab )+ enter
 
Upvote 0
If I understand it well, your checkbox returns true or false in G6 to decide if you want to include or exclude sheet2!C7 of the sum, same thing for g7 and sheet3, etc.

Then maybe
Code:
=sum(if(G6=true,[COLOR=#333333]Sheet2!C7,0),if(g7=true,[/COLOR][COLOR=#333333]Sheet3!C7,0),[/COLOR][COLOR=#333333]if(g8=true,[/COLOR][COLOR=#333333]Sheet4!C7,0),[/COLOR][COLOR=#333333]if(g9=true,[/COLOR][COLOR=#333333]Sheet5!C7,0),[/COLOR][COLOR=#333333]if(g10=true,[/COLOR][COLOR=#333333]Sheet6!C7,0),[/COLOR][COLOR=#333333]if(g11=true,[/COLOR][COLOR=#333333]Sheet7!C7,0),[/COLOR][COLOR=#333333]if(g12=true,[/COLOR][COLOR=#333333]Sheet8!C7,0),[/COLOR][COLOR=#333333]if(g13=true,[/COLOR][COLOR=#333333]Sheet9!C7,0),[/COLOR][COLOR=#333333]if(g14=true,[/COLOR][COLOR=#333333]Sheet10!C7,0),[/COLOR][COLOR=#333333]if(g15=true,[/COLOR][COLOR=#333333]Sheet11!C7,0))[/COLOR]
?
 
Last edited:
Upvote 0
Worked like a charm! Thanks!!

If I understand it well, your checkbox returns true or false in G6 to decide if you want to include or exclude sheet2!C7 of the sum, same thing for g7 and sheet3, etc.

Then maybe
Code:
=sum(if(G6=true,[COLOR=#333333]Sheet2!C7,0),if(g7=true,[/COLOR][COLOR=#333333]Sheet3!C7,0),[/COLOR][COLOR=#333333]if(g8=true,[/COLOR][COLOR=#333333]Sheet4!C7,0),[/COLOR][COLOR=#333333]if(g9=true,[/COLOR][COLOR=#333333]Sheet5!C7,0),[/COLOR][COLOR=#333333]if(g10=true,[/COLOR][COLOR=#333333]Sheet6!C7,0),[/COLOR][COLOR=#333333]if(g11=true,[/COLOR][COLOR=#333333]Sheet7!C7,0),[/COLOR][COLOR=#333333]if(g12=true,[/COLOR][COLOR=#333333]Sheet8!C7,0),[/COLOR][COLOR=#333333]if(g13=true,[/COLOR][COLOR=#333333]Sheet9!C7,0),[/COLOR][COLOR=#333333]if(g14=true,[/COLOR][COLOR=#333333]Sheet10!C7,0),[/COLOR][COLOR=#333333]if(g15=true,[/COLOR][COLOR=#333333]Sheet11!C7,0))[/COLOR]
?
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top