Sum a Row based on found value in a column

[Eriktoinfinity]

I need to find a specific value in a column and then sum a specific range in that row belonging to the value, but the column location changes whenever someone inputs their data for their specific project, even though the value doesn't. Is this possible in a formula or would that require a macro?

Example: my value is the category number and I need to find category 5 (row 6 for store 1) and sum up the sales in the 3rd, 4th and 5th columns of that row. The location of category 5 changes to row 5 for store 2.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Category[/TD]
[TD]Jan Sales[/TD]
[TD]Feb Sales[/TD]
[TD]Mar Sales[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4[/TD]
[TD]4000[/TD]
[TD]4000[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD]5000[/TD]
[TD]5000[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]6[/TD]
[TD]6000[/TD]
[TD]6000[/TD]
[TD]6000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store[/TD]
[TD]Category[/TD]
[TD]Jan Sales[/TD]
[TD]Feb Sales[/TD]
[TD]Mar Sales[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[TD]4000[/TD]
[TD]4000[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5[/TD]
[TD]5000[/TD]
[TD]5000[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]7[/TD]
[TD]7000[/TD]
[TD]7000[/TD]
[TD]7000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8[/TD]
[TD]8000[/TD]
[TD]8000[/TD]
[TD]8000[/TD]
[/TR]
</tbody>[/TABLE]

 

[bbott]

Assuming your table starts in A1, try something like:

=SUM(INDEX(B1:E7,MATCH(5,B1:B7,0),2):INDEX(B1:E7,MATCH(5,B1:B7,0),4))

Ranges would need to be adjusted for your actual data.

 

[L. Howard]

Hi Erik...

Maybe something like this for a VBA solution.

Using input boxes to identify the store/category, with results posted in columns K & L.

With a tiny bit of tweaking, we can go to entering the store in G1, category in G2 and a message box for the total.

Howard

Option Explicit

Sub My_Store_No_Sum()
Dim i As Long, ii As Long
Dim c As Range, sSum As Range
Dim OneRng As Range

Set OneRng = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)

i = Application.InputBox("Enter Store No.", _
    "Col A Finder", , , , , , 2)
ii = Application.InputBox("Enter Category.", _
    "Col A Finder", , , , , , 2)

'i = Range("G1")
'ii = Range("G2")

For Each c In OneRng
  If c = i And c.Offset(, 1) = ii Then
    Set sSum = c.Offset(, 2).Resize(1, 3)
    'MsgBox Application.WorksheetFunction.Sum(sSum)
  Range("K" & Rows.Count).End(xlUp)(2) = Application.WorksheetFunction.Sum(sSum)
  Range("L" & Rows.Count).End(xlUp)(2) = "Store - " & i & " Category - " & ii
  End If
Next

'[G1].Activate
End Sub

 

[Eriktoinfinity]

Thanks, bbott! This is exactly what I needed. It didn't work until I changed the "2" and "4" in the index formulas to "1" and "3" as the index function only wanted the column number within the array, not within the worksheet. Thanks again!!!

 

[Eriktoinfinity]

Thank you, L. Howard. I needed a formula over VBA, but I'll certainly save this as it might come in handy for something else I'm working on! Thanks again!