# EXCEL Running Sum Based on Column Values

I have 5 columns with possible values A, B, C, each will either add a point or subtract a point from a running sum based on value of the cell.  The 6th column will be the sum.

I need to come up with a yes or no signal in the 6th column  by adding a positive and negative points

Col1 = A
Col2 = B
Col3 = C
Col4 = A
Col5 = C

So if value = A, add a point
If value = B, subtract a point
If value = C, add a point

So if the sum is negative, the 6th cell will be red and if the sum is positive, the 6th cell will be green.
If color with this rule is too, difficult, replace with the letter N or Y.

Has anyone does this before?
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ConsultingCommented:
This formula maybe:
``````=IF(OR(A1="A"; A1="C"); 1; -1) + IF(OR(B1="A"; B1="C"); 1; -1) + IF(OR(C1="A"; C1="C"); 1; -1) + IF(OR(D1="A"; D1="C"); 1; -1)
``````
As for color, a simple conditional formatting will do the trick.
Commented:
Put this formula in F5:

=IF(COUNTIF(A5:E5,"a")-COUNTIF(A5:E5,"b")+COUNTIF(A5:E5,"c")>0,"Y","N")

To change the cell color you can use conditional formatting to change fill and font color to the same color, green or red so the cell will look like it doesn't have a value in it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Software Team Lead, ex-Business Systems Analyst , ex-Senior Application EngineerCommented:
So if value = A, add a point
If value = B, subtract a point
If value = C, add a point
is that possible to have values other than A, B and C?
Commented:
Yes.  Substitute in the whatever values you want for A, B, and C.

An alternate way to write the formula so you can change the values easily is to reference a cell that has the value you want to match rather than putting that value in the formula.  Here's the same formula, still in cell F5, rewritten this way, with A1=A, A2=B, and A3=C.

=IF(COUNTIF(A5:E5,\$A\$1)-COUNTIF(A5:E5,\$A\$2)+COUNTIF(A5:E5,\$A\$3)>0,"Y","N")

Notice the \$ signs in front of each part of the reference.  These will keep the reference as "absolute" when you copy the formula to other cells.  If you copy this formula to F6 notice what happens:

=IF(COUNTIF(A6:E6,\$A\$1)-COUNTIF(A6:E6,\$A\$2)+COUNTIF(A6:E6,\$A\$3)>0,"Y","N")

The references for the rows that you're evaluating will change (from row 5 to row 6), but the value you're evaluating against, A1, A2, and A3, didn't change.  This will make maintaining the spreadsheet easier if it grows over time.
Author Commented:
I went to try the first solution in it's simplest form

=IF(OR(A1="A"; A1="C"); 1; -1)

but excel gives an error with the formula

what could be missing here?
Commented:
Try changing the ";" to ","
Software Team Lead, ex-Business Systems Analyst , ex-Senior Application EngineerCommented:
upload your excel file here if necesary
Author Commented:
I couldn't get the first solution to work with the second condition.

I'm investigated nested IF statements.

Here's a good website that explains them.

https://tinyurl.com/y8cp7955
Author Commented:
This is the closest solution that I was able to get working.
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.