Adding numbers ignoring text & negatives

[CLDr]

I wish to add a column of numbers with 2 complications: 1. some cells contain negative numbers which I want to treat as positive numbers and 2. some cells may also contain text which I want to ignore, but still add the number in the cell (in which case text is always the same word - online). I have a formula to treat the negative numbers as positive ( =SUM(ABS(T3:T19)) ), but can't work out how to also include the numerical data in cells with text. In row '11' below I have shown the results I would like to generate.

Any help appreciated! Thx

[JohnTopley]

TRY

=SUM(IF(NOT(ISNUMBER(A1:A9)),ABS(--SUBSTITUTE(A1:A9,"online","")),ABS(A1:A9)))

Enter with Ctrl+Shift+Enter

[CLDr]

Thanks John. That gives me #VALUE! - have I copied it wrong maybe?!

[HansDouwe]

Or only this?

Please Login or Register  to view this content.

[CLDr]

Thanks - it wont let me input that formula - I get the error message - 'There's a problem with this formula...' box..?

[HansDouwe]

And if you try to copy the formula from the sheet?

[Pepe Le Mokko]

Try =

Please Login or Register  to view this content.

[JohnTopley]

Se attached with answers from Hans and Pepe

[CLDr]

For some reason I cant open the sheet you've attached - even if I download it first...

[CLDr]

Oooh! That works perfectly in my example but for some reason when I copy it over it doesnt! Could it be that there are some blank cells in the 'real' sheet?

[CLDr]

Se attached with answers from Hans and Pepe

Thanks I can see that and both work... One added complication is that some cells in the 'actual' sheet are blank - and when I copy it over (or create blank cells in the example) it doesnt like it. Im guessing thats easier to work around?

[HansDouwe]

That works perfectly in my example but for some reason when I copy it over it doesnt! Could it be that there are some blank cells in the 'real' sheet?

If you add a sheet then I can tell.
Without a sheet, the most likely cause of a #VALUE error in ABS or SUM is that there is a cell that does not contain a numeric value and is not "online", as indicated in post #1.
Or a cell with a space is involved. SUM can handle that, but ABS can't.

Furthermore, the request to consider add reputation request to any helpers' answers that you think deserve.
This can be done by clicking * Add reputation at the bottom left of the answer.

[JohnTopley]

@Hans: the #Value is caused by a blank cell.

[CLDr]

Thanks again for all the help with this!! One final question - is there a way of adapting either of the formulas to ignore empty cells? Ive tried a couple of things with my limited excel knowledge - wondering if that a formula too many?! Thanks

[HansDouwe]

The #VALUE in my formula is NOT caused by a blank cell.

The SUM functions ignores blank cells (only if no cells left, SUM replaces 1 blank cell by zero) before executing SUM.
The ABS function replaces a blank cell by zero before executing ABS.
Because of how these functions work in this way, a blank cell cannot cause #VALUE in SUM or ABS.

I indicated in post #12, what could be the cause of a #VALUE.
If you upload your sheet I can check if that is the case.

[CLDr]

Thanks Hans- this is the relevant part of the actual sheet. Im playing around with V-Y 24.

[JeteMc]

Try the following array entered formula** in cell V24:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.

[Phuocam]

Or try

=SUM(IF(V3:V19="","",ABS(SUBSTITUTE(V3:V19,"online",""))))

Ctrl+Shift+Enter.

[CLDr]

Try the following array entered formula** in cell V24:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.

Amazing!! Thanks so much! This will make such a difference to what I'm trying to do & I could never have got here myself!!

[CLDr]

Or try

=SUM(IF(V3:V19="","",ABS(SUBSTITUTE(V3:V19,"online",""))))

Ctrl+Shift+Enter.

Thanks v much - this forum has been so helpful!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.