Sum Column of Win-Loss-Tie records

I am attempting to sum a single column (17 rows) of win-loss-tie records in WW-LL-TT format. A cell in the summation column could have up to two digits in either of the 3 areas before or after the dash (9-10-1, 10-2-10, or 1-2-9). Also, a cell in the summation column could only have numbers in the WW & LL areas (no ties: 9-7, or 10-3). I would like the summation cell to look like the same format (WW-LL-TT). I hope this makes sense.

Use this :

Please Login or Register  to view this content.

Replace A1:A4 with your range.

Originally Posted by Tony Valko

Adjusted the range as needed.

Using this data:

Data Range

	A
1	9-10
2	12-2-0
3	5-1-3
4	7-100-2500
5	0-0-2

That formula returns 33-23-5.

This will not work for that. nlarson has told us the format as WW-LL-TT. That's why this formula will work with that format. If you need the formula for any format then it would be larger. And if we can achieve anything with a smaller one, then why do we look for larger?

wow...it works like a champ...thank you very much. you guys are amazing. One thing about the formula's result...it will return a #VALUE! result if there is a blank cell within the range. Not a big deal, but is this fixable to ignore the blank cell and return the summation of any cells w/in a range with data?

For some strange reason I couldn't mix up A1:A6<>"" with the sumproduct formula. May be an expert can solve that issue.

But I have solved that problem with an array formula :

Please Login or Register  to view this content.

Confirm Ctrl+Shift+Enter.

Sanram:

You are da bomb. works awesome. Thank you so much. I tried and tried to do on my own for weeks...took this forum and it's genius' a couple hours. Thanks again!!!

Nate

You are welcome.