Total SUM of column A if check box in column B is checked (HELP)

[xcapes]

I want to be able to add all the prices of each row item if the item is checked off...

so for example:
A B C
item1 $5 checked (assume a checkbox)
item2 $3 not checked
item3 $4 cheked

total $9

I know this can be done but being as I've never written VBA for excel im a little unsure of how to go about it (i've written many Access applications using VBA)...

I know I will need something of this sort but again, not sure how to best implement it:

Dim ctrl As Control
For Each ctrl In activesheet.Controls
If TypeName(ctrl) = "CheckBox" Then
add the items...
End If
Next ctrl

Thanks for your help!

 

[antoineh]

Using vba for excel is not difficult if you are familiar with vba for access.
see the help in excel/vba, every thing is explain. the thinking logic is quite different from access, but easy to get.
but with excel, you'd better use the cell "regular" calculation of the spreadsheet and not use any vba...
make a colum where each row is calculated with the if function : if(column B = true;A;0) and at the bottom, a cell with the sum of this column...
It may even work if you set this cells with a simple * opération (=Bx * Ax)
and no vba if you can...

 

[IFRs]

Can you use the built in function SumProduct?

 

[rss01]

this is using the SUMIF function. I didnt use a checkbox for the example, i put an "X" in column C. But this could be changed for that.

=SUMIF($C$2:$C$7,"X",$B$2:$B$7)

 

[SkipVought]

X,

Are you saying that you have 3 checkboxes for each row of data?

I did not understand your example which had 3 columns, (A, B, C) and then 3 items, one UNDER the other (does this imply rows or is it columns?)

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xcapes]

Wow! Thanks for all the quick replies.

antoineh, If i can do it without using VBA then all the better... The problem I encountered when trying to use a function like IF or SUMIF was that I was not getting a value for the checkbox when checked (maybe i'm missing how to do that...)

SkipVought, There is only one checkbox per row. The A, B, C are the columns and the item1, 2, 3 are the rows...

Thanks again for helping me out!

 

[PHV]

Just link each CheckBox with the corresponding Cell in column C, and then the SUMIF can be used.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

The SIMPLEST approch would be to link a cell to each checkbox and then use rss01's SUMIF formula. No VBA that way.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xcapes]

Thanks!

I'll do a search on the help for linking a cell to a checkbox and then use the SUMIF...

Thanks for all the help!

 

[SkipVought]

Are you using Forms or Control Toolbox?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xcapes]

Forms

 

[PHV]

Right click the checkbox --> Control format
In the "Control" Tab you can choose the linked cell.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xcapes]

That worked great!

Thanks! Talk about saving me a lot of time!