Hi

How do I go about summing all the cells in a row based on their number format.

I will only have either 0.00% or #,##0.00 and need to sum the non pct
formatted cells.

Thanks in advance.

Regards

walk on water at the weekends do you,

thanks vm for your help, it's exactly what I needed.

I really do have to get my head around udf's

thanks again

"Nigel" wrote:

You do not say where you want to sum this, but if it is in a sheet try using
the following UDF (not fully tested).

use it by placing in a cell

=sumformat(A1:IV1)


Function sumformat(rngS As Range)
Dim rngC As Range
sumformat = 0
If Not rngS Is Nothing Then
For Each rngC In rngS
If rngC.NumberFormat < "0.00%" Then
sumformat = sumformat + rngC.Value
End If
Next
End If
End Function

--

Regards,
Nigel




"Richard" wrote in message
...
Hi

How do I go about summing all the cells in a row based on their number
format.

I will only have either 0.00% or #,##0.00 and need to sum the non pct
formatted cells.

Thanks in advance.

Regards

Sorry I jumped the gun a little there, it worked great the first time, but
now all I get in the cell where the function is entered is #NAME?
The UDF is entered as part of a macro, but this shouldn't make any difference.

Can you advise
Thanks

"Nigel" wrote:

You do not say where you want to sum this, but if it is in a sheet try using
the following UDF (not fully tested).

use it by placing in a cell

=sumformat(A1:IV1)


Function sumformat(rngS As Range)
Dim rngC As Range
sumformat = 0
If Not rngS Is Nothing Then
For Each rngC In rngS
If rngC.NumberFormat < "0.00%" Then
sumformat = sumformat + rngC.Value
End If
Next
End If
End Function

--

Regards,
Nigel




"Richard" wrote in message
...
Hi

How do I go about summing all the cells in a row based on their number
format.

I will only have either 0.00% or #,##0.00 and need to sum the non pct
formatted cells.

Thanks in advance.

Regards

Hi
Not sure about walking on water at anytime!

Where have you stored the function? It should be in a standard module.


--

Regards,
Nigel




"Richard" wrote in message
...
Sorry I jumped the gun a little there, it worked great the first time,
but
now all I get in the cell where the function is entered is #NAME?
The UDF is entered as part of a macro, but this shouldn't make any
difference.

Can you advise
Thanks

"Nigel" wrote:

You do not say where you want to sum this, but if it is in a sheet try
using
the following UDF (not fully tested).

use it by placing in a cell

=sumformat(A1:IV1)


Function sumformat(rngS As Range)
Dim rngC As Range
sumformat = 0
If Not rngS Is Nothing Then
For Each rngC In rngS
If rngC.NumberFormat < "0.00%" Then
sumformat = sumformat + rngC.Value
End If
Next
End If
End Function

--

Regards,
Nigel




"Richard" wrote in message
...
Hi

How do I go about summing all the cells in a row based on their number
format.

I will only have either 0.00% or #,##0.00 and need to sum the non pct
formatted cells.

Thanks in advance.

Regards

Nigel

Thanks for getting back to me on this.

Yes I have stored it in a standard module. It's odd as when I either edit
the cell with the udf and then enter with no changes, or I sort the table in
which the udf is included, the correct result appears. Unfortunately I
either have to edit each cell that contains the udf, or sort twice, once to
get the result and once to perform the sort.

Richard

"Nigel" wrote:

Hi
Not sure about walking on water at anytime!

Where have you stored the function? It should be in a standard module.


--

Regards,
Nigel




"Richard" wrote in message
...
Sorry I jumped the gun a little there, it worked great the first time,
but
now all I get in the cell where the function is entered is #NAME?
The UDF is entered as part of a macro, but this shouldn't make any
difference.

Can you advise
Thanks

"Nigel" wrote:

You do not say where you want to sum this, but if it is in a sheet try
using
the following UDF (not fully tested).

use it by placing in a cell

=sumformat(A1:IV1)


Function sumformat(rngS As Range)
Dim rngC As Range
sumformat = 0
If Not rngS Is Nothing Then
For Each rngC In rngS
If rngC.NumberFormat < "0.00%" Then
sumformat = sumformat + rngC.Value
End If
Next
End If
End Function

--

Regards,
Nigel




"Richard" wrote in message
...
Hi

How do I go about summing all the cells in a row based on their number
format.

I will only have either 0.00% or #,##0.00 and need to sum the non pct
formatted cells.

Thanks in advance.

Regards

Nigel

The solution I found was to enter the udf formula in each cell using a loop.
I was previously entering the formula by the use of an array.

Ah well, all sorted now.

Thanks for the help and advice

"Nigel" wrote:

Hi
Not sure about walking on water at anytime!

Where have you stored the function? It should be in a standard module.


--

Regards,
Nigel




"Richard" wrote in message
...
Sorry I jumped the gun a little there, it worked great the first time,
but
now all I get in the cell where the function is entered is #NAME?
The UDF is entered as part of a macro, but this shouldn't make any
difference.

Can you advise
Thanks

"Nigel" wrote:

You do not say where you want to sum this, but if it is in a sheet try
using
the following UDF (not fully tested).

use it by placing in a cell

=sumformat(A1:IV1)


Function sumformat(rngS As Range)
Dim rngC As Range
sumformat = 0
If Not rngS Is Nothing Then
For Each rngC In rngS
If rngC.NumberFormat < "0.00%" Then
sumformat = sumformat + rngC.Value
End If
Next
End If
End Function

--

Regards,
Nigel




"Richard" wrote in message
...
Hi

How do I go about summing all the cells in a row based on their number
format.

I will only have either 0.00% or #,##0.00 and need to sum the non pct
formatted cells.

Thanks in advance.

Regards

Hello,

I suggest to use my format-independant UDF:
http://www.sulprobil.com/html/sum_my_format.html

Regards,
Bernd