CODEWITHSUNDEEP
excelexcel-formula
user101998
user101998

Reputation: 251

output a range and use it in a sumproduct

So, I am trying to do the following:

Enter three, then let excel use sumproduct on those three columns

For example, if I enter the number 1,2,3, then the formula is like sumproduct(column A,column B, column C)

I have tried vlookup, if-else and some random junk, but could not find the solution.

Sample data:

input 56 

1      2       3        4       5
1000    1000    1000    1000    1000
1000    1000    1000    1000    1000
1000    1000    1000    1000    1000
1000    1000    1000    1000    1000
1000    1000    1000    1000    1000
1000    1000    1000    1000    1000
1000    1000    1000    1000    1000
1000    1000    1000    1000    1000
1000    1000    1000    1000    1000
1000    1000    1000    1000    1000
1000    1000    1000    1000    1000
1000    1000    1000    1000    1000

Upvotes: 0

Views: 59

Answers (1)

Scott Craner
Scott Craner

Reputation: 152605

You could do something like this formula:

=INDEX($A:$E,ROW(),MATCH($I$1,$1:$1,0))

Where I1 was the input.

INDEX allows for three parameters. INDEX(range,row,column)

So we set the range for all of columns A through E. Then the ROW() returns the row number in which the formula is.

The MATCH() finds the column that has the input from I1 in row 1 and returns that relative column number.

enter image description here

Just realized you wanted the sum of the numbers in that column. Use this to do that:

=SUM(INDEX(A:E,2,MATCH($I$1,1:1,0)):INDEX(A:E,MATCH(1E+99,INDEX(A:E,0,MATCH($I$1,1:1,0))),MATCH($I$1,1:1,0)))

It will dynamically find the range of number from row 2 to the end of the numbers in the column that is returned to have the criteria in I1 in row 1:1.

Upvotes: 1

Related Questions