Using rSum To Add Multiple Cell Groups

I know I've been on here a lot as of late, but I cannot find the answer to this issue anywhere. I don't think it's a difficult one, but I can't seem to figure it out nonetheless:

I'm trying to create a formula that adds Cells A1 through A10 as well as Cells C1 through C10 and displays the sum into Cell E10. So, E10 should contain the formula =rSum(A1:A10,C1:C10), right? Wrong! If it was just a plain, simple old =Sum, it would work, but no, I need a difficult =rSum for some bizarre reason (actually because of this reason), and I just don't know how to work with the whole r-thing.

Anyone here familiar with =rSum?

Hi,

the 'rSum' you refer to is simply what's known as a user defined function. i.e. someone has decided Excel hasn't got a standard function that performs the task in hand and has written a new one.

However it's not clear why the standard =SUM(A1:A10,C1:C10) doesn't give you what you say you want, which is apparently just the sums of err.. A1:A10 & C1:C10.

Please explain.

Regards

Thank you Richard Buttrey, if you click on the link I had in my first post and look at the attachment in Post #14 there, you'll see that the code I'm using is using =rSum. I never questioned why it was an rSum, but I'm assuming it's because of the reason you described. When I try to change it to just a normal =Sum, the code doesn't work.

Hi,

Don't use the rSum function. Just use the Bog standard =SUM(A1:A10,C1:C10).

Regards

I've tried that, but when I do so, the code doesn't work at all.

Does anyone have a suggestion for me? There's got to be a way this will work... thanks in advance.

You need:
=rSum((A1:A10,C1:C10))

Hi,

For some reason earlier posts seem to have gone missing and your original file is no longer present. However I still don't understand why you say
=SUM(A1:A10,C1:C10) doesn't sum those cells. It's standard excel functionality and I can't believe that it doesn't work.

Can you upload your workbook again please.

OnErrorGoTo0 - brilliant! That does it! Thank you a TON! Richard Buttrey, thank you as well for your help - somehow I think you may have missed my link, this one, which explains why I was not able to use =SUM and had to use the =rSum in order to make the code work. Thanks to both of you for your assistance!

I've been running into issues with this. For some reason, I'm unable to copy and paste data within this sheet now, and I am also not able to use the =rSum((A1:A10,C1:C10)) formula once the range is interrupted by a non-numerical cell, it seems...

I've attached a sample of the worksheet. The four cells that need this formula are coloured on the top left with their respective cells the same colour on the right. The cells on the right will be continued towards the right in that pattern and will continue indefinitely.

Also, how do I select an infinite range of cells? You'll see that the codes I'm attempting to use are in the format of =rSUM((I10:AAF10,I18:AAF18)) - is there anything else I can use besides some crazy cell number like AAF10 and AAF18? I'd like the whole row from I10 and I18 on to be part of the formula.

To clarify, the code and formulas I'm currently using I've received from this thread here which allows numbers that receive the strikethrough feature to be subtracted from the subtotal; and they worked great until I realized that there won't be an uninterrupted flow of numbers in the ranges used.

Any suggestions?

You'll probably not thank me for adopting the response given by Paddy the Irishman to the English tourist seeking directions to Limerick.

"Certainly, sor. If you take the first road to the left, second right under the bridge then, err no, no that won't do. Drive on for about four miles then turn left at the crossroads. Oh Bejesus no, no that won't do either Murphy's got a diversion on round there."
Paddy scratched his head thoughtfully.

"You know, sor, if I was going to Limerick I wouldn't start from here at all at all."

....However I wouldn't start from here either

It seems to me that it would be much simpler if you changed your layout. There is a lot of repeated stuff anyway and using UDFs is often fraught with nuisances that can be avoided with regular functions.

Is there any reason why you can't build a simple 2D database with columns for
Date, Job#, Desc, P/N, Qty, Hrs, StrikeThrough Hrs Yes/No, PRGM, DEL ?

Then not only does summarising with SUMIF() or SUMIFS() become straightforward, but as a bonus you'll also open up the wonderful world of Pivot Tables enabling you to slice and dice your data in ways you probably wouldn't even think of.

Regards

Lol, thank you for that great little story. Unfortunately, there is nothing I can do about being here.. and I need to get to Limerick.. but let me explain.

As you can see, the columns I'm using right now are for each day of the week. Within each day is the schedule for each row - each row being used for a different user. This layout is needed as we need to be able to schedule far ahead, and look far back.

Are you saying that nomatter how I look at it, I'm simply in the wrong place to reach Limerick?

Alright! I've discovered the way to Limerick by following Richard Buttrey's amazing advice.

I was able to update the workbook by making sure the cell ranges were uninterrupted, so now there are no formula issues, just copy and paste ones, plus I'd still love to know how to choose an infinite cell range. So, I'm half-way to Limerick then I guess.

Are those two issues solvable now?

Further to Richard's comment, I'll just refer you back to what I said here.

Well, have a look at this attachment - this is pretty much the most I can change it to - all I need now is to have copy and paste work, really. Do you have any idea why this is an issue?

Hmm, I don't think the apostrophe thing will work in my case - what are API calls or a DataObject? Is there no other way of writing the code to do what I'm trying to do?

OnErrorGoto0, you always find a way, don't you? Thanks a million! I went with your first code, as as far as I can tell, it works very well!

Do you know if there is a way to specify an indefinite range of cells or not?

Specifying an indefinite range is a contradiction in terms, is it not?

How would the formula determine what to look at at any given time, and why can't you just use the whole row?

Haha, good point.

Well, I'd like to use the whole row, except for the first few cells, as you can see in the attachment - for example Row I:10 - I:*infinity*.

So for the formulas I'm using, I would change this:

=rSUM((I10:AZN10,I20:AZN20))

to this?

=rSUM((I10:INDEX(10:10,MATCH(1E+100,10:10,1)),I20:INDEX(20:20,MATCH(1E+100,20:20,1))))

Just out of curiosity - is there a way to make that shorter? I mean, it works! And I'm happy! Just wondering!

You could stick with what you had before? Or do all the work in the UDF since it's a volatile monster anyway...

Okay, umm, do you have any final advice on how can I make this work for three ranges? I've tried

=rSUM((I11:INDEX(11:11,MATCH(1E+100,11:11,1)),I21:INDEX(21:21,MATCH(1E+100,21:21,1)),I31:INDEX(31:31,MATCH(1E+100,31:31,1))))

..but that doesn't seem to work.

In what way?

I get a #VALUE! in the cell I try to use it in.

Do you have numbers in all three rows?

Yes I do - have a look at my latest edition attached below. I've highlighted the two cells using this formula.

They work for me after correcting the incorrect row number in the second cell (referring to row 232 rather than 32)

Ah! That was goofy of me. Sure enough! But hey! I just realized I won't always have numbers in all three cells! And when I don't, I still get the funny #VALUE! error.. is there any way around this?

I would go back to your original formula. The error handling is going to make the formulas even longer...

Oh alright. Thank you for all your help regardless. Another quick question while I'm at it here - did you notice how slowly the worksheet runs? It takes like half a second sometimes to select another cell that's been clicked. Any wisdom regarding this?

Yes - the same as I said in the first place (i.e. don't do this). You are forcing your functions to recalculate every time you select anything on the sheet.

Mmm. I know you are right, but I really don't see how else I can make this work. If I have a cell with an 'X' after the number cells, I don't see how the subtotal formulas will work. Ahh, it's confusing. Is there absolutely no other workaround, like code-wise, that will allow the strikethrough to be subtracted from the subtotal? It worked, until I fixed the copy and paste thing. I'm also getting this weird 'Do you want to save changes you made?' prompt every time I close it, when I haven't even made any changes to it. I'm attaching the latest version once more... you're probably sick of it by now, as I am, but I desperately need something to work.

OnErrorGoto0, you did it again... That's brilliant. I think that will work splendidly! Are we able to have the double-click thing work on the HRS cells only? I can see issues coming up where users try to double-click a cell in order to edit it's contents (H6 for example) and they'll just get strikethroughs. I tried converting all the rest of the cells to text only, but if they get numbers, they still get the strikethrough thing.

It can be done - depends how specific you need it to be. Can we just take it that any rows where column G is "HRS" should respond to the doubleclick, or do we need to be more precise and only look at certain columns too?

No, I think you said it - any row to the right of an HRS cell on column G should respond to the double-click. The rest shouldn't. Thanks so much again, man..

I really, really appreciate that sir. Thank you so much for helping me out with all this.

PS I know you've done more than your share already, but in case you're bored (haha) - I do have one more thing I'm trying to accomplish with this silly worksheet (though it's slightly less silly now thanks to you) right here... if you feel up to it, I would truly appreciate any input from you, as you already know what's going on with it, but seriously, only if you want to. I am more than happy with all you've done already. Thanks again!

I will take a look when I have a second. Have to do some actual work today!

Thank you so much again, OnErrorGoto0!