How to I count a total of hours based on if a checkbox is populated?
I would like to use a functionality of totaling a training duration based on if that course was selected.
Ex: Session A is 1.25hrs, user checks the box for this class  I need to be able to sum up the total time based on all the populated checkboxes.
any help would be greatly appreciated.
thanks
Judi
Best Answer

Hi @Judi Hancock ,
Thanks for sharing the screen shots. The way you have this set up the formula in the Client Hours Column would be =IF([Client Total]@row=1,Hours@row,""). The formula says if the Client Total box is checked then make Client Hours = to Hours, otherwise leave it blank.
The SUMIFS formula I started with would determine which boxes are checked and generate the total without using the Client Hours column. That formula would be =SUMIFS(hours:hours, [Client Total]:[Client Total], =1)
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers

Hi @Judi Hancock ,
Looks like a SUMIFS should work for you. The formula would be:
=SUMIFS(hours:hours, checkbox:checkbox, =1) where hours is the name of your session time column and check box is the name of your course selection column. Replace with your actual column names.
This formula will sum the hours where the check box is checked (=1). If you need to match houses to person you'd add another range and criterion like person:person, =person@row.
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

I got a Incorrect argument set
=SUMIFS(Hours@row, [Client Total]@row = 1)
The total hours for one course is 2.5 hours  I want to be able to check the box for this course and add the 2.5 hours to the client total hours column.

Can you attach a screenshot of your sheet so I can see the structure?
SUMIFS need to start with the range of cells you want to sum if they meet the criteria. I think you'll want to use =SUMIFS(hours:hours, [client total]:[client total], =1) But seeing your screen will let me confirm.
Happy to help you.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

I am trying to check the box in the Client Total column that will add the hours (2.4hrs) to the Client Hours column  then I can total all the training hours on the bottom.
Thank you for all your help!


Hi @Judi Hancock ,
Thanks for sharing the screen shots. The way you have this set up the formula in the Client Hours Column would be =IF([Client Total]@row=1,Hours@row,""). The formula says if the Client Total box is checked then make Client Hours = to Hours, otherwise leave it blank.
The SUMIFS formula I started with would determine which boxes are checked and generate the total without using the Client Hours column. That formula would be =SUMIFS(hours:hours, [Client Total]:[Client Total], =1)
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Thank you it worked!!! Thank you so much for taking the time to explain to me what I was missing. I truly appreciate it!
Judi
Help Article Resources
Categories
Check out the Formula Handbook template!