Error en función SUBTOTALES(109;...)

%3CLINGO-SUB%20id%3D%22lingo-sub-2341527%22%20slang%3D%22es-ES%22%3ESUBTOTALS%20function%20error(109%3B...)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2341527%22%20slang%3D%22es-ES%22%3E%3CP%3EI%20have%20detected%20that%20when%20a%20single%20row%20is%20hidden%2C%20the%20SUBTOTALS(109%3A%2C%2C%2C)%20function%20continues%20to%20sum%20the%20hidden%20row.%3C%2FP%3E%3CP%3EIt%20doesn't%20happen%20like%20that%2C%20when%20you%20hide%20more%20than%20one%20row%20at%20a%20time%2C%20you%20do%20it%20correctly.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22InkedCaptura%20Inicial_LI.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F279481i6B22AE9BBF815696%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22InkedCaptura%20Inicial_LI.jpg%22%20alt%3D%22InkedCaptura%20Inicial_LI.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22InkedCaptura%20Final_LI.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F279482iA3B5B1D6735F66FA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22InkedCaptura%20Final_LI.jpg%22%20alt%3D%22InkedCaptura%20Final_LI.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ERow%2018%20is%20hidden%2C%20the%20total%20must%20be%2011.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20greeting%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2341527%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-2341590%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20en%20funci%C3%B3n%20SUBTOTALES(109%3B...)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2341590%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1049972%22%20target%3D%22_blank%22%3E%40Usatges%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20line%20between%20rows%2017%20and%2019%20confuses%2C%20perhaps%20you%20have%20not%20hided%20%2318%20but%20the%20row%20with%20quite%20small%20row%20height%20(not%20zero).%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2344499%22%20slang%3D%22es-ES%22%3ERe%3A%20SubTOTALS%20function%20error(109%3B...)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2344499%22%20slang%3D%22es-ES%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20input%2C%20but%20when%20I%20hide%20the%20row%20I%20do%20it%20with%20the%20right%20mouse%20button%20selecting%20the%20row%20number.%20As%20it%20appears%20in%20the%20images%20it%20is%20simply%20by%20giving%20it%20%22Hide%20Row%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2346784%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20en%20funci%C3%B3n%20SUBTOTALES(109%3B...)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2346784%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1049972%22%20target%3D%22_blank%22%3E%40Usatges%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20see%20that%20rows%20from%204%20to%208%20are%20also%20hidden.%20If%20they%20are%20filtered%2C%20not%20manually%20hided%2C%20when%20109%20won't%20ignore%20other%20manually%20hided%20rows%20in%20the%20same%20table.%20Try%20to%20check%20with%20visible%204%3A8%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

He detectado que cuando se oculta una sola fila, la función SUBTOTALES(109:,,,) sigue sumando la fila ocultada.

No ocurre así, cuando ocultas más de una fila a la vez, que si lo hace correctamente.

InkedCaptura Inicial_LI.jpgInkedCaptura Final_LI.jpg

Ocultada la fila 18, el total han de ser 11.

 

Un saludo

10 Replies

@Usatges 

The line between rows 17 and 19 confuses, perhaps you have not hided #18 but the row with quite small row height (not zero). 

@Sergei Baklan 

Gracias por tu aporte, pero al ocultar la fila lo hago con el botón derecho del ratón seleccionando el número de la fila. Tal como aparece en las imágenes es simplemente al darle "Ocultar Fila"

@Usatges 

I see that rows from 4 to 8 are also hidden. If they are filtered, not manually hided, when 109 won't ignore other manually hided rows in the same table. Try to check with visible 4:8

Si se ocultan a la vez dos o más filas, la función me funciona correctamente. Es decir, que solo falla cuando se oculta de una en una.
Yo tengo el parámetro 109 (Suma y excluye las filas ocultas). Lo he probado con otros parámetros (promedio, contar, contara) y sigue fallando. Solo si se oculta una fila.
Y he mostrado todas las filas.... y si oculto solo una, sigue fallando.
Pero si oculto dos o más filas funciona bien.
Acabo de hacer una prueba, he movido la celda a otro rango donde contiene la fórmula y entonces si la actualiza.
He vuelto a ocultar una sola fila y vuelve a fallar.
Para que se refresque el valor real, solo lo hace si muevo el contenido de la celda a otra celda.
As an alternative, have you tried using AGGREGATE?

@Usatges 

Sorry, translation doesn't work for some reason and I don't know Spanish...

LO he probado, y ocurre exactamente lo mismo. El Excel no recalcula con la nueva información en este caso. El ocultar solo una fila, si lo hace si ocultas a la vez más de una fila. O mueves la celda donde contiene la función a otra celda.
No te preocupes, Me apaño bien. Yo tampoco se inglés, pero conozco bastante el Excel.
En español se llama AGREGAR.
Gracias por tu interés.