Formula Help

[excel_novice10]

Hello,

I am first time poster looking for help with a formula which uses a sumproduct and vlookups:

=SUMPRODUCT((VLOOKUP(__,__:__,{2,3,4,...},FALSE)>0)*VLOOKUP(__,__:__,{2,3,4,...},FALSE))


Here is a simple example of the formula in action:

https://i.imgur.com/i49xcNl.png


While the formula works with small datasets, Excel's formula character limit prevents it from being applied to larger data sets, as there are too many index column numbers. How should I work around the character limit if I have too many columns (over 1000)? What alternative formula would work more efficiently and accomplish the same objective? I don't have experience using Excel VBA, so any solutions in vanilla Excel would be helpful.


Thanks for your help!

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD]

Person A​

[/TD]
[TD]

Person B​

[/TD]
[TD]

Person C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

x​

[/TD]
[TD]

20​

[/TD]
[TD]

0​

[/TD]
[TD]

15​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

y​

[/TD]
[TD]

30​

[/TD]
[TD]

5​

[/TD]
[TD]

75​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

z​

[/TD]
[TD]

15​

[/TD]
[TD]

45​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6
​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7
​

[/TD]
[TD][/TD]
[TD]

x​

[/TD]
[TD]

y​

[/TD]
[TD]

z​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

x​

[/TD]
[TD]

35​

[/TD]
[TD]

105​

[/TD]
[TD]

15​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

y​

[/TD]
[TD]

35​

[/TD]
[TD]

110​

[/TD]
[TD]

60​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

z​

[/TD]
[TD]

20​

[/TD]
[TD]

35​

[/TD]
[TD]

60​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in B8 copied across and down
=SUMIF(INDEX($B$2:$D$4,MATCH($A8,$A$2:$A$4,0),0),">0",INDEX($B$2:$D$4,MATCH(B$7,$A$2:$A$4,0),0))

Hope this helps

M.

 

[excel_novice10]

Hi M,

Thanks for your help, the formula you provided is the perfect solution!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.