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.
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: