Checkbox COUNTIF

What's the rule for when a factor should be 3 or 7? If there isn't one, then add a helper column:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C
3	Factor 1		3
4	Factor 2		3
5	Factor 3		3
6	Factor 4		3
7	Factor 5		7
8	Factor 6		3
9	Factor 7		3
10	Factor 8		3
11	Factor 9		3
12	Factor 10		3
13	Factor 11		3
14	Factor 12		3
15	Factor 13		3
16	Factor 14		3
17	Factor 15		7
18	Factor 16		3
19	Factor 17		7
20	Factor 18		3
21	Factor 19		7
22	Factor 20		3
23	Factor 21		3
24	Factor 22		3
25	Factor 23		3

Sheet: Sheet1

=IF(B1="","",SUMPRODUCT((B3:B25=TRUE)*C3:C25))

Without a rule, there's no simpler way of doing it. Sorry!

Your current formula is this:

=IF(B1="","",COUNTIF(B3:B6,TRUE)*3+COUNTIF(B7,TRUE)*7+COUNTIF(B8:B16,TRUE)*3+COUNTIF(B17,TRUE)*7+COUNTIF(B18,TRUE)*3+COUNTIF(B19,TRUE)*7+COUNTIF(B20,TRUE)*3+COUNTIF(B21,TRUE)*7+COUNTIF(B22:B25,TRUE)*3)

You have said that there is no rule as to which rows will be weighted 3 and which 7, so I understand it to be (a) random and (b) likely to change.

Without a rule you can't shorten the formula. I suggested a helper column, but this now appears not to be feasible.

The weighting is not random and the weighting of the factors does not change.

weighting are based upon factors that I assumed were irrelevant to the count. But perhaps I'm wrong. See below for and example of one of the rules.

The weighting for Factor 4 (row 6) is based upon the percentage of times a contestant finished 2nd or 3rd. If its from 10% - 19%, the checkbox gets ticked and they receive 3 points.

The weighting for Factor 5 (row 7) is also based upon the percentage of times a contestant finished 2nd or 3rd. However, for the checkbox to get ticked for Factor 5, the percentage must be greater than or equal to 20% and the contestant receives 7 points.

Note: only one checkbox gets ticked according to the rule above. In other words, if the percentage is 20% or higher only Factor 5 gets a tick in the checkbox. If the percentage is less then 10% then neither checkbox gets ticked.


Not sure if this helps but that's an example of the rules and I don't see how it's relevant to the formula using COUNTIF. Either way, you have said its not possible to shorten the formula without a helper column.

Unless I’m missing something, all there is in the workbook is a list of factors, a checkbox list and your formula. I don’t see anything else. Maybe you need to put this in a broader context, but as it stands, I don’t see how it’s possible to shorten your formula without a helper column.

This formula is equivalent to your formula, but shorter:

Please try:

Formula:

=IF(B1="","",SUM(--B3:B25))*3+(B7+B17+B19+B21)*4

You are Welcome!

Thanks for the feedback and rep .


Glad to have helped.