Sumproduct with skip column

[moka123]

Hi

I need help to do sumproduct with skip column :

cell b2 =SUMPRODUCT(('Blade Ring'!$A$4:$A$101="gbr")*('Blade Ring'!D$4:D$101=$A2)*'Blade Ring'!E$4:E$101)
and cell c2 should become =SUMPRODUCT(('Blade Ring'!$A$4:$A$101="gbr")*('Blade Ring'!G$4:G$101=$A2)*'Blade Ring'!H$4:H$101)

now, when i drag from b2 to c2 it become =SUMPRODUCT(('Blade Ring'!$A$4:$A$101="gbr")*('Blade Ring'!E$4:E$101=$A2)*'Blade Ring'!F$4:F$101)

Can anyone help with skip 2 column?

Cheers
Elang

 

[Marcelo Branco]

Welcome to Mr Excel

Try

B2 copied across
=SUMPRODUCT(('Blade Ring'!$A$4:$A$101="gbr")*(INDEX('Blade Ring'!$D$4:$AZ$101,0,3*COLUMNS($B2:B2)-2)=$A2)*INDEX('Blade Ring'!$E$4:$AZ$101,0,3*COLUMNS($B2:B2)-2))

Hope this helps

M.

 

[Aladin Akyurek]

In B2 enter and copy across:

=SUMPRODUCT(('Blade Ring'!$A$4:$A$101="gbr")*(INDEX('Blade Ring'!$D$4:$S$101,0,3*(COLUMNS($B$2:B2)-1)+1)=$A2)*(INDEX('Blade Ring'!$E$4:$T$101,0,3*(COLUMNS($B$2:B2)-1)+1)))

 

[moka123]

Thanks all for the solution. It works