Is it achievable update

[swaink]

Hi there

With excelent help yesterday from Peter and Ron they resolved the problenm I had.

However today I have found that some of the data in my rows is not numeric and the formula fails #Values!

The formual is as follows =SUMPRODUCT(X6:X207/100,J6:J207)

Is there a work around to complete this calculation and ignore any text characters.

Regards

Kevin

 

[Sektor]

Try IFERROR (Excel 2007/2010).

 

[Richard Schollar]

Hi

try this:

=SUM(IF(ISNUMBER(X6:X207),X6:X207,0)/100,J6:J207)

Entered with Ctrl+Shift+Enter (ie not just Enter) - it is an array formula.

 

[swaink]

Hi Guys

Appreciate the reply

Richard, I have used the formula as you suggest (ctrl+shift+enter) however if column X is empty it totals all of the values in column J

What the sumproduct formula did was ignore any blanks and only showed a zero

Any ideas

Regards

Kevin

 

[Richard Schollar]

Sorry, that should have been this:

=SUM(IF(ISNUMBER(X6:X207),X6:X207,0)/100*J6:J207)

again, CSE entered.

 

[swaink]

Hi Richard

Thats done the trick thank you so much

Appreciate all the help

Kevin