New formula to calculate points base on cell having 'X' value

Question

trying to use "x" as marker. Need formula to count each 'X' in a column (with different point values) to tally all points ie: 3 boxes marked in column A (column B worth 5 points ea)= 15 4 boxes marked in column B (column C worth 4 points ea)= 16

=IF(COUNTIF(B10:B18,"x")"*5")

please mock up data and expected output. It is easier to "see" than to read. — Scott Craner, Mar 21 '23 at 18:57
Assuming A is the first column: `=COUNTIF(B10:B18,"x")*(7-MIN(COLUMN(B10:B18)))` put that in Column B and copy over. — Scott Craner, Mar 21 '23 at 19:06

score 1 · Answer 1 · answered Mar 21 '23 at 23:29

1

Use a separate row to store the values for each column (Row 2 in this example).

If your table starts in cell A1, put the formula =COUNTIF(B3:B7, "x") * B2 in B8, and copy to other tally cells.

This formula is cleaner and easier to maintain; if you need to change the values for each column, you won't have to edit formulas.

answered Mar 21 '23 at 23:29

RichardCook

846
2
10

2

Instead of saying thanks, please mark the answer as accepted if it solved your problem (click on the check mark beside the answer). And then delete both of our comments, since they don't add to the discussion. :) – RichardCook Mar 22 '23 at 12:23

David Leal · Answer 2 · 2023-03-24T04:17:53.680

You can use an array approach that spill the entire result at once and extracts directly the numbers to multiply (m) from the header. It assumes the number can be extracted on each cell looking backward for the first ending parenthesis, then the first opening parenthesis, via TEXTBEFORE and TEXTAFTER functions. On cell B7 use the following formula:

=LET(in, B2:F6, h, B1:F1, m, 1*TEXTAFTER(TEXTBEFORE(h,")",-1),"("),
 cnts, MAP(SEQUENCE(,COLUMNS(m)), LAMBDA(i,SUM(N(INDEX(in,,i)="x")))),m*cnts)

Here is the output:

If you want to replace 0s with an empty string as you have in your screenshot, then replace m*cnts as follows: IF(m*cnts=0,"", m*cnts).

If you asked yourself why I used MAP instead of BYCOL?, which is a more intuitive option. You can check the answers to the following questions:

How to make lambda function working on excel365? my answer and from @EEM.
TEXTSPLIT combined with BYROW returns an unexpected result when using an array of strings as input answer from @JvdV.

Anyway, you can use it via Implicit intersection operator (@) as follows:

=LET(in, B2:F6, h, B1:F1, m, 1*TEXTAFTER(TEXTBEFORE(h,")",-1),"("),
 cnts, BYCOL(SEQUENCE(,COLUMNS(m)), LAMBDA(i,SUM(N(INDEX(in,,@i)="x")))),m*cnts)

New formula to calculate points base on cell having 'X' value

2 Answers2