# Can Vlookup be used in an array function like sumproduct?

#### bbhgroup

##### Board Regular
I have columns of data. I want to be able to walk across the columns horizontally and count the number of items that are members of a group using the vlookup function

Cols:
A B C D E F G H (up to 256..)
10 MM 1-1/2GR 20 MM 3/4 STONE (Row 1)
Targ. Act. Targ. Act. Targ. Act. Targ. Act. (Row 2)
1000 1000 2000 2050 4000 4100 1400 1350 (Row 3)
1200 1200 2100 2000 4000 3900 1500 1400 (Row 4)

etc.

The lookup list is (it has the name "Mat_by_Group"):

10 MM "A"
1-1/2 GR "B"
20 MM "A"
3/4 STONE "C"

Ultimately what I want to do is sum the "Targ." columns of the materials that belong to the same group per row. For the above data, I would get
For Row 1
"A" = 5100 "B" = 2000 "C"= 1400
For Row 2
"A" = 5200 "B" = 1200 "C" = 1500

I've tried to use Sumproduct with three arrays to derive the function:
The first array is the data
The second array is to select every other column
The third array is to select the group

=SUMPRODUCT(OFFSET(\$A:A,0,0,1,256),IF(MOD(COLUMN(\$A:\$IV),2)=1,1,0),IF(VLOOKUP(OFFSET(\$A:A,0,0,1,256),Mat_By_Group,1,FALSE)="A",1,0)

But I am getting back a #Value! error.
Can anyone tell me if I'm on the right track?

Thanks.

Last edited:

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### nbrcrunch

##### Well-known Member
There were a few errors in your post so it took me quite a while to figure out how you were arriving at your totals. "A" for row one, given your sample, equals 5000, not 5100. And it is not even row 1, it is row 2. The header is row 1.

What will make your data considerably easier to manage is to organize it into 3 columns and then just run your sumproduct() summaries on that

Col A: Description followed by your single character (example: 10 MM A, or 20 MM A)
Col.B: Target
Col.C: Act(ual)

In that case, your formula would be

=SUMPRODUCT((RIGHT(A2:A10000)="A")*(C2:C10000))

Last edited:

Replies
1
Views
780
Replies
0
Views
1K
Replies
1
Views
577
Replies
14
Views
742
Replies
0
Views
521

1,195,829
Messages
6,011,838
Members
441,650
Latest member
ceyoung75

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back