Sumar.si.conjunto

[AGambus]

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 

[#2]

@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))

[#3]

 Thanks a lot. @Hans Vogelaar 

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º	X	Y	Z
603135	5	8	9

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

[#4]

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