nested ifs

[vckidd2001]

I have a score for whether someone attends a meeting or not connected to a drop-down list eg. present in the list equals 3 and absent equals 0, etc. That drop down list is then used across 5 meetings across a year. I want to give each person an attendance score so that these scores can be compared across attendees.

This is the code I am using but I am sure there must be an easier way to do this. Any ideas?

=IF(C2 = "Present",Key!$B$2,IF(C2 = "Ill",Key!$B$4,IF(C2 = "Half-Time",Key!$B$3,IF(C2 = "N/A",Key!$B$6,0))))

Cheers

 

[VBA Geek]

A1 =INDEX('Key'!$B$2:$B$6,MATCH(C2,{"Present","Half-Time","Ill","ZZZZZZZZZZZZZZZ","N/A"},0))

 

[vckidd2001]

If I then want to sum the row of results could I then embed that in a sum somehow?

 

[VBA Geek]

hi, i dont get the question

please show a visual example? u can use excel jeanie or mr excel html maker

http://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.html

 

[vckidd2001]

So I have all the totals from the words in separate columns and then a sum of those columns. What I want is to be able to do all that in one go in the total column at the beginning without the need for multiple columns. Hope this makes sense

 

[VBA Geek]

<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J2</th><td style="text-align:left">{=SUM(<font color="Blue">TRANSPOSE(<font color="Red">INDIRECT(<font color="Green">IFERROR(<font color="Purple">LOOKUP(<font color="Teal">$B2:$I2,{"Half-Time","Key!B3";"Ill","Key!B4";"N/A","Key!B6";"Present","Key!B2"}</font>),"key!ZZ500000"</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.</td></tr></table><br />

Excel 2013
	A	B	C	D	E	F	G	H	I	J
1	Total	02 2013	04 2013	07 2013	10 2013	01 2014	03 2014	07 2014	10 2014
2		Present	Present	Present	Present			Present		15
3		Present	Present	Present				Present		12
4		Present	Present	Present				Ill		10
5		N/A	Present	Present				Present		12
6		Present	Present	Present				Present		12
7		Present	Present	Present				Present		12
8		N/A	Present	Present				Absent		9
9		Present	Present	Present				Present		12
Sheet1