Search code examples
vbaexcelsumsumifs

Excel: Sum up elements of a row if a certain element of a row is a particular name

My problem is depicted in this picture: enter image description here

I want to sum up all ones of Alice, Bob and Carol – but I don’t know how to do this without a macro. Is it even possible to do this with a simple Excel formula (such as, e.g., =SUMIF()) so that the result is

  • Alice: 8
  • Bob: 8
  • Carol: 12

?

Solution

  • You can use SUMPRODUCT to sum a range based on criteria like this:

    =SUMPRODUCT(--(B2:G8)*(A2:A8="Alice"))

    (A2:A8="Alice") will return 1 or 0 based on whether the criteria isTrue or False. Then, when multiplied by the range (B2:G8) only the values which meet the criteria will be returned.