How to using sumif accross multiple sheets

[LO_user4]

I am happy to join Libre Office Forum.

My query is simple. Please refer to excel sheet in attachment. It is an excel file which uses following commands.

1.sumproduct
2.sumif
3.indirect

and some expressions.

If I can explain.

In the SUM sheet the formula search for the item such as EXCAVATION in sheet 1 2 3 & 4.Then add up all the values. And then display the total values against each item. Same is the case for all the items.

I would like to make this happen in Libreoffice Cal. Please guide me.

I have tried to open the same sheet in Libreoffice, but the software is unable to understand the excel formula. It shows the error “Err:504”.

I am using…

Version: 7.1.2.2 (x86) / LibreOffice Community
Build ID: 8a45595d069ef5570103caea1b71cc9d82b2aae4
Locale: en-US (en_GB); UI: en-US
Calc: CL

Your cooperation in this regard shall be helpful.
BOQ CALCULATION.xlsx (50.1 KB)

[LeroyG]

First, you must replace the exclamation mark (!) by a dot (.).

[joshua4]

@erAck is about to note that so long as it is XLSX, then you need to ditch the ’ single quotes, the ! is fine.

[erAck]

Right, not if the Detailed Calculation Settings Reference syntax for string reference is set to Excel A1, which it is for this document, and that’s not the problem here. The single quotes around the numeric sheet names are also correct.
I didn’t investigate deeper, but I think the expectation what arrays the INDIRECT() expressions would return are wrong. If that works in Excel then please explain what exactly that does there, including the interim arrays returned by INDIRECT().

Specifically, what does Excel return for

=COUNTA(INDIRECT("'"&$I$7:$I$10&"'!"&"C7:C33"))

entered as array formula (Shift+Ctrl+Enter), where Calc returns

8
7
9
17

[joshua4]

I’m seeing that, too. I think that LO is taking a diagonal across the 3D reference for SUMIF. Replace SUMIF with SUMIFS for all cases, and it might be working.

For the above, see the larger, green region added to the SUM sheet and the use of SUMIFS for the blue column.

I did specifically try the references with the single quotes and had problems…I get it that they would be necessary for sheet names including spaces, but I don’t think they work with INDIRECT.

For this, see the small yellowish region added to the SUM sheet. Notice that the top cell with the ’ in the INDIRECT returns #REF!, while the lower cell without any ’ returns the correct 42.

BOQ CALCULATION.xlsx (36.5 KB)

PS: Looks like Excel 2016 doesn’t like any 3D references in INDIRECT…checking quickly. However, Excel 2016 handles the OP’s original SUMIF flawlessly while LO (7.2 and 7.3) throw error 504. Looks like an issue with SUMIF compatibility if not an outright bug in LO’s SUMIF.

[mikekaganski]

@erAck

Specifically, what does Excel return for

=COUNTA(INDIRECT("'"&$I$7:$I$10&"'!"&"C7:C33"))

entered as array formula (Shift+Ctrl+Enter)

It returns a single-cell array with value of 8:

[erAck]

@mikekaganski

It returns a single-cell array with value of 8

And what if 4 rows in one column are preselected when entering the array formula?

[mikekaganski]

[erAck]

Hmm… so that is identical. Debugger to the rescue…

Update
Fwiw… SUMIF() for such reference lists if a separate sum-range is also given is simply not implemented, hence Err:504.
Someone may want to submit a RFE (Request For Enhancement) bug.

[erAck]

@joshua4

see the small yellowish region added to the SUM sheet. Notice that the top cell with the ’ in the INDIRECT returns #REF!, while the lower cell without any ’ returns the correct 42.

That is something different. Note that

"'1:2'!I1"

is one string that of course does not work (because it would denote one sheet named 1:2), whereas

="'"&$I$7:$I$10&"'!"&"C7:C33"

(of the original document) in array context creates an array of 4 strings

'1'!C7:C33
'2'!C7:C33
'3'!C7:C33
'4'!C7:C33

where each works fine as an argument to INDIRECT() and passing them as array works as well, as can be seen with my COUNTA() example.

Your hint with the “diagonal” (I’d guess it iterates over single positional elements trough the arrays) may point into the right direction though.
Update:
Or actually not. That is not what is passed to SUMIF(). In your example document H16:H19 is the flattened 2D result (for display) of the 4 individual arrays. It is like you say a “diagonal”, but unrelated to the SUMIF() error result. Whether that 2D result makes sense in the Excel sense I don’t know.

[joshua4]

@erAck

That is something different.

Yes, it is something different. Thanks. I confused myself by thinking about how Excel did 3D/multi-sheet references rather than what was happening with the array environment…and I was incorrect-ish about Excel, too…

(So…this is a little off topic given the exact formulas the OP is using, but I think it bears continuing a little.)

In Excel if you must use ’ ticks for sheet names (with spaces, say) then you include both sheet names and the : for a 3D reference within a single pair of ’ ticks. This seems odd to me from a parsing perspective. But, in Excel:

=SUM('Sheet 2:Sheet 3'!I1) works, and =SUM('Sheet 2':'Sheet 3'!I1) does not work.

Nonetheless, as of 2016, I still find that no form of 3D reference seems to work with INDIRECT in Excel.

In Calc:

=SUM('Sheet 2:Sheet 3'!I1) does not work, and =SUM('Sheet 2':'Sheet 3'!I1) does not work.

Nonetheless, as of 7.3, unticked INDIRECT 3D references do work in Calc.

I do not see how to do a 3D reference in Calc if there are spaces in one of the sheet names, not that that is particularly a problem in itself. But the ticks are a compatibility question.

As for the SUMIF, I see what you are saying, but honestly I’d have to lock myself in a dimly lit room to picture why H16:H19 doesn’t cut the arrays orthogonally, unless is was literally a developer choice, as you say, for display purposes. I might see if I can capture the actual result in MRI somehow.

That said, SUMIF does not work, and SUMIFS does.

[erAck]

@joshua4

why H16:H19 doesn’t cut the arrays orthogonally

The final display result of a function that expects a scalar argument but is called with an array in array context instead is also an array. The argument’s array elements are iterated and for each the function is called, the result is placed in the result matrix at the iterated position (first call first result at first position, second call second result at second position, …). This happens also if the function returns an array, but the code assembling the final result does not handle that there are different result arrays. Hence while iterating it picks the first value from the first array to the first position, the second value from the second array to the second position, and so on. There is no correct solution to this as the actual result is a sequence of 2D matrices, displaying the first element of each as results might be better but may be equally wrong, maybe best would be to display an error in this case. What does Excel display for

=INDIRECT("'"&$I$7:$I$10&"'!"&"C7:C33")

entered as array formula in a preselected 4 rows area?

[mikekaganski]

[erAck]

This confirms the easy take
Thanks Mike!

[LO_user4]

Thanks a lot all of you!!!

For reviewing my problem…Much appreciated… .

I request to please formulate a function so that same result can be achieved in Libreoffice Cal…

I shall be grateful…thanks

[joshua4]

@joshua4

Replace SUMIF with SUMIFS for all cases

Doesn’t SUMIFS work for you, instead of using SUMIF? Just do SUMIFS with one pair. Remember that the order of arguments changes from SUMIF.

[LO_user4]

Thanks a bunch joshua4

Sorry I was unable to read the detailed post…My bad

Your work helped me…

The main difference in the formulae between Excel and Cal was that I have…

For pure .ODS file saved by LO_Cal

1. Replace (&I7:I10&) of excel with (&$I$7:$I$10&) in Cal
2. Replace (!) with (.)

Ref. file “BOQ CALCULATION (ODS file)”

For .XLSX file saved as .ODS by LO_Cal

1. Replace (&I7:I10&) of excel with (&$I$7:$I$10&) in Cal
2. Use (!)

Ref. file “BOQ CALCULATION (XLSX saved as ODS)”

All is working like a charm…

BOQ CALCULATION (ODS file).ods (18.3 KB)