Values for Y and N in multiple cells

[Corey]

I
am adding up C2:C7, but I want to stay within the same row
when converting the "Y" or "N" to the value I set for it.
In other words this is how I want it to work...

C2 Y (value I set in formula = +1)
C3 3
C4 N (value I set in formula = -2)
C5 10
C6 Y (value I set in formula = +3)
C7 2
C8 17 (Total)

Any Ideas?

Corey

 

[Guest]

Can you give a longer example?
IF C2 = Y, C3 = 3, C4 = N, C5 = 10, C6 = Y, C7 = 2 and
D1 = should return 1, D2 should return 3, D3 should return -2, D4 should
return -2, D5 should reutnr 10, D6 should return 3, and D7 should return 2 and
D2 and D6 return + since they Y and D4 returns - since it is a N and
D2 = 1 since it is the first letter and D4 returns 2 since it is the second
letter and D6 returns 3 since it is the third letter, then you could use the
formula
=IF(ISNUMBER(C2),C2,IF(C2="N",-(COUNTIF(C$2:C2,"Y")+COUNTIF(C$2:C2,"N")),COUNTIF(C$2:C2,"Y")+COUNTIF(C$2:C2,"N")))
in D2 and drag it down to D6.