SUM Characters against a lookup table

johnbird1988

Board Regular
Joined
Oct 6, 2009
Messages
198
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Canapone

Active Member
Joined
May 10, 2007
Messages
463
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
Joined
Oct 6, 2009
Messages
198
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
Joined
May 10, 2007
Messages
463
Hi,

a poor patch. Leave A1 and B1 empty


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

Regards
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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

Canapone

Active Member
Joined
May 10, 2007
Messages
463
Thanks Aladyn!

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

Watch MrExcel Video

Forum statistics

Threads
1,122,414
Messages
5,596,001
Members
414,037
Latest member
Roamingsmile

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top