SUM Characters against a lookup table

[johnbird1988]

Hello,

I have a table with a list of characters in a column like A, B, C, D etc. What I would like to is Sum this column with the value these characters represent. For example A = 1, B = 0.25, C = 0.75 and D = 0.5. Is there a better was to do this other than multiple SUMIF.

Thank you

John

 

[Canapone]

Hi, if have a table in A2:B5

A2="A" B2=1, A3="B", B3= 0.25 ....

with no empty cells in D1:D100


=SUMPRODUCT(LOOKUP(D1:D100,A2:A5,B2:B5))

should do the trick.

 

[johnbird1988]

Hello Canapone,

Thank you for your reply. The formula does work but it is reliant on all the values in D1:D100 being populate and some of the field are not. Can this be changed to reflect that somehow?

Cheers

John

 

[Canapone]

Hi,

a poor patch. Leave A1 and B1 empty


=SUMPRODUCT(LOOKUP(D1:D100&"",A1:A5&"",B1:B5))

Regards

 

[Aladin Akyurek]

Create a 2-column table, consisting of:

A,1
B,0.25
C,0.75
D,0.5

Select this range and name the selection as Table.

Let A2:A100 house the relevant data.

Run now the following formula:

=SUMPRODUCT(INDEX(Table,0,1),A2:A100,INDEX(Table,0,2))

 

[johnbird1988]

That's great thank you, all working now. Cheers John

 

[Canapone]

Thanks Aladyn!

Great formula : I'll put it immediately in my personal toolbox.

 

[Aladin Akyurek]

That's great thank you, all working now. Cheers John

Thanks Aladyn!

Great formula : I'll put it immediately in my personal toolbox.

You are both welcome. Thanks for providing feedback.