CODEWITHSUNDEEP
excelexcel-formulasumifs
GentilmanMyles
GentilmanMyles

Reputation: 29

sumif range and criteria switching

So I have a table of number of fiscal weeks in a year, and to calculate depreciation I'm trying to sum the number of weeks in the total life of the capital investment. So the first argument is the range, the second argument pulls the year from another cell, and then adds the expected life in years of the device. The third argument is the intended sum range.

SUMIF(YearWeeks[Year],"<="&LEFT(AS$14,4)+$H20,YearWeeks[Weeks])

This works fine. Pulls in the right numbers no problem. However, whenever I try and drag this across other cells so that it can be used throughout my spreadsheet to caculate depreciation, it immediately switches the value for year and the value for weeks and becomes:

SUMIF(YearWeeks[Weeks],"<="&LEFT(AU$14,4)+$H20,YearWeeks[Year])

I cannot figure out why it is doing this. It is probably something simple, but it is completely evading me. Any help would be greatly appreciated.

Year Weeks 2015 52 2016 53 2017 52 2018 52 2019 52 2020 52 2021 53 2022 52 2023 52 2024 52 2025 52 2026 52 2027 53 2028 52 2029 52 2030 52

Life

5
5
5
5
5
5
5
5
8
8
8
8
8
8

201504  201505  201506  201507  201508  201509  201510  201511  201512  201601  201602  201603

Upvotes: 0

Views: 174

Answers (1)

trincot
trincot

Reputation: 350310

Instead of dragging the formula to the right, copy the cell and paste it in the cell at the right of it, etc... :

This will keep the column references unchanged (no change from YearWeeks[Weeks] to YearWeeks[Year]).

As specified on Microsoft's support website:

Moving, copying, and filling structured references

All structured references remain the same when you copy or move a formula that uses a structured reference.

When you fill a formula, fully qualified structured references can adjust the column specifiers like a series as summarized in the following table.

NB: YearWeeks[Weeks] is a structured reference, and fill is what many would call drag in this context.

Upvotes: 1

Related Questions