CODEWITHSUNDEEP
excelexcel-formula
ahgert
ahgert

Reputation: 75

Sum if person is in the same row of number

I have a table with numbers in column A. Columns B to D are filled with names (Person X, Person Y, etc), that don't repeat themselves within the same row:

A       B     C      D
----------------------
100  |  X     Z      B
80   |  Y     X      K
60   |  Z     A      Y

I want to use something like a SUMIF to sum all the numbers for the names I have. For example, for X we'd get 100+80=180, and for Y 80+60=140.

Upvotes: 1

Views: 246

Answers (3)

singh30
singh30

Reputation: 1503

You can create hashmap. While traversing if name already exist then add value to it otherwise create a new entry with that name and add value.

Upvotes: 0

A.S.H
A.S.H

Reputation: 29352

=SUMPRODUCT($A$1:$A$3*($B$1:$D$3="X"))

You can replace "X" with any cell reference. The formula exploits the stated fact that X, Y etc "don't repeat themselves within the same row"

You could also use full columns to avoid setting the row numbers... but it will be a little slower:

=SUMPRODUCT(A:A*(B:D="X"))

Upvotes: 2

Gowtham Shiva
Gowtham Shiva

Reputation: 3875

Approach 1: If you have just three columns B:D, use 3 SUMIFs like below, (Where column F is your sum criteria)

=SUMIF(B:B,F1,A:A)+SUMIF(C:C,F1,A:A)+SUMIF(D:D,F1,A:A)

enter image description here

Approach 2: If there are more columns, use the below formula in column E - helper column,

=B1&C1&D1

This formula concatenates the columns to a single string. Then enter the formula to get the sum like below in column H,

=SUMIF(E:E,"*"&G1&"*",A:A)

enter image description here

Upvotes: 2

Related Questions