Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? -- Thanx in advance, Best Regards, Faraz |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi Faraz
Check out the below... =SUMPRODUCT(--RIGHT(SUBSTITUTE("0" & A1:A6," ",REPT(" ",255)),255)) -- Jacob "Faraz A. Qureshi" wrote: 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? -- Thanx in advance, Best Regards, Faraz |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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" wrote: Hi Faraz Check out the below... =SUMPRODUCT(--RIGHT(SUBSTITUTE("0" & A1:A6," ",REPT(" ",255)),255)) -- Jacob "Faraz A. Qureshi" wrote: 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? -- Thanx in advance, Best Regards, Faraz |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks for the feedback. Check out the below link..
http://support.microsoft.com/?id=190212 -- Jacob "Faraz A. Qureshi" wrote: 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" wrote: Hi Faraz Check out the below... =SUMPRODUCT(--RIGHT(SUBSTITUTE("0" & A1:A6," ",REPT(" ",255)),255)) -- Jacob "Faraz A. Qureshi" wrote: 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? -- Thanx in advance, Best Regards, Faraz |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanx brother,
Best Regards, Faraz "Jacob Skaria" wrote: Thanks for the feedback. Check out the below link.. http://support.microsoft.com/?id=190212 -- Jacob "Faraz A. Qureshi" wrote: 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" wrote: Hi Faraz Check out the below... =SUMPRODUCT(--RIGHT(SUBSTITUTE("0" & A1:A6," ",REPT(" ",255)),255)) -- Jacob "Faraz A. Qureshi" wrote: 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? -- Thanx in advance, Best Regards, Faraz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Sumproduct with blanks | Excel Worksheet Functions | |||
Sumproduct with blanks and the numeric 0 | Excel Discussion (Misc queries) | |||
Counting non blanks with SUMPRODUCT? | Excel Discussion (Misc queries) | |||
Sumproduct copying blanks or how to insert zero into blanks | Excel Worksheet Functions | |||
Sumproduct - Blanks | Excel Worksheet Functions |