I am trying to sum a cell from many sheets dependent on a condition in each sheet. using libreoffice calc

Question

the formula =SUM(IF($Sheet1.C16=main.B12,$Sheet1.M9,0)+IF($Sheet2.C16=main.B12,$Sheet2.M9,0)) works but listing a hundred sheets is not workable I have tried =SUM(IF($Sheet1.C16=main.B12,$Sheet1.M9,0):IF($Sheet2.C16=main.B12,$Sheet2.M9,0)) and many varations of sumifs and sumif I do not seem to find a formula that will work.

I would expect sum cell value of all sheets that meet the condition

Answer 1

If you have to handle such a poorly designed spreadsheet, and the formula with a built-in function is too cumbersome (and also broken), then just write your own function. For example, like this:

Option Explicit 
Function Sheets_SUMIF(vCriterion As Variant, sCellCompare As String, sCellToSum As String, Optional sExcludeSheet As String) As Double 
Dim oSheets As Variant, oSheet As Variant, aTemp As Variant, aRes As Double 
    If isMissing(sExcludeSheet) Then sExcludeSheet = ""
    For Each oSheet In ThisComponent.getSheets()
        If StrComp(oSheet.getName(), sExcludeSheet, 0) <> 0 Then
            aTemp = oSheet.getCellRangeByName(sCellCompare).getDataArray()
            If aTemp(0)(0) = vCriterion Then aRes = aRes + oSheet.getCellRangeByName(sCellToSum).getValue()
        EndIf 
    Next oSheet
    Sheets_SUMIF = aRes
End Function

It's not much longer than listing the names of a hundred sheets in one formula. Call it as =SHEETS_SUMIF($main.B12;"C16";"M9";"main") and get result.