| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
sumproduct of part cells of a range with blanks
hi all,
please help REF is the name of the range A5:A10, it's items are the following A5=123.a A6=45.b A7=22.b A8=19.a A9=25.a A10 is blank the formula =sumproduct(--left(REF,find(".",REF)-1)) gives an error #VALUE! if any cell of the range is blank. is there a way to solve this problem with many thanks |
|
#2
|
|||
|
|||
|
It's not that it is a blank per se, it's because find cannot find a period,
this should work unless you have letter with periods like aaa.a but if the only other alternative to numberDOTletter is a blank this should work =SUM(IF(ISERROR(FIND(".",A5:A10)),0,--LEFT(A5:A10,FIND(".",A5:A10)))) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "excelFan" wrote in message ... hi all, please help REF is the name of the range A5:A10, it's items are the following A5=123.a A6=45.b A7=22.b A8=19.a A9=25.a A10 is blank the formula =sumproduct(--left(REF,find(".",REF)-1)) gives an error #VALUE! if any cell of the range is blank. is there a way to solve this problem with many thanks |
|
#3
|
|||
|
|||
|
thanks Peo Sjoblom for your help your suggestion works very nice
but now if you can help please, i like to sum for a particular letter for examlpe those of .a only many thanks again "Peo Sjoblom" wrote: It's not that it is a blank per se, it's because find cannot find a period, this should work unless you have letter with periods like aaa.a but if the only other alternative to numberDOTletter is a blank this should work =SUM(IF(ISERROR(FIND(".",A5:A10)),0,--LEFT(A5:A10,FIND(".",A5:A10)))) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "excelFan" wrote in message ... hi all, please help REF is the name of the range A5:A10, it's items are the following A5=123.a A6=45.b A7=22.b A8=19.a A9=25.a A10 is blank the formula =sumproduct(--left(REF,find(".",REF)-1)) gives an error #VALUE! if any cell of the range is blank. is there a way to solve this problem with many thanks |
|
#4
|
|||
|
|||
|
I would use a formula to extract all numbers in one column,
=IF(ISERROR(FIND(".",A5)),0,--LEFT(A5,FIND(".",A5))) copy down then use =SUMPRODUCT(--(ISNUMBER(FIND("a",A5:A10)),B5:B10)) will sum extracted numbers in B where A is "a" -- Regards, Peo Sjoblom "excelFan" wrote in message ... thanks Peo Sjoblom for your help your suggestion works very nice but now if you can help please, i like to sum for a particular letter for examlpe those of .a only many thanks again "Peo Sjoblom" wrote: It's not that it is a blank per se, it's because find cannot find a period, this should work unless you have letter with periods like aaa.a but if the only other alternative to numberDOTletter is a blank this should work =SUM(IF(ISERROR(FIND(".",A5:A10)),0,--LEFT(A5:A10,FIND(".",A5:A10)))) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "excelFan" wrote in message ... hi all, please help REF is the name of the range A5:A10, it's items are the following A5=123.a A6=45.b A7=22.b A8=19.a A9=25.a A10 is blank the formula =sumproduct(--left(REF,find(".",REF)-1)) gives an error #VALUE! if any cell of the range is blank. is there a way to solve this problem with many thanks |
|
#5
|
|||
|
|||
|
Many thanks Peo Sjoblom for you great help
your second suggestion for summing for a particular letter is very helpfull many thanks again and again "Peo Sjoblom" wrote: I would use a formula to extract all numbers in one column, =IF(ISERROR(FIND(".",A5)),0,--LEFT(A5,FIND(".",A5))) copy down then use =SUMPRODUCT(--(ISNUMBER(FIND("a",A5:A10)),B5:B10)) will sum extracted numbers in B where A is "a" -- Regards, Peo Sjoblom "excelFan" wrote in message ... thanks Peo Sjoblom for your help your suggestion works very nice but now if you can help please, i like to sum for a particular letter for examlpe those of .a only many thanks again "Peo Sjoblom" wrote: It's not that it is a blank per se, it's because find cannot find a period, this should work unless you have letter with periods like aaa.a but if the only other alternative to numberDOTletter is a blank this should work =SUM(IF(ISERROR(FIND(".",A5:A10)),0,--LEFT(A5:A10,FIND(".",A5:A10)))) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "excelFan" wrote in message ... hi all, please help REF is the name of the range A5:A10, it's items are the following A5=123.a A6=45.b A7=22.b A8=19.a A9=25.a A10 is blank the formula =sumproduct(--left(REF,find(".",REF)-1)) gives an error #VALUE! if any cell of the range is blank. is there a way to solve this problem with many thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| What formula is used for subtracting a range of different cells f. | Excel Worksheet Functions | |||
| Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) | |||
| How can I dynamically eliminate blank cells in a given range in E. | Excel Worksheet Functions | |||
| summing part of cells in a range | Excel Discussion (Misc queries) | |||
| How do i get an average that ignores blanks in the range of cells. | Excel Worksheet Functions | |||
Linear Mode
