How to preserve relative references using a vertical copy where the formula needs to move horizontally

[polarisking]

I've included a file.

Col A and Col B are sets of numbers. Col C denotes which rows to sum using SUMIF.

The first formula (cell F3) is =SUMIF($C$3:$C$25,"X",$A$3:$A$25)

I want to be able to drag/copy this formula down and have the third argument point to $B$3:$B$25. Using a drag or copy transpose, it's (cell F4) still pointing to $A$3:$A$25.

What's the technique for getting my desired result? Is there one?

Thank you in advance for your help.

 

[adityakapoor]

I am a little confused about what you are trying to achieve. Which of the following is your intention:
1. if there's an X in column C, return the sum of the entire range (A3:A25) in column A, otherwise the sum of entire the range (B3:B25) in column B
2. if there's an X in column C, return the sum of the range up to that row in column A (A3:A_), otherwise the sum of the range in B up to that row (B3:B_)
3. if there's an X in column C, return the sum of the range from that row onwards in A (A_:A25), otherwise the sum of the entire remaining range in column B (B_:B25)

or, are you just trying to get two values which are the sums of column A and Column B in rows where the X is marked?

Firstly using an absolute reference with the $ signs like $A$3:$A:$25, will not permit the range to change when you copy / drag the formula across. This is the opposite of what its meant to do.
You would be better of using A$3:A$25 - this would allow you to drag horizontally to the next cell = F4 and it will automatically take the range as B$3:B$25

when using cell referencing, $A$3 - the first $ sign fixes the absolute column reference to column A and the second $ sign fixes the absolute row reference to row 3. You would like to keep the range as row 3 to row 25 so the second $ sign is important to retain. The first one isn't since you want the column to change. But this will apply only when you drag the formula horizontally across to the next column.

If you only need two formulae you can just type it in and not worry about it!
If there's alot of columns, you can consider using tables with filtering / SUMIF / even consider making a horizontal selection and transposing it to a vertical column if required.

If you can explain a little more about what you are desiring, maybe someone can help more clearly.

 

[Peter Bartholomew]

I think you are expecting too much of drag/fill and relative referencing. All it offers is a cheap and nasty method of scanning arrays without needing to understand arrays. Since the value columns you are drawing data from left to right, the references need to be column relative, e.g.

= SUMIF($C$3:$C$25,"X",A$3:A$25)

An alternative is to scan the indices {1;2} vertically but allow OFFSET to run horizontally, As an array this reads

= SUMIF(Select, "X", OFFSET(Select, 0, {1;2}-3))

With the latest versions of 365, other solutions are available which are barely recognisable as spreadsheet formulas

= LET(
      SUMIFλ, LAMBDA(NbrCol, SUMIF(Select, "X", NbrCol)),
      totals, BYCOL(Nbrs, SUMIFλ),
      TRANSPOSE(totals))

 

[Peter Bartholomew]

The file

 

[p45cal]

Perhaps a quick and dirty fix; in any cell in row 3:
=SUMIF($C$3:$C$25,"X",OFFSET(A$3:A$25,0,ROW()-3))
copy down.

 

[polarisking]

Thanks to all for your responses. I'm sorry it took me so long to get back to you.

 

[polarisking]

Here's the solution I came up with.
The formulae in H13 thru H17 accomplish what I needed to do. I put A3:E25 in a named range - dataNbrs. Using the INDEX function, by column, and the Row() function to move the columns left to right, I'm able to get what I need. Hope this is helpful to some.

The implementation of Col F to dynamically determine which rows will be summed is unnecessary for this example, but it's what I was using for that piece of analysis.