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 t...
excel
Formulas and Functions
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.