SUMS of (start:end) arguments not working

[cheeko]

Hi guys,

I have a ('start:end') function that's not working. A simplified example is shown below in the screen shots.

I want a given cell in sheet1 to produce the sum of factors within a given column/row arrangements and equations from sheet3 and sheet4.

I've tried all the arguments I can think of in their proper places including the ' arguments before and after start:end but nothing seems to work. My resulting answer is usually #REF!

Can suggestions and help?

sheet1.png
sheet3.png
sheet4.png
worksheets.png

Thanks

[mikeTRON]

First off post a sample workbook and not photos.
Second, are you trying to multiply B5 from sheet 1 or the other sheets?
also the +(start:end!D3*b5) is invalid

I think you might mean

Please Login or Register  to view this content.

[cheeko]

This is a partial solution. there's a few factors and arguments I need altered to make it work.

I'd like to post a sample worksheet but how do you do that? I don't seem to have the file attachment icon.

[alvin-chung]

Hi cheeko, try click on the "Go Advanced" button and you shall see an icon to upload attachment.



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[cheeko]

Thanks. Ok here's the sample worksheet.
1. The formula in sheet1 is this:

Please Login or Register  to view this content.

However what I need is essentially this:

Please Login or Register  to view this content.

which should yield 8. (5*1)+(4*0)+(2*0)+(3*1)=8, but incorporating the (start:end) function into it

I've tried several different combinations but I must be inputting the arguments wrong somehow.

2. But i also need this formula to satisfy any additions to worksheets.

e.g.

Please Login or Register  to view this content.

so I can simply copy and paste within the (start:end) worksheets when I need to add more.

Any help would be appreciated. Thanks

[alvin-chung]

Could you share how does your sheets name looks like? Is't Sheet1, Sheet2, Sheet3, Sheet4...?



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[cheeko]

The attachment should show this.

Sheet1, start, Sheet2, Sheet3, end

[alvin-chung]

Here's a quick solution, perhaps it could be shorter/simpler

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[cheeko]

Hi,

Although this formula works directly to the sample I provided, it doesn't on my actual file. There is no logical difference between the two files except for how the results are obtained. The sample are entered numbers where as my file are equations. However both follow the exact same sequence.

However in your example you've listed combination of rows ROW(2:3). In mine it's just products of singular cells, not combinations of rows. B2*B4, G2*G4, etc

When I change the ROW(2:3) for the single cell I get an #REF! error.

Perhaps I'm not understanding the formula/arguments but I don't see any multiplication of cells B2 and B4, G2 and G4.
Likewise I don't know what the "<9.99999999999999E+307" is supposed to represent. Selected rows until infinity...??

I need the pattern of this formula, in logical terms, translated into an excel formula (proper arguments and characters, etc)

Please Login or Register  to view this content.

Any other suggestions?

Here's a quick solution, perhaps it could be shorter/simpler

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[alvin-chung]

Hi cheeko, it will be helpful if you can explain how it doesn't work in your actual file in more details, e.g. returning wrong value? returning error? etc..

ROW(2:3) is just a way to make an array of {2;3} so that it can works with INDIRECT to get all the sheet name, i.e. Sheet2 and Sheet3 in your case.
SUMPRODUCT is doing the multiplication job.
9.99999999999999E+307 is the max number that shall never be reached

You can simply extend the formula to match your logic to include F2*F4 and H2*H4 on both sheets, try this:

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[MrShorty]

Is there some reason that the multiplication and summation have to occur in the same formula? The obvious solution to me (one who is very fond of using helper cells) is to perform the multiplication step in each individual sheet. So, sheet1:B5 might be =B2*B4. Then, the sheet performing the summation can perform the =sum(start:end!b5).

I guess there's my suggestion -- decide if this all has to be done in one step. If it does, someone else will probably have to provide a solution. If you can bring yourself to separate the multiplication and summation steps, then it seems like it should be as easy as I've suggested here.

On edit: Another additional thought. If you make sure your helper cells are in a contiguous range on each sheet (or not interfered with by other cells), then your 3D reference can be =sum(start:end!B11:G11) or similar.

[Tony Valko]

If there are only a couple of sheets you can just use the array constant instead of the ROW function:

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&{2,3}...

[Jonmo1]

Here's how I would do it.

EFCheeko.xlsx

[cheeko]

Hi alvin-chung,
Thanks I thought the 9.99999999999999E+307 must have been something to do along the lines of infinity of some sorts.
I see the array now only because I don't use arrays that often.

My error is #REF! It says 'Invalid cell reference error' formula error.png
I would have to guess this is directly related to the array in the equation. I cannot have an array as such for multiple rows.
The product needs to come from, for instance, sheet2 of B2*B4 only + sheet3 of B2*B4 only + sheet2 of D2*D4 only + sheet3 of D2*D4 only, etc etc. Using, for instance, B1 or B3 in any sheet or D2 D4 in any sheet will cause a reference error because these cells can be anything from text, to other forms of numbers (%, $), equations, or even blank cells themselves.

I don't mean to beat a dead horse but that's why I need the formula to follow this pattern

Please Login or Register  to view this content.

I just can't get the characters and arguments correct.

[cheeko]

I suppose I could use helper cells like MrShorty suggested by adding another sheet or something. I'm just limited to cell and sheet inputs, additions and removals because most are locked and hidden, except for these ones I need to find equations for.

The main idea is that as long as I can continually copy and paste these columns with the working sheets (for x-amount of times) without having to add these into the formula manually.

I need the formula to update automatically when I copy and paste with a sheet and/or copy and paste a sheet within start:end, otherwise I will pull my hear out if I have to do it manually

[cheeko]

To Jonmo1,

I looked at your example. Simple and clever just one thing regarding cell F6 (B6+D6) in either sheet 2 or 3.....

Suppose you copy and paste columns A:B or C:D in either sheet 2 or 3. Now, in these sheets you have the same format in columns E:F, G:H, I:J, etc....

How do you get the formula in cell F6 to adjust automatically so it reads (B6+D6+F6+H6+J6, etc) as I copy and paste?

Essentially this is the problem. An answer and formula that works for this should solve my issue.

Thanks

Here's how I would do it.

Attachment 290096

[Jonmo1]

What will be in the 'between' cells? (C6 E6 G6 etc.)
I would assume nothing, because there was nothing there to begin with.

Basically, put the first calculations (=B2*B4 and =D2*D4) in a new previously unused row.
Say we put them in Row6 for example.
This way, we are sure that there is nothing in the between cells.
Then in Say B8 (another previously unused cell)
=SUM(6:6)
This will then sum all the values in Row6 no matter how many you add later.

Then on your summary page as before
=SUM(start:end!B8)

[cheeko]

In between these cells you ask are a variety of inputs: more numbers, equations, text, sometimes blank cells, anything.

The issue is utilizing blank cells somewhere (like you're suggesting) that should work.

My only hiccup is that many of these cells are already locked, hidden and otherwise unaccessible, including adding more sheets, except for those in between the (start:end) portion.

But I will give it a try. Thanks

What will be in the 'between' cells? (C6 E6 G6 etc.)
I would assume nothing, because there was nothing there to begin with.

Basically, put the first calculations (=B2*B4 and =D2*D4) in a new previously unused row.
Say we put them in Row6 for example.
This way, we are sure that there is nothing in the between cells.
Then in Say B8 (another previously unused cell)
=SUM(6:6)
This will then sum all the values in Row6 no matter how many you add later.

Then on your summary page as before
=SUM(start:end!B8)

[Jonmo1]

You're welcome

[cheeko]

Thanks everyone for your help and input!

Although many of my cells were locked I was able to use all these suggestions and find a work around even with the spreadsheet limitations.

You're welcome

[alvin-chung]

Great to hear that, and thanks for the repu



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin