=SUMIF('K.Chakhrit '!$D$3:$BM$3,Sheet1!C$1,'K.Chakhrit '!$D6:$BM6) i have a1 as…
- User
=SUMIF('K.Chakhrit '!3:3,Sheet1!CD6:$BM6) i have a1 as K.Chakhrit, can i replace cell a1 as sheetname
- Assistant
Yes, you can use the
INDIRECTfunction in Excel to reference a sheet name stored in a cell dynamically. Assuming you have the sheet name "K.Chakhrit" stored in cellA1, you can modify yourSUMIFformula 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. - User
3:3 and BM6 not fix value
- Assistant
If the ranges
$D$3:$BM$3and$D6:$BM6are not fixed and you need to dynamically adjust them based on changing row or column indices, you'll need to use a combination of theINDIRECTandADDRESSfunctions 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
B1andC1, respectively. - Start row and column indices for the sum range are defined in cells
D1andE1, 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:
$B$1and$C$1determine the starting row and column for the criteria range.$D$1and$E$1determine 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, andE1).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
COLUMNSlogic accordingly if your endpoints for dynamic length are stored/calculated differently. - Start row and column indices for the criteria range are defined in cells