Search code examples
excelvbaexcel-formulaexcel-2007

How to sum values in the same column based on other values in the same row

I have three columns in an Excel spreadsheet: name, type and amount. (I have more, but the others aren't relevant to this problem). Each row contains the name of a playing card, its type and the number of copies included in the deck as such:

Table example image

In other cells outside this table, I have the total amount of cards. For example, the total amount of cards with the type "ranged".

=COUNTIF(D2:D70; "*ranged*")

What this shows me is only how many distinct cards there are with the type "ranged," but not how many cards with "ranged" would actually be included in the deck.

What I would like is a way to count the number of "ranged" cards. Meaning, the sum of the "amount" of all "ranged" cards.

In a for loop, this would be: If Type in row 2 = "ranged," add Amount in row 2 to the total, then repeat this operation for all rows.

I already did this with VBA, however, I have to update it manually. Also, I cannot have multiple different values in the "type" column, because x has to be 100% equal to the content of the cell in order to register it.

Sub Cardtypetotal()
 result = 0
 x = InputBox("SortBy", "Enter_Info")
  For i = 2 To 63
   If Cells(i, 3).value = x Then
    result = result + Cells(i, 5).value
   End If
  Next
 ActiveCell.value = result
End Sub

Is there a way to do this with excel formulas? Thank you.

I tried using:

=SUMIF(E2:E63; D2:D63 = "*ranged*")

But obviously, this counts nothing, as I need to compare individual cells, not arrays.

Solution

  • Most of your problems with the function could be solved by looking at the documentation of the SUMIFS function here.

    Your sumifs statement above is using the wrong syntax. You should be using commas, not semicolons, your equal sign sould also be a comma, and you should not use an asterisk infront of "ranged". Then your formula would work just fine.

    If the above image showed Columns A, B and C, it would be someting like this 

     =SUMIFS(C:C,B:B,"ranged")