Kathy tiene una hoja de trabajo que incluye información para todas las piezas en su almacén. En esta hoja, los números de pieza se muestran en la columna A usando el formato 12345 XXX, donde XXX representa un código de ubicación. Esto significa que podría tener varias entradas en la hoja de trabajo para los mismos números de pieza, pero cada entrada representa una ubicación diferente para esa pieza. Kathy necesita una fórmula que sume los valores asociados con cada número de pieza, independientemente de su código de ubicación. Por lo tanto, necesita una forma de sumar la columna de cantidad relacionada con las partes 12345 ABC, 12345 DEF, 12345 GHI, etc.

Necesita una forma de hacer esto sin dividir el código de ubicación en una columna diferente.

Hay más de una forma de obtener la respuesta deseada. Por el bien de los ejemplos de este consejo, suponga que los números de pieza están en la columna A (como indicó Kathy) y que las cantidades de cada pieza están en la columna B. Son estas cantidades las que deben sumarse, basándose solo en parte de lo que hay en cada celda en la columna A. Además, puede poner el número de pieza (menos el código de ubicación) deseado en la celda D2.

La primera solución potencial es usar la función SUMPRODUCTO, de esta manera:

=SUMPRODUCT(--(VALUE(LEFT(A2:A49,FIND(" ",A2:A49)))=D2),B2:B49)

Esta fórmula comprueba los valores en el rango A2: A49. Debe asegurarse de que este rango refleje el rango de sus datos reales. Si generaliza la fórmula para que mire todas las columnas A y B (como en A: A y B: B), obtendrá un error #VALUE, ya que intenta aplicar la fórmula a celdas vacías en las columnas. .

Puede obtener un resultado similar utilizando una fórmula de matriz como esta:

=SUM(B:B*(LEFT(A2:A49,5)=TEXT(D2,"@")))

Recuerde, nuevamente, que esta es una fórmula de matriz, por lo que debe ingresar presionando Shift + Ctrl + Enter. Tenga en cuenta también que esta fórmula convierte el valor en D2 en texto para la comparación. Esto no se hizo en la fórmula anterior porque allí la subcadena seleccionada de la columna A se convirtió en un valor numérico usando la función VALOR.

También puede utilizar la función DSUM para construir una fórmula de trabajo. Supongamos que los números de pieza (columna A) tienen un encabezado de columna en la celda A1.

Copie este encabezado de columna (como «Part Num») en otra celda de la hoja de trabajo, como la celda D1. En la celda D2, ingrese el número de pieza, sin su código de ubicación, seguido de un asterisco. Por ejemplo, puede ingresar «12345 *» (sin las comillas) en la celda D2. Con esa especificación configurada, puede usar esta fórmula:

=DSUM($A$1:$B$49,$B$1,D1:D2)

Esta fórmula usa la especificación en la celda D2 (los caracteres 12345 seguidos de cualquier cosa) como una clave a la que se deben sumar los valores de la columna B.

Finalmente, si tuviera la misma especificación en la celda D2 que usó con el enfoque DSUM, podría usar una función SUMIF muy simple, de esta manera:

=SUMIF(A:A,D2,B:B)

Tenga en cuenta que este enfoque le permite utilizar los rangos de columna completos (A: A y B: B) en la fórmula.

Si sus números de pieza (en la columna A) no son tan consistentes en su formato como le gustaría, entonces es mejor que cree una función definida por el usuario para encontrar sus cantidades. Por ejemplo, si sus números de pieza no siempre tienen la misma longitud o si los números de pieza pueden contener tanto dígitos como letras o guiones, entonces una UDF es el camino a seguir. El siguiente ejemplo funciona muy bien; Teclea la presencia de al menos un espacio en el valor. (Kathy indicó que un espacio separaba el número de pieza del código de ubicación).

Function AddPrtQty(ByVal Parts As Range, PartsQty As Range, _   FindPart As Variant) As Long     Dim Pos As Integer     Dim Pos2 As Integer     Dim i As Long     Dim tmp As String     Dim tmpSum As Long     Dim PC As Long

PC = Parts.Count     If PartsQty.Count <> PC Then         MsgBox "Parts and PartsQty must be the same length", vbCritical         Exit Function     End If

For i = 1 To PC         Pos = InStr(1, Parts(i), " ")

Pos2 = InStr(Pos + 1, Parts(i), " ")



If Pos2 > Pos And Len(Parts(i)) > Pos + 1 Then             tmp = CStr(Trim(Left(Parts(i), Pos2 - 1)))

ElseIf Pos > 0 And Len(Parts(i)) > 0 Then             tmp = CStr(Trim(Left(Parts(i), Pos - 1)))

End If

If CStr(Trim(tmp)) = CStr(Trim(FindPart)) Then             tmpSum = tmpSum + PartStock(i)

End If     Next i

AddPrtQty = tmpSum End Function

Para usar la función, en su hoja de trabajo llámela usando dos rangos y el número de pieza que desea:

=AddPrtQty(A2:A49,B2:B49,"GB7-QWY2")

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.

link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (11469) se aplica a Microsoft Excel 2007, 2010, 2013 y 2016.

Puede encontrar una versión de este consejo para la interfaz de menú anterior de Excel aquí:

link: / excel-Summing_Based_on_Part_of_the_Information_in_a_Cell [Suma basada en parte de la información en una celda].