1

Very new to Google sheets and very lost.

I am trying to create an awards tracker for school Merits 2024

Ideally, I want to include drop down menus with options for each week we hand out merits at school e.g. Week 1, Week 2 etc. Depending on the week, these awards are worth either 5 or 10 points. (Wk 4 and 7 are 10 points, all other weeks are 5 points.)

My aim is to then have a total column that keeps track of how many points students should have (Column B in example.) E.g. If I get a merit in Week 4, then 10 points gets added to the total in column B.

I have also added conditional formatting to this column so that depending on the points, I know what level they are on e.g. bronze, silver and gold.

Any help would be greatly appreciated

Improve this question
1
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Commented Apr 8, 2024 at 5:52

2 Answers 2

Reset to default
1

One way is to use map() and ifs(), like this:

=sum(map(D2:2, lambda(week, ifs( week = "N/A", 0, week = "", 0, week = "Week 4", 10, week = "Week 7", 10, true, 5 ))))

See map() and ifs().

Improve this answer
2
  • Thank you! That worked! Commented Apr 9, 2024 at 6:20
  • Thanks. Yes and I am very lost haha Commented Apr 10, 2024 at 1:35
0

Using REGEXMATCH

# SYNTAX =REGEXMATCH(text, regular_expression)

Single Row Formula

Needs to be copied down for each row.

=INDEX( SUM( REGEXMATCH(D2:E2, "4|7") * 10, REGEXMATCH(D2:E2, "[^47\D]") * 5))

Multi-Row Formula

BYROW is added so a single LAMBDA's formula can return the results for all rows that have a student listed.

=BYROW( FILTER(D2:E, A2:A<>""), LAMBDA(r, INDEX( SUM( REGEXMATCH(r, "4|7") * 10, REGEXMATCH(r, "[^47\D]") * 5))

Explanation

Regular Expressions

  1. REGEXMATCH is used within INDEX to return TRUE/FALSE values for the range (row) rather than just one cell:

    # Single Cell Version =REGEXMATCH({"Week 7", "Week 2"}, "4|7")=TRUE =REGEXMATCH({"Week 7", "Week 2"}, "[^47\D]")=FALSE
     # INDEX (Array) Version =INDEX(REGEXMATCH({"Week 7", "Week 2"}, "4|7"))={TRUE, FALSE} =INDEX(REGEXMATCH({"Week 7", "Week 2"}, "[^47\D]"))={FALSE, TRUE}

  2. Sheets will coerce TRUE & FALSE to 1 & 0 if used in a calculation that requires digits. This behavior is leveraged to multiply the resulting array of matches for 4 or 7 by 10 and the other matched numbers by 5:

    # TRUE & FALSE to 1 & 0 =INDEX(REGEXMATCH({"Week 7", "Week 2"}, "4|7")*10) =INDEX({TRUE, FALSE}*10) ={1*10, 0*10} ={10, 0}
     =INDEX(REGEXMATCH({"Week 7", "Week 2"}, "[^47\D]") * 5) =INDEX({FALSE, TRUE} * 5) ={0*5, 1  *5} ={0, 5}

  3. All that remains is to SUM the two arrays to get the total.

    # Where range={"Week 7", "Week 2"} =INDEX(SUM(REGEXMATCH(range, "4|7") *10), REGEXMATCH(range, "[^47\D]") *5) =INDEX(SUM({TRUE, FALSE}*10, {FALSE, TRUE} * 5)) =INDEX(SUM({10, 0},{0, 5})) =15
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.