Sumproduct + vlookup

[reneev]

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.

 

[Eric W]

Re: Help with SUMPRODUCT + VLOOKUP

I tried your formula, and I could not get it to work. And putting {3,4,5,6} into a cell to populate an array does not work in Excel. However, there are options. If the columns you want to sum are contiguous, you can try something like this:

	A	B	C	D	E	F	G	H	I	J
5			Value	Num Columns	Sum
6			b	4	10
7					#REF!
8
9
10
11
12		a
13		b	1	2	3	4	5	6	7	8
14		c
15

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula E6 =SUM(OFFSET($C$12,MATCH(C6,$B$12:$B$20,0)-1,0,1,$D$6)) E7 =SUMPRODUCT(VLOOKUP(C6,B12:F18,{3,4,5,6},0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[reneev]

Re: Help with SUMPRODUCT + VLOOKUP

Thank you, I will give that a try.

 

[Aladin Akyurek]

Re: Help with SUMPRODUCT + VLOOKUP

Let A1 house the number of quarters. 1 means 3; 2 >> 3 and 4; 3 >> 3, 4, and 5...

Control+shift+enter, not just enter:

=SUM(VLOOKUP(C6,$B$12:$G$18,ROW(INDIRECT("1:"&A$1))+2,0))