Simple Excel formula Help.....

[vijaychennai]

Hello all,

Good day !!!

I have excel file in the below format.

Excel Workbook
	A	B
1	Grade	Error
2	A	1
3	A	2
4	B	1
Sheet1

Based on grade i need to get points.

Answer :

Excel Workbook
	A	B	C
1	Grade	Error	Points
2	A	1	20
3	A	2	15
4	B	1	20
Sheet1

Condition:

Grade A - Error 0 = Points 30

Grade A - Error 1 = Points 20
Grade A - Error 2 = Points 15
Grade A - Error 3 = Points 15
Grade A - Error > 3 = Points 0

Grade b - Error 0 = Points 30
Grade B - Error 1 = Points 20
Grade B - Error 2 = Points 15
Grade B - Error > 2= Points 0

 

[Firefly2012]

Hi

Here's one way using SUMIFS - you create a lookup table with your possible grade/error combinations and resulting points. The 0 points don't need an entry in the table.

Excel 2007
	A	B	C	D	E	F	G	H
1	Values					Lookup Table
2	Grade	Error	Points			Grade	Error	Points
3	A	1	20			A	0	30
4	A	2	15			A	1	20
5	B	1	20			A	2	15
6	B	6	0			A	3	15
7						B	0	30
8						B	1	20
9						B	2	15
Sheet2

Cell Formulas
Range	Formula
C3	=SUMIFS($H$3:$H$9,$F$3:$F$9,A3,$G$3:$G$9,B3)