Forum Discussion

AGambus's avatar
AGambus
Copper Contributor
May 18, 2022

Sumar.si.conjunto

Hola, alguien sabe si funciona usar sumar.si.conjunto en varias hojas a la vez?

Lo he probado pero me da error.

Gracias

Hi, does somebody know how to use sum.if.conjunto in several worksheets at the same time?

I have tried but it returns an error message.

Thanks 

excel
Formulas and Functions

3 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 18, 2022

    AGambus SUMAR.SI.CONHUNTO does not work across sheets, but you can do the following:

     

    On a separate sheet, create a list of the sheet names that you want to use. Name this range Sheets.

    And let's say that the formula for one sheet looks like this:

     

    =SUMAR.SI.CONJUNTO(sheet1!sum_range; sheet1!criteria_range1; criteria1; sheet1!criteria_range2; criteria2)

     

    The multi-sheet formula would look like

     

    =SUMAR(SUMAR.SI.CONJUNTO(INDIRECTO("'"&Sheets&"'!sum_range"); INDIRECTO("'"&Sheets&"'!criteria_range1") criteria1; INDIRECTO("'"&Sheets&"'!criteria_range2"); criteria2))

    • AGambus's avatar
      AGambus
      Copper Contributor
      May 19, 2022

       Thanks a lot. HansVogelaar 

      Have another question, would appreciate if you could help.

      I have two files, first is origin, second is destination.

      Origin file has several sheets with same content structure but different nº and data

      nºXYZ
      603135589

       

      Destination file has a big table with all nº's in a column followed by X, Y, Z columns (empty cells). Is there any kind of VLOOKUP combination formula that I could use to retrieve all data at once?

       

      Thanks in advance.

      Antonio

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 19, 2022

        AGambus 

        In the destination workbook, create a list of the sheets in the origin workbook and name this range Sheets, as before.

        If you have Microsoft 365 or Office 2021, enter the following formula in B2 of the sheet in the destination workbook:

         

        =VLOOKUP($A2, INDIRECT("'[Origin.xlsx]"&INDEX(Sheets, MATCH(1, --(COUNTIF(INDIRECT("'[Origin.xlsx]"& Sheets &"'!A2:D1000"), $A2)>0), 0)) &"'!A2:D1000"), {2,3,4}, FALSE)

         

        In Spanish:

         

        =BUSCARV($A2; INDIRECTO("'[Origin.xlsx]"&INDICE(Sheets; COINCIDIR(1; --(CONTAR.SI(INDIRECTO("'[Origin.xlsx]"& Sheets &"'!A2:D1000"); $A2)>0); 0)) &"'!A2:D1000"); {2\3\4}; FALSO)

         

        where Origin.xlsx is the name of the origin workbook (it should be open in Excel too).

        Fill down.

         

        If you have an older version, select B2:B2, enter the formula mentioned above and confirm it with Ctrl+Shift+Enter, then fill down.