Assign different letters different values across a range of cells

[mtaylor]

Hi there, using Google Sheets here

I am inputting the letters G, A and R across D5 to N5

G is to assign the value 7
A is to assign the value 4
R is to assign the value 1

I dont need to see these values just the total please

I'd like the total of these values to total into Cell: D4

Please and thank you

 

[PeteWright]

Paste the formula into cell D4:

= SUMPRODUCT( (D5:N5="G")*7 + (D5:N5="A")*4 + (D5:N5="R")*1 )

 

[mtaylor]

Paste the formula into cell D4:

= SUMPRODUCT( (D5:N5="G")*7 + (D5:N5="A")*4 + (D5:N5="R")*1 )

Thank you,
Works brilliantly.

 

[mtaylor]

What if I wanted to include some cells further along?

For example: D5:O5 but also R5:W5?

I've amended it to this: = SUMPRODUCT( (D5:O5,R5:W5="G")*7 + (D5:O5,R5:W5="A")*4 + (D5:O5,R5:W5="R")*1 )

But I get an error.

Any help would be much appreciated

 

[PeteWright]

Hi @mtaylor

What if I wanted to include some cells further along?

For example: D5:O5 but also R5:W5?

I've amended it to this: = SUMPRODUCT( (D5:O5,R5:W5="G")*7 + (D5:O5,R5:W5="A")*4 + (D5:O5,R5:W5="R")*1 )

But I get an error.

Unfortunately this doesn't work.

I'm afraid you'll need to use the SUMPRODUCT function twice like this:

= SUMPRODUCT( (D5:N5="G")*7 + (D5:N5="A")*4 + (D5:N5="R")*1 ) + SUMPRODUCT( (R5:W5="G")*7 + (R5:W5="A")*4 + (R5:W5="R")*1 )

or try the whole range starting with the very first cell and ending with the last (but every cell in between will be added if one of the values R, G, A occurs!):

= SUMPRODUCT( (D5:W5="G")*7 + (D5:W5="A")*4 + (D5:W5="R")*1 )