CODEWITHSUNDEEP
excelexcel-formula
Rob
Rob

Reputation: 199

Sum rows in Excel with same text value

I am trying to sum the rows in Excel that have the same text value in 2 different columns. I saw the post here Group by Sum in Excel about summing rows with the same text value in one column. Here is my table:

A     B     C
Item Region quantity_sold
A     South    1
A     South    4
A     North    7
B     South    5
B     South    9
D     South    3
C     South    6
C     South    4
C     North    8

I think I need to use Sumifs instead of Sumif. I would like the result to be:

Item Region quantity_sold
A     South    
A     South     5
A     North     7
B     South    
B     South    14
D     South     3
C     South    
C     South    10
C     North     8

I tried =IF(A2=A1,"",SUMIFS(A:A,A2,B:B,B2,C:C)) but I got: #VALUE!

Upvotes: 3

Views: 2181

Answers (3)

manu
manu

Reputation: 942

You can do it using an UDF:

=SumQuatitySold(2;A2;B2;C:C)

Where:

  • The First entry is the start row, in this case is 2
  • The second one is your item (select the cell)
  • The third one is the region (select the cell)
  • Last one select the column of the quantity_sold

Exemple

You just need to enter this code in a module:

Function SumQuatitySold(StartRow As Long, Item As Range, region As Range, Quantity_Sold As Range) As Long

Dim ws As Worksheet
Dim i As Long, j As Long

Set ws = ThisWorkbook.ActiveSheet

With ws
    i = Item.Row
    j = StartRow
    Do
            If .Cells(i, Item.Column) = .Cells(j, Item.Column) And .Cells(i, region.Column) = .Cells(j, region.Column) And i <> j Then
                    If quantitysold = 0 Then
                        quantitysold = .Cells(j, Quantity_Sold.Column)
                    Else
                        quantitysold = quantitysold + .Cells(j, Quantity_Sold.Column)
                    End If
            End If
    j = j + 1
    Loop Until IsEmpty(.Cells(j, Quantity_Sold.Column))

 End With

SumQuatitySold = quantitysold + ws.Cells(Item.Row, Quantity_Sold.Column)

End Function

Upvotes: 0

Lewis Fogden
Lewis Fogden

Reputation: 524

An alternative I find works well is to add a column which contains both columns you wish to group as a single value. For example set up col D with this:

=A1&"_"&B1
result is A_South etc

You can then use normal SUMIF, using this column as criteria.

Main benefits are separation of formulae, and you can extend easily to further grouping variables.

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152450

Try this formula:

=IF(OR(B2<>B3,A2<>A3),SUMIFS($C$2:$C$10,$A$2:$A$10,A2,$B$2:$B$10,B2),"")

enter image description here

On caveat, the columns must be sorted. This will not work if the columns are not sorted.

Upvotes: 1

Related Questions