funcion anidada

%3CLINGO-SUB%20id%3D%22lingo-sub-2737882%22%20slang%3D%22es-ES%22%3Enested%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2737882%22%20slang%3D%22es-ES%22%3E%3CP%3EI%20have%202%20formulas%20that%20apply%20on%20the%20same%20collunmna%20of%20data%2C%3C%2FP%3E%3CP%3E1-on%20the%20one%20hand%20a%20function%20add%20if%20according%20to%20condications%20that%20must%20be%20met%20and%3C%2FP%3E%3CP%3E2-another%20sub%20total%20function%20to%20give%20me%20the%20subtotal%20of%20the%20same%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20nest%20these%20two%20formulas%20so%20that%20on%20the%20one%20hand%20I%20add%20the%20cells%20that%20fulfill%20the%20function%20marked%20with%20an%20x%20and%20on%20the%20other%20hand%20that%20I%20add%20the%20subtotal%20if%20I%20make%20a%20filto%20selecting%20certain%20terms%20from%20some%20cell%20(in%20this%20case%20the%20name%20of%20the%20client)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethese%20are%20the%20two%20formulas%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMAR.SI.%20SET(U29%3AU7000%3BI29%3AI7000%3B%22%20x%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUBTOTALS(9%3B%20U27%3AU7000)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2737882%22%20slang%3D%22es-ES%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2738094%22%20slang%3D%22en-US%22%3ERe%3A%20funcion%20anidada%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2738094%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1151479%22%20target%3D%22_blank%22%3E%40cphb6220%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20use%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT(SUBTOTAL(9%2COFFSET(U29%3AU7000%2CROW(U29%3AU7000)-MIN(ROW(U29%3AU7000))%2C%2C1))%2C--(I29%3AI7000%3D%22X%22))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20Spanish%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMAPRODUCTO(SUBTOTALES(9%3BDESREF(U29%3AU7000%3BFILA(U29%3AU7000)-MIN(FILA(U29%3AU7000))%3B%3B1))%3B--(I29%3AI7000%3D%22X%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

tengo 2 formulas que aplican sobre una misma colunmna de datos ,

1-por una parte una funcion sumar si de acuerdo a condicones  que se deben cumplir y

2-otra funcion de sub total para que me entregue el subtotal de la misma  columna .

 

necesito anidar estas dos formulas para que por una parte me sume  las celdas que cumplen la funcion marcadas con una x y por otra parte que me sume el subtotal si es que  hago un filto seleccionando determinados  terminos de alguna celda ( en este caso el nombre del cliente)

 

estas son las dos formulas 

 

=SUMAR.SI.CONJUNTO(U29:U7000;I29:I7000;"x")

 

=SUBTOTALES(9;U27:U7000)

 

 

1 Reply

@cphb6220 

You can use

 

=SUMPRODUCT(SUBTOTAL(9,OFFSET(U29:U7000,ROW(U29:U7000)-MIN(ROW(U29:U7000)),,1)),--(I29:I7000="X"))

 

In Spanish:

 

=SUMAPRODUCTO(SUBTOTALES(9;DESREF(U29:U7000;FILA(U29:U7000)-MIN(FILA(U29:U7000));;1));--(I29:I7000="X"))