Simplify Long Formula

Hi,

Is anyone able to simplify the following formula:

Please Login or Register  to view this content.

Thanks

Try this aRRAY formula.

Please Login or Register  to view this content.

To enter ARRAY formula,
Paste the formula
Don't Press Enter Key
Press Ctrl+Shift+Enter Keys together.
You will see {} around ARRAY formula.

Right, I've another one here. Its a simple piece of code but it sometimes causes my PC to freeze for a good few seconds (Even longer when drag filling up or down, left or right), can it be shortened? I tried to add columns H & I (i.e. $H:$I) together but it did not work, so have to do them separately.

Please Login or Register  to view this content.

Thanks

The most effective way to make that calculate faster would be to NOT use entire column references like C:C
Change the references to a meaningful range like C1:C1000

You could also cut the 8 sumproducts down to 4 by combining the H and I columns into 1 sumproduct
=SUMPRODUCT(('C:\Files\[Worksheet.xlsx]DA'!$C$1:$C$1000=B$1)*('C:\Files\[Worksheet.xlsx]DA'!$D$1:$D$1000=$A2)*('C:\Files\[Worksheet.xlsx]DA'!$H$1:$I$1000))

Originally Posted by Jonmo1

The most effective way to make that calculate faster would be to NOT use entire column references like C:C
Change the references to a meaningful range like C1:C1000

You could also cut the 8 sumproducts down to 4 by combining the H and I columns into 1 sumproduct
=SUMPRODUCT(('C:\Files\[Worksheet.xlsx]DA'!$C$1:$C$1000=B$1)*('C:\Files\[Worksheet.xlsx]DA'!$D$1:$D$1000=$A2)*('C:\Files\[Worksheet.xlsx]DA'!$H$1:$I$1000))

I get a #VALUE! error when I enter that formula, but if I do this it works:

=SUMPRODUCT(('C:\Files\[Worksheet.xlsx]DA'!$C$1:$C$1000=B$1)*('C:\Files\[Worksheet.xlsx]DA'!$D$1:$D$1000=$A2)*('C:\Files\[Worksheet.xlsx]DA'!$H$1:$H$1000))
+SUMPRODUCT(('C:\Files\[Worksheet.xlsx]DA'!$C$1:$C$1000=B$1)*('C:\Files\[Worksheet.xlsx]DA'!$D$1:$D$1000=$A2)*('C:\Files\[Worksheet.xlsx]DA'!$I$1:$I$1000))

That makes no sense...
If the formula I posted returns #Value! (probably because there is text in either column H or I)
Then the one doing H & I seperately would as well.

Notice the column references isn't the only thing I changed from your original..

I also changed the last , to *(

Yes, it works now! There was an instance of text in the FD Sheet where a number should have been, thats now sorted.