# SUM Characters against a lookup table

#### johnbird1988

##### Board Regular
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

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Canapone

##### Active Member
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.

Last edited:

#### johnbird1988

##### Board Regular
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

##### Active Member
Hi,

a poor patch. Leave A1 and B1 empty

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

Regards

Last edited:

##### MrExcel MVP
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

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

#### Canapone

##### Active Member

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

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

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

You are both welcome. Thanks for providing feedback.

Replies
7
Views
159
Replies
5
Views
211
Replies
1
Views
182
Replies
5
Views
313
Replies
7
Views
251

1,195,598
Messages
6,010,644
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back