Skip to content
The Chat Archive
gpt-4o-2024-08-06· OpenAI WildChat-4.8M (AI2)

=SUMIF('K.Chakhrit '!$D$3:$BM$3,Sheet1!C$1,'K.Chakhrit '!$D6:$BM6) i have a1 as…

  1. User

    =SUMIF('K.Chakhrit '!DD3:BMBM3,Sheet1!C1,′K.Chakhrit′!1,'K.Chakhrit '!D6:$BM6) i have a1 as K.Chakhrit, can i replace cell a1 as sheetname

  2. Assistant

    Yes, you can use the INDIRECT function in Excel to reference a sheet name stored in a cell dynamically. Assuming you have the sheet name "K.Chakhrit" stored in cell A1, you can modify your SUMIF formula to dynamically reference the sheet name like this:

    =SUMIF(INDIRECT("'" & $A$1 & "'!$D$3:$BM$3"), Sheet1!C$1, INDIRECT("'" & $A$1 & "'!$D6:$BM6"))
    

    This way, if you change the sheet name in cell A1, the formula will automatically update to reference the new sheet name.

  3. User

    DD3:BMBM3 and D6:D6:BM6 not fix value

  4. Assistant

    If the ranges $D$3:$BM$3 and $D6:$BM6 are not fixed and you need to dynamically adjust them based on changing row or column indices, you'll need to use a combination of the INDIRECT and ADDRESS functions to construct the ranges dynamically.

    Suppose you have cell references that determine the start and end points for your ranges. Let's say:

    • Start row and column indices for the criteria range are defined in cells B1 and C1, respectively.
    • Start row and column indices for the sum range are defined in cells D1 and E1, respectively.
    • You have the sheet name in A1.

    Here's how you might construct the formula:

    =SUMIF(
        INDIRECT("'" & $A$1 & "'!" & ADDRESS($B$1, $C$1) & ":" & ADDRESS($B$1, COLUMNS(INDIRECT("'" & $A$1 & "'!" & $C$1)))),
        Sheet1!C$1,
        INDIRECT("'" & $A$1 & "'!" & ADDRESS($D$1, $E$1) & ":" & ADDRESS(COLUMNS(INDIRECT("'" & $A$1 & "'!" & $E$1)), $D$1))
    )
    

    To use this for flexible address configuration:

    1. $B$1 and $C$1 determine the starting row and column for the criteria range.
    2. $D$1 and $E$1 determine the starting row and column for the sum range.

    Ensure that your formula references the sheet name correctly and dynamically updates based on the row and column indices you provide in other cells (like B1, C1, D1, and E1).

    Please note: The above formula assumes default column references, increases in formula complexity with dynamic column use, and might need customization based on your exact needs in managing starting points and dynamic range length. Adjust COLUMNS logic accordingly if your endpoints for dynamic length are stored/calculated differently.