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:

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.