Convert Excel multi function formula to Calc

[NewerCalcUser]

=SUMPRODUCT(SUMIF(INDIRECT("’"&$A$1:$A$15&"’!$A$2:$A$33"),$A19,INDIRECT("’"&$A$1:$A$15&"’!D$2:D$33")))

The formula works in Excel but in Calc it’s giving sporadic results - which indicates to me the syntax isn’t completely incorrect.

I have the profile formula set to Excel A1.

Cells A1:A15 contain the names of 15 different sheets I’m wanting to search and total the amounts.
Cell A19 contains the value to search each sheet. The values being search are in A2:A33 on each sheet and the amounts being added in each sheet are in D2:D33.

I’m not getting any errors and I haven’t been able to determine a pattern in the results but some results are coming from different sheets but none are totaling from multiple sheets. I initially thought there could be an issue with the cells being different formats so it wasn’t finding the values but all appear to be the same.

Any suggestions would be appreciated.

[Rugslug]

Could it be the ! (exclamation mark)? LO uses a . (period/dot) to separate the sheet name from the cells. The formula may not convert properly when xls is loaded into LO?

[rautamiekka]

Yes, LO uses a dot whereas Google Sheets uses §!§. The format is §‘SHEET_NAME’.COLUMN#§ where §SHEET_NAME§ is the target sheet’s name in between apostrophes, then a dot, the column letter and then the row number. Google Sheets uses the exactly same but not a dot.

[NewerCalcUser]

NewerCalcUser gravatar image NewerCalcUser 1 ●1

First thanks for the response. Tried replacing the exclamation mark (!) with a period (.) it didn’t give an error but gave no results.

[NewerCalcUser]

I’ve manipulated the formula many ways replacing the “!” with “.” and placing a “$” in front of the sheet names. The issues seems to be around the first range (A1:A15) giving the sheet names. If I replace that range with a single value - lets say A1 in both places, I get the correct results for the one sheet as long as the value I’m searching on is on the sheet.

[NewerCalcUser]

Reported bug as recommended below #85539

[ROSt53]

Unfortunately I don’ t have MSO anymore to make a test.

What makes me wondering is @NewerCalcUser 's statement “it’s giving sporadic results”. If there are “sporatic” results there must be different conditions for the results and the cases without results. Whenever I has sporadic results in Excel or in Calc, I also had an error in one of the cells delivering information to the cell with the formula which did not show a correct result.

I would look into a result case and a non result case in ALL cells contributing to the cell containing “=SUMPRODUCT(SUMIF(INDIR…”- formula for differences in formats and especially if all contributing cells are populated. I also would use the elements of the “Detective” toolbar.

[NewerCalcUser]

Since I’m not as familiar with Calc, I will have to look into the “Detective” toolbar. Maybe my use of sporadic wasn’t the best choice of words. Out of 40 rows looking to get results, 6 would produce results. I still believe the issue has to do with being able to reference a range that has all the sheet names and getting the formula in this manner.

[mariosv]

I think their is a bug, take a look at the example file. Seems only one sheet is summed.

Sumproduct_test_multisheet

Please could you report the bug.

Bug Submission Assistant

If you want attach sample file to the report.

[NewerCalcUser]

Unfortunately I could not retrieve and view your file. I created another test file and I am only getting results from the last sheet in the range but for only one row. If I replace the range with a single cell reference it sums the entire sheet correctly - so it works for one cell reference but not a range.

There doesn’t seem to be a way to post my example file - I don’t have an account set up with the Bug Submission Assistant but may look into setting up to report.