| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Avoiding Blanks In SUMPRODUCT
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
|
|||
|
|||
|
Avoiding Blanks In SUMPRODUCT
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
|
|||
|
|||
|
Avoiding Blanks In SUMPRODUCT
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
|
|||
|
|||
|
Avoiding Blanks In SUMPRODUCT
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
|
|||
|
|||
|
Avoiding Blanks In SUMPRODUCT
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 | |
|
|
Similar Threads
|
||||
| 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 | |||
Linear Mode
