# Convert Excel multi function formula to Calc [closed]

=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.

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?

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 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.

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.

Reported bug as recommended below #85539