# How do I make dropdown selections add value to a totals column in Google Sheets

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

• Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
– Community Bot
Commented Apr 8 at 5:52

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().

• Thank you! That worked! Commented Apr 9 at 6:20
• Thanks. Yes and I am very lost haha Commented Apr 10 at 1:35

# 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```