DESREF devuelve una referencia a un rango desplazado un número de filas y columnas respecto a otro:
=DESREF(ref; filas; columnas; [alto]; [ancho])
- filas > 0: hacia abajo, < 0: hacia arriba; columnas > 0: a la derecha, < 0: a la izquierda;
- alto/ancho: tamaño del rango devuelto (por defecto, el de la referencia).
Ejemplo del manual: ventas por departamento
Los departamentos están en los encabezados de columna, así que SUMAR.SI.CONJUNTO no es práctico. Tomamos la primera columna de datos y la desplazamos a la derecha según la posición del departamento que da COINCIDIR:
=SUMA(DESREF($B$2:$B$7;0;COINCIDIR(G2;$B$1:$E$1;0)-1))
También con una condición por filas, mediante SUMAPRODUCTO:
=SUMAPRODUCTO(DESREF($B$2:$B$7;0;COINCIDIR(G2;$B$1:$E$1;0)-1)*($A$2:$A$7=I$1))