Avoiding Blanks In SUMPRODUCT

  • Thread starter Thread starter Faraz A. Qureshi
  • Start date Start date
F

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?
 
Hi Faraz

Check out the below...

=SUMPRODUCT(--RIGHT(SUBSTITUTE("0" & A1:A6," ",REPT(" ",255)),255))
 
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
 
Back
Top