Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como utilizar la WorksheetFunction SumIf


(adsbygoogle = window.adsbygoogle || []).push({});

En este post WorksheetFunction funciones de Excel en VBA se dijo que WorksheetFunction tiene por objeto ejecutar casi todas las funciones que trae incorporadas Excel, es decir se puede agregar en una macro y echar manos a estas funciones, luego se expuso un listado de todas las funciones de Excel que se pueden ejecutar desde una macro de VBA, en el ejemplo que se presenta en el post actual te voy a mostrar como se usa la función o WorksheetFunction SumIf o SumarSi, en WorksheetFunction SumIfs o SumarSi.Conjunto encontrarás como se usa esta función que tiene como diferencia que se pueden utilizar varios criterios de suma a la vez; en Funciones de Excel, descripción y traducción al inglés y otros idiomas, encontrarás listadas y con una breve explicación sobre que realiza cada una de las funciones de Excel; en Función Si se presenta un tutorial sobre el uso de esta función en Excel.

SumIf permite sumar las celdas de un rango que cumplen un criterio especificado la sintaxis es la siguiente:


WorksheetFunction.SumIf(Arg1,Arg2,Arg3)

El significado de los parámetros son:

Arg1, es un dato obligatorio, siendo el rango de celdas que se deben evaluar según los criterios especificados.


Arg2, es un dato también obligatorio, se refiere a los criterios en forma de número, expresión o texto, que determinan las celdas que se deben sumar, los criterios se pueden expresar como 12, «12», «<12» o «palmeras». En los criterios se pueden usar caracteres comodín (?) y (*); donde el «?» corresponde a un solo caracter y el «*» a una cadena de caracteres, si lo que se desea buscar es (?) o (*) se debe escribir un tilde (~) antes del caracter.


Arg3, es un dato opcional, representa las celdas que se deben sumar si se cumple los criterios de las celdas correspondientes del rango del Arg1, si se omite el dato, las celdas del Arg1 se evaluarán según los criterios datos y se sumarán si se cumplen los mismos.


Antes de seguir recomiendo leer un excelente libro sobre Excel que te ayudará operar las planillas u hojas de cálculo, haz click acá, si quieres aprender sobre Excel, en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet.

  

Específicamente en el ejemplo, que podrás descargar desde el link del final, se suma el rango de la columna A, que es determinado automáticamente, evalúa la columna B, si el dato de la celda es «>2000» entonces suma la celda correspondiente de la columna A, la suma es colocada al final de la columna A.

Para sumar el código necesario es Application.WorksheetFunction.SumIf(Range(«B2» & «:B» & uf – 1), «> 2000», Range(«A2» & «:A» & uf – 1)) el Argumento 1 «Range(«B2» & «:B» & uf – 1)» es el rango que se debe evaluar, luego se debe determinar el criterio  «> 2000»; por último se coloca el rango que se desea sumar si se cumple el criterio, «Range(«A2» & «:A» & uf – 1)».

Se debe notar que el rango se arma en forma automática con «Range(«B2» & «:B» & uf)», se utiliza el método Range concatenando la columna y la última fila con datos, lo mismo se hace con el rango que se debe sumar.

El vídeo que sigue muestra una explicación más detallada y gráfica de la macro presentada, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo formulario que crea un listado de todas las hojas para poder luego seleccionarlasbuscar en listbox mientras escribes en textboxordenar hojas libro excel por su nombreconectar Excel con Access y muchos ejemplos más.

function onYtEvent(payload) {
if (payload.eventType == ‘subscribe’) {
// Add code to handle subscribe event.
} else if (payload.eventType == ‘unsubscribe’) {
// Add code to handle unsubscribe event.
}
if (window.console) { // for debugging only
window.console.log(‘YT event: ‘, payload);
}
}



Código que se inserta en un userform

Sub SumIf()
Application.ScreenUpdating = False
‘On Error Resume Next
DisplayAlerts = False
Dim uf As String
uf = Sheets(«Hoja1»).Range(«A» & Rows.Count).End(xlUp).Row
Cells(uf + 1, «A») = Application.WorksheetFunction.SumIf(Range(«B2» & «:B» & uf – 1), «> 2000», Range(«A2» & «:A» & uf – 1))
DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.


(adsbygoogle = window.adsbygoogle || []).push({});

If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.

Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends      

Entradas relacionadas

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Comentarios (36)

hello!,I love your writing very much! proportion we keep up a correspondence
more about your post on AOL? I require a specialist on this area to
unravel my problem. May be that is you! Taking a look forward to see you.

Responder

I am the owner of JustCBD Store company (justcbdstore.com) and am trying to grow my wholesale side of business. I am hoping anybody at targetdomain give me some advice ! I thought that the most ideal way to do this would be to talk to vape companies and cbd retailers. I was hoping if anybody could suggest a dependable web site where I can get UK Vape Shop Database I am already taking a look at creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. Unsure which one would be the most ideal option and would appreciate any support on this. Or would it be simpler for me to scrape my own leads? Suggestions?

Responder

процесс, требующий очень вдумчивого и аккуратного подхода посмотрите что советуют профессионалы ликвидация ООО а так же банкротство фирмы с долгами

Responder

You’re so awesome! I don’t think I have read something like that before. So wonderful to discover another person with some genuine thoughts on this issue. Really.. thank you for starting this up. This site is something that’s needed on the web, someone with a bit of originality!

Responder

Having read this I thought it was extremely enlightening. I appreciate you finding the time and energy to put this content together. I once again find myself personally spending a significant amount of time both reading and posting comments. But so what, it was still worthwhile!

Responder

It’s hard to come by experienced people about this subject, however, you seem like you know what you’re talking about! Thanks

Responder

Pretty! This has been an extremely wonderful post. Thank you for supplying this info.

Responder

Excellent post. I definitely appreciate this site. Continue the good work!

Responder

I really like it when individuals get together and share ideas. Great site, stick with it!

Responder

There’s definately a great deal to know about this subject. I really like all of the points you made.

Responder

Oh my goodness! Incredible article dude! Thank you so much, However I am experiencing issues with your RSS. I don’t know why I cannot subscribe to it. Is there anybody else having identical RSS problems? Anybody who knows the solution can you kindly respond? Thanks!!

Responder

This excellent website definitely has all the information I wanted concerning this subject and didn’t know who to ask.

Responder

Good post. I’m dealing with a few of these issues as well..

Responder

Very good post. I am going through some of these issues as well..

Responder

I must thank you for the efforts you have put in writing this website. I am hoping to see the same high-grade blog posts by you in the future as well. In fact, your creative writing abilities has motivated me to get my very own blog now 😉

Responder

Having read this I thought it was really enlightening. I appreciate you spending some time and effort to put this short article together. I once again find myself spending a lot of time both reading and posting comments. But so what, it was still worth it!

Responder

Hello there, There’s no doubt that your site could possibly be having browser compatibility issues. Whenever I take a look at your site in Safari, it looks fine however when opening in IE, it has some overlapping issues. I just wanted to give you a quick heads up! Aside from that, fantastic blog!

Responder

Aw, this was a very good post. Taking a few minutes and actual effort to produce a very good article… but what can I say… I put things off a whole lot and never manage to get anything done.

Responder

You’ve made some decent points there. I looked on the net to learn more about the issue and found most individuals will go along with your views on this web site.

Responder

Great post! We are linking to this particularly great content on our site. Keep up the good writing.

Responder

Greetings! Very helpful advice in this particular post! It is the little changes that will make the most important changes. Thanks for sharing!

Responder

I absolutely love your blog.. Pleasant colors & theme. Did you build this amazing site yourself? Please reply back as I’m planning to create my own website and would love to learn where you got this from or just what the theme is named. Thanks!

Responder

Hi, I do think this is an excellent site. I stumbledupon it 😉 I’m going to revisit yet again since I book marked it. Money and freedom is the best way to change, may you be rich and continue to guide other people.

Responder

I seriously love your website.. Great colors & theme. Did you develop this website yourself? Please reply back as I’m attempting to create my very own blog and would like to find out where you got this from or just what the theme is called. Many thanks!

Responder

It’s nearly impossible to find educated people for this subject, but you seem like you know what you’re talking about! Thanks

Responder

Way cool! Some extremely valid points! I appreciate you writing this article and also the rest of the website is really good.

Responder

You have made some really good points there. I looked on the web for more information about the issue and found most people will go along with your views on this website.

Responder

After checking out a few of the blog articles on your site, I seriously like your technique of blogging. I saved it to my bookmark website list and will be checking back in the near future. Take a look at my web site as well and let me know how you feel.

Responder

Having read this I believed it was very enlightening. I appreciate you taking the time and effort to put this short article together. I once again find myself spending a significant amount of time both reading and leaving comments. But so what, it was still worthwhile!

Responder

Good post. I learn something totally new and challenging on sites I stumbleupon every day. It will always be interesting to read through articles from other writers and practice something from their websites.

Responder

I used to be able to find good information from your content.

Responder

I’d like to thank you for the efforts you have put in writing this blog. I’m hoping to see the same high-grade content by you in the future as well. In fact, your creative writing abilities has motivated me to get my own blog now 😉

Responder

You should take part in a contest for one of the greatest blogs online. I most certainly will recommend this website!

Responder

Hello very nice site!! Guy .. Excellent .. Superb .. I will bookmark your
site and take the feeds additionally? I am happy to find numerous helpful information here within the put up,
we need work out more techniques in this regard, thanks for sharing.

. . . . .

Responder

Everything is very open with a precise description of the issues.
It was really informative. Your website is useful. Many thanks for sharing!
adreamoftrains website hosting companies

Responder

I needed to thank you for this very good read!! I certainly loved every bit of it. I have you book-marked to check out new stuff you post…

Responder