Avoiding Blanks In SUMPRODUCT

[Faraz A. Qureshi]

With data:

Area of 0 to 500
Area of 501 to 1000
Area of 1001 to 1500
Area of 1501 to 2000
Area of 2001 to 2500
Area of 2501 to 3000

In A1:A6 I use the formula:

=SUMPRODUCT(--VALUE(MID(A1:A6,FIND("to",A1:A6)+2,LEN(A1:A6)-FIND("to",A1:A6))))

to sum up the MAXIMUM Limit in entries resulting to:
10,500

However, how to have the same formula be applied and working correctly if
there is a blank cell in A1:A6?

 

[Jacob Skaria]

Hi Faraz

Check out the below...

=SUMPRODUCT(--RIGHT(SUBSTITUTE("0" & A1:A6," ",REPT(" ",255)),255))

 

[Faraz A. Qureshi]

XClent!
New concept of usage of =REPT()
Thanx again buddy,
By the any result of my former query in respect of package and deployment
add-in, in the programming section?

Best Regards,

Faraz

 

[Jacob Skaria]

Thanks for the feedback. Check out the below link..
http://support.microsoft.com/?id=190212

 

[Faraz A. Qureshi]

Thanx brother,
Best Regards,

Faraz

Thanks for the feedback. Check out the below link..
http://support.microsoft.com/?id=190212