How to I count a total of hours based on if a checkbox is populated?

Judi Hancock

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

Mark Cronk

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

Mark Cronk

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

Judi Hancock

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.

Mark Cronk

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

Judi Hancock

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!

Judi Hancock

Mark Cronk

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

Judi Hancock

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