SOLVED

Consulta SUMAR SI

%3CLINGO-SUB%20id%3D%22lingo-sub-2078388%22%20slang%3D%22es-ES%22%3ESee%20SUM%20SI%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2078388%22%20slang%3D%22es-ES%22%3E%3CP%3EGood%20morning%3C%2FP%3E%3CP%3EI%20want%20to%20use%20this%20formula%20in%20the%20cell%20.SUM.SI(A8%3AA11%3B%22%2045*%22%3B%20B8%3AB11)%20and%20it%20returns%20a%20value%20of%200%20to%20me.%3C%2FP%3E%3CP%3EOrders%20are%20in%20cells%20A8%20through%20A11%20and%20amounts%20in%20B8%20through%20B11.%3C%2FP%3E%3CP%3EThe%20formula%20in%20cell%20D4.%3C%2FP%3E%3CP%3EI've%20been%20looking%20into%20help%20and%20I%20can't%20find%20the%20fault.%20I%20want%20if%20the%20orders%20start%20with%2045%20I%20will%20be%20in%20the%20corresponding%20amounts.%20But%20it%20gives%20me%200.%3C%2FP%3E%3CP%3EI'd%20really%20appreciate%20your%20help.%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%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-2078388%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-2078620%22%20slang%3D%22en-US%22%3ERe%3A%20Consulta%20SUMAR%20SI%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2078620%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F935935%22%20target%3D%22_blank%22%3E%40Mirti%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20it%20shall%20work%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20348px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247728i6F1D5ED0AAF62A64%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIt's%20better%20to%20check%20sample%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2078636%22%20slang%3D%22es-ES%22%3ERe%3A%20Check%20SUM%20YES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2078636%22%20slang%3D%22es-ES%22%3E%3CP%3EI%20attach%20the%20excell%20to%20see%20if%20anyone%20can%20tell%20me%20why%20the%20formula%20fails.%3C%2FP%3E%3CP%3EThanks%20a%20lot.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Mirti_0-1611148394946.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247731i79EFD2AF3932046E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Mirti_0-1611148394946.png%22%20alt%3D%22Mirti_0-1611148394946.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Buenos días,

quiero usar esta fórmula en la celda =SUMAR.SI(A8:A11;"45*";B8:B11) y me devuelve el valor 0.

Los pedidos están en las celdas de A8 a A11 y los importes en B8 a B11.

La fórmula en la celda D4.

He buscado en la ayuda y no encuentro el fallo. Quiero que si los pedidos empiezan por 45 me sume los importes correspondientes. Pero me da 0.

Agradecería mucho su ayuda.

 
 

 

 

12 Replies

@Mirti 

In general it shall work

image.png

It's better to check sample file

Adjunto el excell para ver si alguien me puede decir por qué falla la fórmula.

Muchas gracias.

Mirti_0-1611148394946.png

 

@Mirti puede ser Excel entender la columna A tiene numeros, pero las " " son per il texto. intenta hacer otra columna con el espacio delante el pedido code con la formula en columna C, por C6 =" "&A6 e despues =SUM.SI(C6:C9;" 45*";B6:B9)

@AnaBoyko

Ha funcionado! Como no soy una experta me parece difícil de entender esa solución.

Pero ha funcionado.

Muchas gracias Ana. 

@Mirti 

¿Podría haber otra solución en la que no haya que crear otra columna?

 

Best Response confirmed by Mirti (Occasional Contributor)
Solution

@Mirti 

Most probably you have numbers, not texts in left column. When second formula, please check attached file.

image.png

As a comment, it's much better to share sample file instead of screenshot.

@Sergei Baklan 

Me da un error, pero sigo intentando tu fórmula.

Muchas gracia

@Sergei Baklan 

Ya me ha salido. En español, en las funciones se separan las condiciones con ; en lugar de ,

En la parte de la Función IZQUIERDA(A6:A9;2)

Muchas gracias Sergei.

 

@Mirti , you are welcome.

You may open the file attached to my post, formulas will be automatically transformed into your locale.

@Sergei Baklan 

I have opened the file and formulas haven´t been transformed in spanish.

@Mirti 

Do you mean the formula which you see in formula bar? If your Excel is on Spanish formula shall be shown for Spanish locale. And you always may use this tool Translator • Excel-Translator (excel-translator.de)

@Sergei Baklan 

Good morning Sergei,

Thank you very much for your help.

Best regards.