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

microsoft excelmicrosoft-excel-2010worksheet-function

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 🙂

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

Related Solutions

Excel – Is it possible to reference two cells in Excel to find a third cell

I tried to make it worked under the assumption that:

"Attached Components" is always in the same column.
You are actually trying to lookup the description for each "Material #".

And I will use this sheet to work on:

Example

This might not be exactly what you need but I can try do improve my answer with your remarks on it.

By reused your formula to identify where is "Attached Components" in the column and then added 2 it gives the relative row where the material description starts:

=MATCH("Attached Components",B1:B32,0)+2

The result is in the example "7".

After you need to identify the last row where the descriptions are. To search in the correct range the formula needs to change depending on which row "Attached Components" is located. The combination of MATCH, ADDRESS, CONCATENATE will recreate the range.

MATCH gives you the relative row, ADDRESS transform a row number and column number in a string with the cell name ( ADDRESS(1,1)="$A$1" ), CONCATENATE will put the strings together in order to create a range.

CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))

This return a string like "$C$7:$C$25". So it covers the Description column and start at the row where you have your values to 18 rows lower. To cover more or less rows just change the "+20" in the formula into the appropriate value.

Finding the last row is just a matter of finding the first empty cell with IF and MIN.

{=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
     )
)-1}

This formula is an array formula. That is why it has brackets around it (do not type the brackets, they appear when you enter the formula and then press Ctrl+Shift+Enter)

INDIRECT transform the string that we built into a cell reference. ROW gives the row number as a result. MIN will take the smallest value in the range returned. The "-1" at the end is to have the row number of the last description and not the first blank row.

In the example this formula return "9".

Now we have the row number of the first description and the last description, 7 to 9. We can combine those numbers the way we want using ADDRESS, CONCATENATE and INDIRECT to do any operation you need. But this time you have a specific cell reference to work with.

For example a Material # lookup:

VLOOKUP example

In this last example the cells contains

E2:

=MATCH("Attached Components",B1:B32,0)+2

F2 (To enter using Ctrl+Shift+Enter):

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
    )
)-1

F7:

=VLOOKUP(E7,INDIRECT(CONCATENATE(ADDRESS(E2,1),":",ADDRESS(F2,3))),3,FALSE)

This way when you type a Material # in cell E7 it display the description in cell F7.

EDIT:

Following the comments the solution can be worked out that way:

Using a more complicated example:

Complicated example

The row matching is just a cascade of 2 MATCH function. Using the first MATCH function to find the Part # and then the second one to find the section of interest:

The 2 matching

F3: a string of the part you are looking for

F4: the formula to look for the "Part #" in the first column.

=MATCH($F$3,A1:A32,0)

F6: the name of the section you are looking for

F7: the formula to look for the section in the part identified before. The match is done in a range that starts at the row of the "Part #" (stored in cell F4). The range is built using the same kind of formula that use INDIRECT, CONCATENATE, ADDRESS. Then the relative row returned by MATCH is offsetted by F4-1 to have the absolute row number.

=MATCH($F$6,INDIRECT(CONCATENATE(ADDRESS(F4,2,1),":",ADDRESS(F4+20,2,1))),0)+F4-1

Now, to identify the first and last rows of the description we can reuse the same formulas as before:

first and last row for descriptions

F9: adding 2 to the row number of the "Attached Components" row to get the first description row.

=F7+2

F10: looking for the first blank row in the description range (starting at the row stored in F9). This is an array formula that needs to be entered using CTRL+SHIFT+ENTER

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3))))
     )
)-1

Then to display the description we can use INDIRECT and a index column:

Display matrix

F15:

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,1)),"")

G15:

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,3)),"")

Those formulas will display the Material # and the description for a row identified by an index in the E column. The IF statement is to make sure we do not display the rows that are below the last rows. In the example it display only 5 rows but you can just copy this formula by dragging down the first row and adding new indexes to have more.

Best Answer

Related Solutions

Excel – Is it possible to reference two cells in Excel to find a third cell

Related Question