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?
lcorAsked:
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.

Fabrice LambertFabrice LambertCommented:
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)

Open in new window

As for color, a simple conditional formatting will do the trick.
0
Joe DCommented:
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.
0

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.

Start your 7-day free trial
Ryan ChongCommented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Joe DCommented:
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.
0
lcorAuthor 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?
0
Joe DCommented:
Try changing the ";" to ","
0
Ryan ChongCommented:
upload your excel file here if necesary
0
lcorAuthor 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
0
lcorAuthor Commented:
This is the closest solution that I was able to get working.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.