CONTAR.SI, SUMAR.SI y PROMEDIO.SI en R

R no tiene funciones CONTAR.SI, SUMAR.SI ni PROMEDIO.SI (COUNTIF, SUMIF y AVERAGEIF en la versión en inglés de Excel), y tampoco las necesita. Una condición en R devuelve TRUE o FALSE para cada fila, y combinando ese vector lógico con sum, mean o los corchetes [] puedes escribir cualquier fórmula condicional de Excel en una línea. En este tutorial aprenderás a traducir a R base y a dplyr CONTAR.SI, CONTAR.SI.CONJUNTO, SUMAR.SI, SUMAR.SI.CONJUNTO, PROMEDIO.SI, MAX.SI.CONJUNTO y MIN.SI.CONJUNTO, a usar criterios de texto y comodines, a tratar los valores faltantes y a calcularlos por grupos o como una nueva columna.

Respuesta rápida

Escribe el criterio como una condición y envuélvelo en la función que necesites. Suponiendo un data frame llamado pedidos con las columnas region e importe:

# CONTAR.SI(region; "Este")
sum(pedidos$region == "Este")

# SUMAR.SI(region; "Este"; importe)
sum(pedidos$importe[pedidos$region == "Este"], na.rm = TRUE)

# PROMEDIO.SI(region; "Este"; importe)
mean(pedidos$importe[pedidos$region == "Este"], na.rm = TRUE)

[1] 4
[1] 3050
[1] 1016.667

En el resto del artículo verás por qué funciona y cómo aplicar varias condiciones, comodines, valores faltantes, resultados por grupos y sus equivalentes en dplyr.

Datos de ejemplo

Usaremos el siguiente data frame con diez pedidos de una tienda. Cada fila tiene el vendedor, la región, el producto, el importe del pedido y su estado. Observa que falta el importe del séptimo pedido (NA), como si fuese una celda vacía en Excel.

pedidos <- data.frame(
  vendedor = c("Ana", "Bruno", "Ana", "Carlos", "Bruno", "Ana", "Diana", "Carlos", "Bruno", "Diana"),
  region   = c("Este", "Oeste", "Este", "Sur", "Oeste", "Oeste", "Este", "Sur", "Este", "Oeste"),
  producto = c("Portátil Pro", "Móvil", "Portátil", "Tablet", "Portátil Air",
               "Móvil", "Tablet", "Móvil", "Portátil", "Portátil Pro"),
  importe  = c(1200, 450, 900, 300, 1100, 500, NA, 480, 950, 1300),
  estado   = c("Pagado", "Pagado", "Pendiente", "Pagado", "Pagado",
               "Cancelado", "Pagado", "Pendiente", "Pagado", "Pagado")
)

pedidos

   vendedor region     producto importe    estado
1       Ana   Este Portátil Pro    1200    Pagado
2     Bruno  Oeste        Móvil     450    Pagado
3       Ana   Este     Portátil     900 Pendiente
4    Carlos    Sur       Tablet     300    Pagado
5     Bruno  Oeste Portátil Air    1100    Pagado
6       Ana  Oeste        Móvil     500 Cancelado
7     Diana   Este       Tablet      NA    Pagado
8    Carlos    Sur        Móvil     480 Pendiente
9     Bruno   Este     Portátil     950    Pagado
10    Diana  Oeste Portátil Pro    1300    Pagado

CONTAR.SI en R

Contar con sum(condición)

La fórmula de Excel =CONTAR.SI(E2:E11; "Pagado") cuenta cuántos pedidos están pagados. En R escribes la condición con == y la sumas:

sum(pedidos$estado == "Pagado")

[1] 7

¿Por qué sumar sirve para contar? Porque la condición devuelve un vector lógico, con un TRUE o un FALSE por fila:

pedidos$estado == "Pagado"

 [1]  TRUE  TRUE FALSE  TRUE  TRUE FALSE  TRUE FALSE  TRUE  TRUE

Cuando haces operaciones aritméticas con valores lógicos, R convierte TRUE en 1 y FALSE en 0, así que sum suma uno por cada fila que cumple la condición. Esta idea está detrás de todas las funciones de este tutorial.

Criterios numéricos

Los criterios de Excel como ">1000" o "<>Pagado" se convierten en los operadores de comparación habituales de R: >, >=, <, <=, == y !=. Por ejemplo, =CONTAR.SI(D2:D11; ">1000"):

sum(pedidos$importe > 1000, na.rm = TRUE)

[1] 3

El na.rm = TRUE es necesario por el importe que falta, como se explica más abajo. Para contar valores entre dos números, que en Excel requiere CONTAR.SI.CONJUNTO, une dos condiciones con &:

# Pedidos entre 500 y 1000 (ambos incluidos)
sum(pedidos$importe >= 500 & pedidos$importe <= 1000, na.rm = TRUE)

[1] 3

Criterios de texto: coincidencia exacta

Para texto, usa == para “igual a” y != para “distinto de” ("<>Pagado" en Excel):

sum(pedidos$estado != "Pagado")

[1] 3

Para contar las filas que coinciden con cualquiera de varios valores usa %in%, mucho más corto que encadenar |:

sum(pedidos$estado %in% c("Pendiente", "Cancelado"))

[1] 3

Coincidencias parciales y comodines con grepl

En Excel cuentas las celdas que contienen un texto usando comodines, como en =CONTAR.SI(C2:C11; "*Portátil*"). En R el equivalente es la función grepl, que devuelve TRUE cuando el patrón aparece en cualquier parte del texto:

# Productos que contienen "Portátil" (*Portátil*)
sum(grepl("Portátil", pedidos$producto))

[1] 5

El patrón es una expresión regular, así que el resto de comodines se traducen así:

Por ejemplo, los productos que empiezan por “Portátil” y terminan en “Pro”:

sum(grepl("^Portátil.*Pro$", pedidos$producto))

[1] 2

Si el texto que buscas contiene caracteres especiales como ., ( o +, añade fixed = TRUE para que no se interpreten como símbolos de expresión regular.

Criterios con valores faltantes (NA)

Excel ignora las celdas vacías, pero R no: comparar un NA con cualquier cosa devuelve NA, y la suma de un vector con algún NA también es NA:

sum(pedidos$importe > 1000)

[1] NA

Añade na.rm = TRUE para ignorar esas filas. La función is.na te da además la versión en R de CONTAR.BLANCO (COUNTBLANK) y CONTARA (COUNTA):

# CONTAR.SI ignorando los valores faltantes
sum(pedidos$importe > 1000, na.rm = TRUE)

# CONTAR.BLANCO: número de valores faltantes
sum(is.na(pedidos$importe))

# CONTARA: número de valores no faltantes
sum(!is.na(pedidos$importe))

[1] 3
[1] 1
[1] 9

Aprende más en el artículo sobre valores NA en R.

CONTAR.SI para cada categoría

En Excel es habitual escribir una lista de categorías y arrastrar =CONTAR.SI($B$2:$B$11; G2) hacia abajo para contar cada una. En R obtienes todos los recuentos a la vez con la función table:

table(pedidos$region)

 Este Oeste   Sur 
    4     4     2 

Con dplyr, count devuelve los mismos recuentos como data frame y admite varias columnas, lo que te da el recuento de cada combinación (consulta count en dplyr):

library(dplyr)

pedidos %>% count(region, estado)

  region    estado n
1   Este    Pagado 3
2   Este Pendiente 1
3  Oeste Cancelado 1
4  Oeste    Pagado 3
5    Sur    Pagado 1
6    Sur Pendiente 1

Las combinaciones que no aparecen en los datos, como Sur y Cancelado, no se listan. Si las quieres en una tabla cruzada con ceros, usa table(pedidos$region, pedidos$estado) o aprende a crear una tabla dinámica en R.

CONTAR.SI.CONJUNTO: varias condiciones

CONTAR.SI.CONJUNTO (COUNTIFS) cuenta las filas que cumplen todas las condiciones. En R, únelas con & (Y). Por ejemplo, los pedidos pagados de la región Este:

# CONTAR.SI.CONJUNTO(B2:B11; "Este"; E2:E11; "Pagado")
sum(pedidos$region == "Este" & pedidos$estado == "Pagado")

[1] 3

Excel no tiene una función directa para condiciones O (tienes que sumar dos CONTAR.SI con cuidado de no contar una fila dos veces). En R basta con usar |, que cuenta cada fila una sola vez aunque cumpla las dos condiciones:

# Pedidos de la región Este O con un importe mayor que 1000
sum(pedidos$region == "Este" | pedidos$importe > 1000, na.rm = TRUE)

[1] 6

Cuando las condiciones son largas, with te evita repetir pedidos$ en cada columna:

with(pedidos, sum(region == "Oeste" & estado == "Pagado" & importe > 400))

[1] 3

Pon cada grupo de condiciones entre paréntesis cuando mezcles & y |, ya que & se evalúa primero: (region == "Este" | region == "Oeste") & estado == "Pagado".

SUMAR.SI y SUMAR.SI.CONJUNTO

SUMAR.SI (SUMIF) suma los valores de una columna en las filas que cumplen una condición. En R, selecciona los valores con la condición entre corchetes y súmalos:

# SUMAR.SI(E2:E11; "Pagado"; D2:D11)
sum(pedidos$importe[pedidos$estado == "Pagado"], na.rm = TRUE)

[1] 5300

pedidos$importe[condición] se queda solo con los importes en los que la condición es TRUE, y sum los suma. El na.rm = TRUE es obligatorio porque uno de los pedidos pagados no tiene importe.

Para SUMAR.SI.CONJUNTO (SUMIFS), pon varias condiciones dentro de los corchetes:

# SUMAR.SI.CONJUNTO(D2:D11; B2:B11; "Este"; E2:E11; "Pagado")
sum(pedidos$importe[pedidos$region == "Este" & pedidos$estado == "Pagado"], na.rm = TRUE)

[1] 2150

Cualquier criterio de la sección de CONTAR.SI funciona también aquí, comodines incluidos. Por ejemplo, los ingresos de cualquier modelo de portátil:

sum(pedidos$importe[grepl("Portátil", pedidos$producto)], na.rm = TRUE)

[1] 5450

SUMAR.SI y CONTAR.SI por grupos

En lugar de escribir un SUMAR.SI por región, calcula la suma de todas las regiones a la vez. La función tapply aplica una función a una columna dividida por grupos:

tapply(pedidos$importe, pedidos$region, sum, na.rm = TRUE)

 Este Oeste   Sur 
 3050  3350   780 

Para añadir una condición, como “solo pedidos pagados”, filtra primero las filas. La función aggregate tiene un argumento subset para eso:

aggregate(importe ~ region, data = pedidos, FUN = sum, subset = estado == "Pagado")

  region importe
1   Este    2150
2  Oeste    2850
3    Sur     300

Con la interfaz de fórmula, aggregate elimina las filas con NA antes de sumar, así que aquí no necesitas na.rm.

En dplyr, usa summarise con .by. Lo mejor es que puedes calcular varios resúmenes condicionales en la misma llamada, escribiendo la condición dentro de cada función:

pedidos %>%
  summarise(pedidos    = n(),
            n_pagados  = sum(estado == "Pagado"),
            pagado     = sum(importe[estado == "Pagado"], na.rm = TRUE),
            pendiente  = sum(importe[estado == "Pendiente"], na.rm = TRUE),
            .by = region)

  region pedidos n_pagados pagado pendiente
1   Este       4         3   2150       900
2  Oeste       4         3   2850         0
3    Sur       2         1    300       480

Es exactamente un CONTAR.SI.CONJUNTO y varios SUMAR.SI.CONJUNTO uno al lado del otro en una tabla resumen de Excel. Escribir la condición dentro de sum en lugar de filtrar antes mantiene todos los grupos, incluso aquellos en los que ninguna fila cumple la condición (en este caso, la región Oeste tiene 0 pendiente). Si haces filter(estado == "Pendiente") antes de resumir, la región Oeste desaparecería del resultado.

PROMEDIO.SI y PROMEDIO.SI.CONJUNTO

PROMEDIO.SI (AVERAGEIF) funciona igual que SUMAR.SI, cambiando sum por mean:

# PROMEDIO.SI(B2:B11; "Este"; D2:D11)
mean(pedidos$importe[pedidos$region == "Este"], na.rm = TRUE)

# PROMEDIO.SI.CONJUNTO(D2:D11; B2:B11; "Este"; E2:E11; "Pagado")
mean(pedidos$importe[pedidos$region == "Este" & pedidos$estado == "Pagado"], na.rm = TRUE)

[1] 1016.667
[1] 1075

Aquí na.rm = TRUE importa todavía más que con las sumas: la región Este tiene cuatro pedidos pero solo tres importes, así que la media se calcula con tres valores, igual que hace Excel con una celda vacía. También puedes usar median o cualquier otra función del artículo sobre la media en R de la misma forma. Por grupos, cambia sum por mean en cualquiera de los ejemplos anteriores:

pedidos %>%
  summarise(media_pagado = mean(importe[estado == "Pagado"], na.rm = TRUE), .by = region)

  region media_pagado
1   Este         1075
2  Oeste          950
3    Sur          300

MAX.SI.CONJUNTO y MIN.SI.CONJUNTO

El mismo patrón sirve para el mayor y el menor valor que cumplen una condición (MAXIFS y MINIFS):

# MAX.SI.CONJUNTO(D2:D11; C2:C11; "Móvil")
max(pedidos$importe[pedidos$producto == "Móvil"], na.rm = TRUE)

# MIN.SI.CONJUNTO(D2:D11; E2:E11; "Pagado")
min(pedidos$importe[pedidos$estado == "Pagado"], na.rm = TRUE)

[1] 500
[1] 300

Ten cuidado cuando ninguna fila cumple la condición. Excel devuelve 0, mientras que R devuelve -Inf para max (e Inf para min) junto con un aviso:

max(pedidos$importe[pedidos$region == "Norte"], na.rm = TRUE)

[1] -Inf
Warning message:
no non-missing arguments to max; returning -Inf

Si quieres la fila completa con el máximo, y no solo el valor, usa which.max o slice_max de dplyr.

Añadir el resultado como una nueva columna

Una hoja de Excel típica tiene una columna auxiliar con una fórmula como =CONTAR.SI($A$2:$A$11; A2) arrastrada hacia abajo, para que cada fila muestre cuántos pedidos tiene su vendedor. En R base, la función ave hace justo eso: aplica una función por grupos y devuelve un valor para cada fila:

# CONTAR.SI($A$2:$A$11; A2): pedidos del vendedor de cada fila
pedidos$pedidos_vend <- ave(seq_along(pedidos$vendedor), pedidos$vendedor, FUN = length)

# SUMAR.SI($A$2:$A$11; A2; $D$2:$D$11): importe total del vendedor de cada fila
pedidos$total_vend <- ave(pedidos$importe, pedidos$vendedor, FUN = function(x) sum(x, na.rm = TRUE))

pedidos[, c("vendedor", "importe", "pedidos_vend", "total_vend")]

   vendedor importe pedidos_vend total_vend
1       Ana    1200            3       2600
2     Bruno     450            3       2500
3       Ana     900            3       2600
4    Carlos     300            2        780
5     Bruno    1100            3       2500
6       Ana     500            3       2600
7     Diana      NA            2       1300
8    Carlos     480            2        780
9     Bruno     950            3       2500
10    Diana    1300            2       1300

Usamos seq_along(pedidos$vendedor) como primer argumento porque ave necesita un vector numérico, y length simplemente cuenta sus elementos en cada grupo.

En dplyr, usa mutate con .by (o group_by). A diferencia de summarise, mutate mantiene todas las filas:

pedidos %>%
  mutate(pedidos_vend = n(),
         total_vend   = sum(importe, na.rm = TRUE),
         peso         = round(importe / total_vend * 100, 1),
         .by = vendedor) %>%
  select(vendedor, importe, pedidos_vend, total_vend, peso)

   vendedor importe pedidos_vend total_vend  peso
1       Ana    1200            3       2600  46.2
2     Bruno     450            3       2500  18.0
3       Ana     900            3       2600  34.6
4    Carlos     300            2        780  38.5
5     Bruno    1100            3       2500  44.0
6       Ana     500            3       2600  19.2
7     Diana      NA            2       1300    NA
8    Carlos     480            2        780  61.5
9     Bruno     950            3       2500  38.0
10    Diana    1300            2       1300 100.0

La columna peso muestra el porcentaje que representa cada pedido sobre el total de su vendedor, algo que en Excel necesita la columna de SUMAR.SI y además una división.

Recuento acumulado: CONTAR.SI($A$2:A2; A2)

Con un rango medio fijo, =CONTAR.SI($A$2:A2; A2) numera las apariciones de cada valor: 1 la primera vez que aparece un vendedor, 2 la segunda y así sucesivamente. En dplyr esto es row_number por grupos:

pedidos %>%
  mutate(aparicion = row_number(), .by = vendedor) %>%
  select(vendedor, region, aparicion)

   vendedor region aparicion
1       Ana   Este         1
2     Bruno  Oeste         1
3       Ana   Este         2
4    Carlos    Sur         1
5     Bruno  Oeste         2
6       Ana  Oeste         3
7     Diana   Este         1
8    Carlos    Sur         2
9     Bruno   Este         3
10    Diana  Oeste         2

En R base la misma columna es ave(seq_along(pedidos$vendedor), pedidos$vendedor, FUN = seq_along). Es un truco habitual de Excel para marcar duplicados (aparicion > 1). En R tienes funciones específicas para eso, que se explican en eliminar duplicados en R.

Porcentaje de filas que cumplen una condición

En Excel lo calculas como =CONTAR.SI(...)/CONTARA(...). En R, como TRUE vale 1 y FALSE vale 0, la media de una condición es la proporción de filas que la cumplen:

mean(pedidos$estado == "Pagado")

[1] 0.7

El 70% de los pedidos están pagados. También funciona por grupos:

pedidos %>%
  summarise(pct_pagados = mean(estado == "Pagado") * 100, .by = region)

  region pct_pagados
1   Este          75
2  Oeste          75
3    Sur          50

Tabla de equivalencias de Excel a R

Errores comunes

Error: unexpected ‘=’ in “sum(pedidos$estado =”

En Excel un solo = compara valores, pero en R un solo = asigna argumentos. Las comparaciones necesitan un doble signo igual, ==:

sum(pedidos$estado = "Pagado")

Error: unexpected '=' in "sum(pedidos$estado ="

Escribe sum(pedidos$estado == "Pagado") en su lugar.

El resultado es NA

Si sum o mean devuelven NA, la columna de la condición o la de los valores tiene valores faltantes. Añade na.rm = TRUE, como se explica en la sección sobre NA.

El recuento es 0 pero hay filas que coinciden

El texto no coincide exactamente: revisa las mayúsculas y minúsculas, los espacios al principio o al final y las tildes. Imprime unique(pedidos$estado) para ver los valores exactos que hay guardados en la columna, y recuerda que == no admite comodines: pedidos$producto == "Portátil*" busca literalmente el texto “Portátil*“. Usa grepl para las coincidencias parciales.