If statement or VBA coding ? 3

[Lil8]

I have a sert of data in columns that I need to go through each row and calculate the no Of points for each 4 week block that reads each column and adds up the points A = -1, E = 1 and / = 0. So for example for the first row the formula will calculate a total of -1 As shown.

Points
A A E / -1 A A E /
E E A A 0 E E A A

Do I need to write some VBA coding or perhaps use a formula ?
thanks Nic

 

[RivetHed]

maybe try something like this

Dim rgrange As Range
Dim intColumn As Integer
Dim introw As Integer
Dim intTotal As Integer

Set rgrange = ActiveCell.CurrentRegion
Set rgrange = rgrange.Resize(rgrange.Rows.Count, rgrange.Columns.Count + 1)

For introw = 1 To rgrange.Rows.Count
intTotal = 0
For intColumn = 1 To rgrange.Columns.Count
If intColumn = rgrange.Columns.Count Then
rgrange.Cells(introw, intColumn) = intTotal
Exit For
End If

If rgrange.Cells(introw, intColumn) Like "A" Then
intTotal = intTotal - 1
ElseIf rgrange.Cells(introw, intColumn) Like "E" Then
intTotal = intTotal + 1
End If
Next intColumn
Next introw

End Sub

it should cycle through everything and put a total in the last column, just make sure a cell within the table is selected when you run it

hth

rivethed

 

[AlaskanDad]

Nic,

I've always been a sucker for a complicated but efficient formula.

Assuming your four values are in columns A-D, try this in column E:
=SUM((A1="A")*-1,(A1="E")*1,(A1="/")*0)+SUM((B1="A")*-1,(B1="E")*1,(B1="/")*0)+SUM((C1="A")*-1,(C1="E")*1,(C1="/")*0)+SUM((D1="A")*-1,(D1="E")*1,(D1="/")*0)

Looks ugly but gets the job done!

- Rob

 

[KenWright]

Probably just being dense but what has the data to the right got to do with the data to the left? is is 2 sets of data or what.

Anyway, I much prefer formulas myself, but I think I can shorten that one a tad

Assuming your data was in A11 then to do what you have asked for that piece of data you could just in E1 use

=COUNTIF(A11,"E")-COUNTIF(A11,"A")

If you had to repeat that for all the rowsw then just copy it down.

To do that for the whole range as Rob's formula has done, just change the ranges:-

=COUNTIF(A13,"E")-COUNTIF(A13,"A")

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[KenWright]

Oops forget the last part of my note, as in fact it Robs formula had only looked at one row. :-(

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[AlaskanDad]

Ken,

Yours is considerably more elegant.

Star for tight code!

Rob