1

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.

Here's an image to better clarify things

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

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.).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy