SUM Formula, but exluding a hard coded n/a field

[manny1975]

Hi guys, trying to do a sum of numbers but excluding the hard coded n/a field. I did this, but it did not work:

=IFSUM(Ai2,AG2,AF10,AB10,Z10,"n/a"=0)

obviously thats wrong, but can someone tell me how to create this formula?

Thanks!!!

 

[Eric W]

It should just be:

=SUM(AI2,AG2,AF10,AB10,Z10)

since SUM ignores non-numeric values.

 

[manny1975]

Thanks Eric! Here's where it get a bit more complicated now. So I have a point value system of 100 total points. That's what each employee can get for certain projects, but not every employee participates in these projects. So for example, project one is worth 5 points, project two worth 8 points, but if a project wasn't completed by the employee for example, project 3 was worth 10 points, but the employee didn't do it so they got a "0". How would I add the sum of all projects, but excluding the NA so the employee doesn't get dinged for a project that didn't get completed because it didnt apply but do ding them for the one that they didnt do and got a "0" for?

 

[manny1975]

Sorry, I had posted this question on the forum again, but I got the sum part ignores text values. My other dilema is the part where if the point system totals 100, but not all of the subjects will add to 100 since it does not apply. What type of formula can I use with the scenario above?

 

[Eric W]

It depends on how you want to "ding" the employee. Here's one idea:

Book1
	A	B	C	D	E
1	Project	Value		Employee1	Employee2
2	A	5		5	NA
3	B	8		6	0
4	C	10		NA	NA
5	D	7		0	7
6	E	6		4	3
7
8			Sum	15	10
9			Sum/total	15/26	10/21
Sheet21

Cell Formulas
Range	Formula
D8:E8	D8	=SUM(D2:D6)
D9:E9	D9	=D8&"/"&SUMIFS($B$2:$B$6,D$2:D$6,"<>NA")

Employee1 has a total of 15 points. Then the SUMIFS formula in D9 figures out what the maximum number of points is for that employee by not counting the values of cancelled projects. I just displayed the points/maximum available, but you can easily do the actual division to get a percentage. I'm not sure how this will work on your sheet, since your sample showed the point values in cells all over the place, but if you show a sample of your sheet, we might be able to adapt this concept to it.