Need help with sumproduct and dynamic columns

[wayward]

Hello All, I am currently working on a workbook that contains
sumproduct function, the problem I have is that the data worksheet tha
the sum product is looking at can have different columns at variou
times.

The equation I have so far is:

=SUMPRODUCT(((ISNUMBER(SEARCH("*Dist. Comp.*",'HOIT Cub
Data'!C2:C2000))*(LEFT('HOIT Cube Data'!B2:B2000,6)="FS6010")*('HOI
Cube Data'!L2:L2000))))

The first 2 criteria (column B and C) will never change but column L
('HOIT Cube Data'!L2:L2000) which I am summing is likely to change a
times, but will always be the last column. So i guess I can eithe
search for last column in worksheet or use a match on the column titl
ie (MATCH("354321*",'HOIT Cube Data'!$C$1:$AZ$1,0).

Any Thoughts or Suggestions would be appreciated.

Cheers
Kevi

 

[Frank Kabel]

Hi Kevin
try
=SUMPRODUCT(((ISNUMBER(SEARCH("*Dist. Comp.*",'HOIT Cube
Data'!C2:C2000))*(LEFT('HOIT Cube
Data'!B2:B2000,6)="FS6010")*(OFFSET('HOITCube
Data'!A2:A2000,0,MATCH("354321*",'HOIT Cube Data'!$C$1:$AZ$1,0)-1)))))

 

[wayward]

Cheers Frank
I have just tried your function but it doesnt appear to be working
Any ideas what will be throwing it out Im assuming that the offse
function moves the position from column A to last column is tha
correct?

kevi

 

[wayward]

I worked it out just need to change

(OFFSET('HOITCube Data'!A2:A2000,0,MATCH("354321*",'HOIT Cub
Data'!$C$1:$AZ$1,0)-1)))))

All refs from A to C



=SUMPRODUCT(((ISNUMBER(SEARCH("*Dist. Comp.*",'HOIT Cub
Data'!C2:C2000))*(LEFT('HOIT Cub
Data'!B2:B2000,6)="FS6010")*(OFFSET('HOIT Cub
Data'!C2:C2000,0,MATCH("354321*",'HOIT Cube Data'!$C$1:$AZ$1,0)-1)))))


Thanks heaps for your speedy help Frank
Much appreciated

Kevi

 

[wayward]

Although this equation appears to be working it is causing excel to ru
very slow with the above sumproduct function. everytime I alter a cel
even if it is unrelated to the sumproduct equation it takes 5 - 10 sec
to recalculate cells.

Is this equation so unefficient that it would cause these calculatio
delays

Kevi

 

[Peo Sjoblom]

You might want to change to manual calculation under

tools>options>calculation and check manual.

That way you can control it yourself and when you want to calculate you
can press F9

http://www.decisionmodels.com/calcsecrets.htm

 

[Aladin Akyurek]

Does...

=SUMPRODUCT(--ISNUMBER(SEARCH("*Dist. Comp.*",'HOIT Cube
Data'!$C$2:$C$2000)),--(LEFT('HOIT Cube
Data'!$B$2:$B$2000,6)="FS6010"),INDEX($C$2:$AZ$2000,0,MATCH("354321*",'HOIT
Cube Data'!$C$1:$AZ$1,0)))

perform better?

Do you have other SumProduct formulas with conditions other than FS6010 for
the range in B?

 

[wayward]

Hello Aladin, I tried that formula and it didnt appear to speed up th
calculation process.

THe sheet conatans approximately 30 sum product formulas with
different criterias ie FS6010, FS5020, FS5040, FS5070

cheers
Kevin