Forum Discussion
Formula Help - Checkbox triggers and sums
I am working on a sheet where I have users select a check box if a variable is true and then have that generate a number value in another cell that works as a score. The formula I am using to generate the number values is =IF(D4,"0",""). I want to sum that column but I get an error if I try simple addition (=E4+E5+E6=E7+E8+E10+E11+E12) and nothing if I use an =SUM() formula. I assume that I am not using the best approach to solve this problem and could use some help. Thanks.
Hi Bryan,
You have to generate something countable, so please replace this:
=IF(D4,"0","")
With this:
=IF(D4,1,0)
Or directly you can sum the range of TRUEs and FALSEs using this formula:
=SUMPRODUCT(--(D4:D12))
Regards
Haytham
8 Replies
Hi Bryan,
You have to generate something countable, so please replace this:
=IF(D4,"0","")
With this:
=IF(D4,1,0)
Or directly you can sum the range of TRUEs and FALSEs using this formula:
=SUMPRODUCT(--(D4:D12))
Regards
Haytham
Haytham;
I am trying to create an excel form that will keep track of thermocouple uses. We need to ensure that the uses do not exceed a certain amount. I have set each row up as an individual thermocouple listed in the first column. The subsequent columns have two form checkboxes in each cell, one for a use over a temp and one for use under a temp. the user would check the box each time the thermoucouple is used and they would check the box based on the usage temperature. I need to create a cell that will sumif the checkboxes which are checked into these two categories. I envision two sum boxes, one for the high temp and one for the low temp. I cant seem to figure out what to sum or how to count the number of TRUE inputs for the checkboxes. Any help greatly appreciated.
Regards
Phil
Hi PhilHSEP ,
You can use the SUMPRODUCT function
You have two columns, each has true or false values, so basically if you do an arithmetic operation, the TRUE will become 1 and the FALSE will become 0.
so for each column, you can use SUMPRODUCT.
if you true and false values are in cells A1:A5 for example, =SUMPRODUCT(--($A$1:$A$5))
these two negative signs will be considered as an arithmetic operation and will force the conversion of TRUE and FALSE values to 1s and 0s.
hope that helps. let me know if you have any other questions.
Abdelrahman Abdou,
Certified Microsoft Excel Expert, Consultant.
Founder, Owner @ ExcelBonanza.com
http://www.excelbonanza.com/ |
https://www.linkedin.com/in/abdelrahman-abdou-60240150 | https://www.youtube.com/channel/UC2-D2unZHustY_f6V7cj03Q | https://www.facebook.com/ExcelBonanza/
Awesome! Thanks. That adapted to what I needed. Appreciate the help.
