Sum multiple sheets with check boxes

[tmmcentyre]

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!

 

[offthelip]

try changing all the commas between the ifs to + signs
you can do this with a global edit ctrl H the enter ), tab )+ enter

 

[Kamolga]

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

=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]

?

 

[tmmcentyre]

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

=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]

?

 

[Kamolga]

You are welcome, thanks for the feedback!