Getting a Column with REF!'s to Sum?

[mrvp]

Hi,

I have been re-working a sheet from a book I've read (hence the file that I have attached is 99% values that I have pasted from part of the re-worked sheet and only an example of the formula I am having issues with) - in short, it had an example of a Markov Chain model for modelling an inning of a baseball game. The model was run with the @Risk add-in, using the RiskDiscrete function, which I re-worked into a nested if using the random number generating function (random numbers in column A).

Basically the only thing, I haven't been able to re-work is how to sum up the runs generated in the H column? The model generates ref!'s when you get consecutive "yes"'s in column K (to denote the end of an inning). The end in J1, you can do with a Match, I just couldn't figure how to do something similar in J2 for column H (as the REF!'s generate a REF!)? Basically it should add up to 6 as per M2 (and the formula would be suitable etc). Sometimes the inning will run to over 100 lines (to give you an idea of how much it needs to vary).

Any advice would be appreciated (or even nudges in the right direction).

Thanks in advance,

Runs_Problem.xlsx

[JieJenn]

Because sum function doesn't take errors.

Enter this formula with Ctrl + Shirt + Enter keys together

=SUM(IFERROR(H4:H203,0))

[Pete_UK]

Put this array* formula in J2:

Please Login or Register  to view this content.

*An array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <Enter>. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar, but you should not type these yourself. If you need to amend the formula you will need to use CSE again to confirm the amendment.

Hope this helps.

Pete

[Haseeb Avarakkan]

Another option is SUMIF with just ENTER

=SUMIF(H11:H1000,">0")

to avoid volatile, use

=SUMIF(H11:H1000,">0",H11:H1000)

[mrvp]

Thanks all. Everything would be spot on that everyone has suggested - I posted this without being as thorough as normal though, and actually had a quick look around whilst on my lunchbreak today and I ended up going with this:

=SUMIF(H57:H256,"<>#REF!")

The H numbers match the numbers off of my actual sheet. The formula came off a similar question someone had asked a couple of years ago.

Thanks to everyone for answering though.