For loop in Excel (appling formula to range of cells)

I have a ledger in which I record expenses made by multiple persons (let's call them A, B, C and D).

The structure is - roughly - the following:

COLUMN 1 COLUMN2 COLUMN3 COLUMN4
Date Amount Person Description

I would like to track the expenses of each one of these persons. To do that, I have added a fifth column in which there are 4 cells, one for each person.

Ideally, the formula of each one of these cells should be:

For all the cells ranging from C1 to C100, check the content of the cell. If the content matches the name of the person, then add the amount of the B cell of the same row to this cell.

I am, however, unsure on how to do this and would appreciate suggestions :)

1 Answer

The formula for GEORGE (in the cell E4) may be:

=SUMIFS(B2:B100, C2:C100, "GEORGE")

For other people it will be the same, only change the last parameter (the name):

=SUMIFS(B2:B100, C2:C100, "DONNIE")
=SUMIFS(B2:B100, C2:C100, "GEORGE")
=SUMIFS(B2:B100, C2:C100, "BARRY")

But the much better approach is to use absolute references for ranges and a relative reference to the cell with a name - to be able to copy the formula from the cell E4 to other cells. So write this formula into the cell E4:

=SUMIFS($B$2:$B$100, $C$2:$C$100, E3)

and then simply copy it to others appropriate cells (i. e. for DONNIE, GEORGE, etc.).