reneev
Board Regular
- Joined
- Apr 26, 2017
- Messages
- 53
I have just learned how to use the SUMPRODCT with VLOOKUP in order to sum an array of columns inside a vlookup as shown below.
" =SUMPRODUCT(VLOOKUP(C6,B12:F18,{3,4,5,6},0))"
Now, this particular formula is basically calculating year-to-date totals where column 3 is 1st qtr, column 4 is 2nd qtr etc... HOWEVER, this formula has to be updated each quarter to add the next quarter to the mix. So for example, this month I'm only looking at 1st quarter so would only bring in column 3. But next quarter, when I update the formula, I need to add column 4 so I would now have {3,4}.
The problem is that I have to copy this formula through a lot non-consecutive rows so it's kind of a pain to update. What would be great is to replace the {3,4,5,6} with a reference to a single cell that contains the columns I want to include. So in my mind the formula would look like this:
" =SUMPRODUCT(VLOOKUP(C6,B12:F18,{$A$1},0))" Where A1 contains 3,4,5,6.
Does that make sense? Just trying to automate as much as possible and I don't want to create another column in my data set which sums only the quarters I'm looking for.
" =SUMPRODUCT(VLOOKUP(C6,B12:F18,{3,4,5,6},0))"
Now, this particular formula is basically calculating year-to-date totals where column 3 is 1st qtr, column 4 is 2nd qtr etc... HOWEVER, this formula has to be updated each quarter to add the next quarter to the mix. So for example, this month I'm only looking at 1st quarter so would only bring in column 3. But next quarter, when I update the formula, I need to add column 4 so I would now have {3,4}.
The problem is that I have to copy this formula through a lot non-consecutive rows so it's kind of a pain to update. What would be great is to replace the {3,4,5,6} with a reference to a single cell that contains the columns I want to include. So in my mind the formula would look like this:
" =SUMPRODUCT(VLOOKUP(C6,B12:F18,{$A$1},0))" Where A1 contains 3,4,5,6.
Does that make sense? Just trying to automate as much as possible and I don't want to create another column in my data set which sums only the quarters I'm looking for.
