Help required on Sumproduct and Trim combined

Dear All,

I have a formula like this =SUMPRODUCT(--(TRIM(LEFT('Feb 2013'!A:A,14))=B3),'Feb 2013'!B:B) it takes lot of time to process since I have huge data set which takes ages if I need to do any changes or additions to the data.

The above formula trims the data that is present in the Feb 2013 sheet in column A and then and then looks whether the result matches with Cell B3 and sums up all the cells in Feb 2013 column B which matches the condition.

The source data sheet cannot be changed or altered (Feb 2013 sheet) meaning I do not want to add any columns in the source sheet. This is because this gets circulated across - The user will only copy data from the database paste it in the source sheet so that automatically the result sheet gets updated.

Is there anyway to use other formula to do the same job.

Thanks in advance for your kind support in this regard.

You should avoid using full-column references like A:A with SUMPRODUCT, as it will check every cell in that range (over 1 million of them). If you have, say, 50,000 records then changing to A$1:A$50000 (and the same with B) will certainly speed things up.

Hope this helps.

Pete

Try this ...

=SUMIF('Feb 2013'!A:A,B3&"*",'Feb 2013'!B:B)

The TRIM function may not be needed, but we would need to see a fair sample of data in column A to know for sure.
The Wildcard * would handle any spaces at the end of the string in A
But may also need the * at the beginning as well

=SUMIF('Feb 2013'!A:A,"*"&B3&"*",'Feb 2013'!B:B)

Thanks a lot for your kind support in this regard. Your help is really appreciated

You're welcome.