1

I have a football predictions table and one of the columns marks the score in the most usual manner: 3:2, 1:0 etc. I'd like to take a range (for instance A2:A20, where A is the column where scores are situated in these positions) and take a sum of it, but before taking the sum, applying the function for it.

As my explanation is a bit complicated, i'm going to illustrate with an example:

Values:

A1 - 0:0
A2 - 1:0
A3 - 2:2
A4 - 5:3
A5 - 2:1 
A6 - 4:2

I figured out a formula for a single cell, which is: =SUM(SPLIT(A1,":")). This will give the sum out of a single cell, for instance for A1, it'd be 0, for A2 that'd be 1, A4 would give out 8 etc. Now what I want to achieve is to sum these sums and get the value of total goals scored. By that example, the value would be 22.

I could create a separate column which would contain the values where the sumsplit function has been already applied and take goals scored as a sum from that column, but I'm wondering whether it could be solved as a single equation, without creating any additional cells?

Banana
  • 814
  • 1
  • 8
  • 28
  • Afaik, this `Split` function is a VBA function, not to be used in a formula. See [here](https://stackoverflow.com/questions/1031305/simulate-string-split-function-in-excel-formula). BTW By entering a football-score in a single cell like this, you are making it yourself very difficult. When I entered 2:2 excel changed it to the time 2:02 on my PC. So I had to set the layout explicitly to Text first. Why not use 2 cells for both numbers? – ffonz Jun 20 '18 at 08:12
  • And finally: If you can create a formula to get the result for 1 cell, have a look at [array formulas](https://support.office.com/en-us/article/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7). With that you can get a result in a single cell of a whole range of cells. – ffonz Jun 20 '18 at 08:13
  • I actually resolved the issue a bit differently - I've exported my excel to google docs, which allows to use javascript to write macros (as i'm not proficient in VBA). Then i wrote a simple javascript function to iterate over the range and calculate the sum based on that. I'll keep the question open in case anybody offers anything (where last resort still may be to actually use VBA macros). Your statement is valid, i could use multiple columns, better values in cells, but that was not the point of my question – Banana Jun 20 '18 at 08:24

0 Answers0