Using Indirect to reference multiple cells

English
,
#1

I’ve edited this whole post, in the hope of making things clearer. The original Post was confusing.

I’m trying to come up with a formula to conditionally sum identical ranges that exist in separate sheets with an all in one formula.

Essentially I’m trying to do this:

=SUMIF(Sheet2!$A$3:$A$8,Summary!A5,Jan!B3:B8)+SUMIF(Sheet3!$A$3:$A$8, Summary!A5,Feb!B3:B8)+SUMIF…

The Above Formula is for illustration of what I am trying to achieve only. The Cell Ranges and Sheet Names do not apply to my examples below, or the calc sheet I have provided for download. The Formula was taken from 3D SUMIF myonlinetraininghub

Which will yield a very long formula, which will eventually become impractical to edit over time.

In excel i believe what i want to achieve is called 3D SUMIF. These are references articles I started with, which are for Excel only, unfortunately. Here are the links for the Articles:

3D SUMIF Exceljet

3D SUMIF myonlinetraininghub

I have been trying to convert the formula from the articles listed above for use in Libreoffice. What I got so far is =SUMPRODUCT(SUMIF(INDIRECT(B6&".A1:A10"),A2,INDIRECT(B6&".B1:B10")))

WHERE

B6 is=$A$6:$A$7 WHERE A6 and A7 are the cells that contain the Sheets Names, Sheet2 and Sheet3. The purpose of this is, when i need to add more Sheet Names, Sheet4, Sheet5 etc etc, I just add them to the next cell, for example A8. So now =$A$6:$A$7 would change to =$A$6:$A$8.

In this way, I will not need to touch the formula, and just add Sheet Names between A6 to A30 for example. For this to work, B6 should pick up the updated list. Right now it only picks up 1 sheet.

I hope this is clear now.

A1:A10 contains the numerical values to SUM from Sheet2 and Sheet3.

A2 = any conditional value i enter from cells B1:B10 from Sheet2 and / or Sheet3 (well actually not all the way to B10)

EXAMPLE: So now if I enter the Letter “a” into cell A2. The Formula should lookup A1:A10 in Sheet2 and Sheet3 and sum the corresponding values.

From the example above, lets say “a” corresponds to one cell in Sheet2 with the number 5 and another in Sheet3 with the number 10, I should get total 15.

I’m not sure if this is possible with Libreoffice. The only problem I am having right now is to declare more than 1 sheet in INDIRECT().

With 1 Sheet, the formula works. I’ve also tried Concatenate, but i’m still searching around the web on how to declare Concatenate properly for this purpose.

I know this may seem silly, but this is actually for an accounting sheet, where i may enter the name of a person and instantly know the outstanding due, form January to December.

My sincere apologies for the unclear explanation previously, i asked for help after hours of searching. My mind was basically lagging by then.

I understand it will be better to use Database for this, and I am working on it. But it is going to take time as i’m learning.

I have included the link for the document with the formula and example below.

Here is the Download Link for the Document

#2 
=SUMPRODUCT(SUMIF(INDIRECT(B11&".B1:B8"),A2,INDIRECT(B11&".A1:A8")))

If cell B11 contains: $A$10:$A$11 then B11 & ".B1:B8" yields: $A$10:$A$11.B1:B8 which is not a valid reference, hence INDIRECT() must fail with #REF! and in consequence all the rest of the formula.

Please don’t expect an answer - From your description I cannot take what you really want to calculate (may be my bad) and a sample file may be very helpful here.

#3

Yes, i agree B11 must fail, which is why i tried =$A$10:$A$11 as well, which returned the results from only Sheet2

Basically i’m trying the sum the numerical values of 2 sheets, with corresponding variables (a to f) entered in column A and numerical values entered in Column B in Sheet2 and Sheet3

When the any variable (a to f) is entered on my summary sheet in cell A2, it should sum the numerical values from both Sheets. Similarly if i enter “b” in A2, it should sum the corresponding numerical values from both Sheets

I’ve been trying to replicate this solution which is done on excel:

Excel Solution 3D SUMIFS

#4

which is why i tried =$A$10:$A$11

Now you enter the complicated area of implicit intersections and whether INDIRECT() works, heavily depends on what is in cell $A$11 If B11 is set to =$A$10:$A$11 the B11 implicitly intersects with A11 (both cells share the same row 11) and the result will be de facto: INDIRECT(A11 & ".B1:B8") - this might work, if cell A11 contains an existing sheet name.

#5

Do you want to get something like =SUMPRODUCT(Sheet2.$A$1:$A$8=$A$2;Sheet2.$B$1:$B$8)+SUMPRODUCT(Sheet3.$A$1:$A$8=$A$2;Sheet3.$B$1:$B$8) but in short form with INDIRECT() and dynamic sheetnames? Not sure that it’s possible (and that is worth the effort)

#6

@anon73440385:

Now you enter the complicated area of implicit intersections and whether INDIRECT() works, heavily depends on what is in cell $A$11 If B11 is set to =$A$10:$A$11 the B11 implicitly intersects with A11 (both cells share the same row 11) and the result will be de facto: INDIRECT(A11 & ".B1:B8") - this might work, if cell A11 contains an existing sheet name.

Goodness, I tried this and you’re right! If I enter the formula in B10, I get the sheet name entered in A10. Formula in B11 gets the name entered in A11. Thats frustrating but at the same time interesting!

#7

@JohnSUN

I’m trying to use SUMIF to conditionally sum identical ranges that exist in separate sheets with an all in one formula. Your formula is something like what I want to do but it’ll be extremely tedious. Thank you

#8

@anon73440385:

I’m trying to see what I can do with CTRL + SHIFT + ENTER

#9

There are at least two solutions for any problem. I see four solutions for your problem. The first way is to find the required values ​​in a confusing data structure using an equally convoluted formula. The second way is to preliminarily bring the confusing data to an ordered form in an auxiliary table and use this table for searching. You named the third way yourself - the database. And the fourth way is a not very complex macro (user-defined function) that will do all the work with each sheet of the book and return the desired result. You have chosen the most time consuming of the paths, the first. Do not hope that the formula that you may be able to find will be easy to understand and further maintain - it took me about an hour to analyze what you have already received.

#10

@JohnSun

I was under the impression that, well, a good amount of stuff that can be done in Excel can also be done on Calc. Which has been my experience since I began using Openoffice.

The sheet I have provided is a test sheet. Obviously the data is not as per the test sheet. The actual formula is to be used on an accounting sheet, where if i enter the name of a person/company, I’ll be able to get the amounts owed, paid etc etc.

I might have found a fifth way actually. perseverance does pay off!

#11

Yes, you are right, a lot can be done exactly the same in Excel and Calc - after all, both are just a spreadsheet, the basic principles are the same. But nevertheless, these are different programs - some differences are and always will be. For example, Excel does not want to understand a boolean value as a numeric 0 or 1, in order to use the result of the array test as one of the parameter in SUMPRODUCT(), you have to additionally wrap the comparison result in the N() function. Or Excel doesn’t know the OOO/LiO native STYLE() function. At the same time, Excel has functions from Google tables, a PowerQuery data manipulation mechanism and something else that is not in Calc.

What is the fifth way you mentioned?

#12

@JohnSUN

Ok, so, for fifth way, i’m just going to upload an example sheet. Basically I’m using SUMIFS on individual sheets, and using SUM on the summary sheet. here’s the neat thing, well for me its neat coz its simple and a quick fix :stuck_out_tongue:

the cell where you enter the names to query, they = to the name cell on the summary sheet. so now you only enter the name on the summary sheet and get the info for how much the customer has paid, whats outstanding, for each month, summed up :smiley: I mean its pretty cool for me coz i don’t know anything. I’ll include the sheet’s download link in the answer column

#13

@JohnSUN

So I just used SUMIFS on the two Sheets, November and December, and they reference the input field (where i input the names) and populate the input fields on November and December. Now I get the details in the Summary Sheet as well as November and December. Kinda neat. :stuck_out_tongue:

Next, figuring out how to color the cells which are identified by SUMIFS…

#14

In other words, the summation is for each sheet separately. This is not the only 3D formula you were trying to get. Well, you at least tried.

#15

So, here’s a quick fix for what i was trying to do. The sheet will be self explanatory. On the Summary Sheet enter the customer’s name from the Name Columns from Sheet November or December. You’ll get the Total Paid and Total Outstanding

Here’s the Download Link for the File

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.