Forum Discussion
Formula Help - Checkbox triggers and sums
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/
I tried the fix that you suggested but it does not seem to be working ? I changed the location of the cells array so that it would look for true/false in those cells where there were checkboxes. The type of checkbox I am inserting is the one that is added by clicking on developer, insert, form controls, checkbox. I edit the name to "> 1200 F" in order to identfy why a person would check the box, is this the reason that the result is not showing up in the cell I put the formula into ?
Any help greatly
You need to make sure to link the checkbox to a cell as well by filling the cell link field when you right-click on the checkbox and click on Format Control. You need to link the checkbox to a cell and this cell will have the result of checking/ un-checking the checkbox of TRUE/FALSE.
The formula I mentioned in the previous reply will help you count these TRUEs and FALSEs.
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/
Here is the file I am working on. Each thermocouple (row) would have a series of two checkboxes in each cell. Each row would have two checkboxes. There would be 180 cells for each row filled with these checkboxes. They would totalize/sum and that number of uses would display in the red or blue cells for that row... Is there an easy way to do this without using much memory or computational space?
Any help greatly appreciated....Thanks very much for your help already..
Regards
Phil