|
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 06:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com