summing different values

  • Thread starter Thread starter Khalil handal
  • Start date Start date
K

Khalil handal

Range B3:F33 has differen values. Cells are formatted as accounting for 3
different currecy: Hebrew (NIS), Dollars and Euros

In cell B35 I want to have the sum of the NIS Hbrew Shekels
In cell B36 I want the summ of Dollars
In cell B37 I want the sum of the Euros from the range.
Is This possible???
 
Are you saying the numbers in B3:B33 have one of three formats; you wan to
sum by format?
 
YES, the numbers in the range B3:B33 are divided into three different
formats and want to sum by format for the three formats.
 
There's nothing built into excel that lets you do this kind of thing.

But you can use a User Defined Function (UDF):

Option Explicit
Function SumByNumberFormat(rng As Range, NumFmt As String) As Double

Dim myCell As Range
Dim mySum As Double

Application.Volatile
mySum = 0
For Each myCell In rng.Cells
If Application.IsNumber(myCell.Value) Then
If LCase(myCell.NumberFormat) = LCase(NumFmt) Then
mySum = mySum + myCell.Value
End If
End If
Next myCell

SumByNumberFormat = mySum

End Function

Be aware that if you change the numberformat of any of the cells, then this
formula cell won't change until your workbook calculates.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=sumbynumberformat(A1:A5,"#,##0.00_);(#,##0.00)")

You'll change the numberformat string to whatever you need.
 
Hi,
I did it step by step but have a zero answer in the cell.
I think i am missing something inthe "numberformat string" #,##0.00 ???
I am not certain to how this should be??? Is it to be done from custom
formating?
 
Select a cell that has the numberformatting that you want to know about.
Go to the VBE
hit alt-g to get to the immediate window
type this and hit enter
?activecell.numberformat
 
This is what i have for the Israeli Shekel
_ [$?-40D] * #,##0.00_ ;_ [$?-40D] * -#,##0.00_ ;_ [$?-40D] * "-"??_ ;_ @_

This is what i get for the Dollars
_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

Khalil
 
First, change a line in the UDF:

If Application.IsNumber(myCell.Value) Then
becomes
If Application.IsNumber(myCell.Value2) Then

(It's the way excel/VBA treats currency.)

For the dollars:

=sumbynumberformat(A1:D10,
"_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)")

Notice how the double quotes are treated--double them up.



Khalil said:
This is what i have for the Israeli Shekel
_ [$?-40D] * #,##0.00_ ;_ [$?-40D] * -#,##0.00_ ;_ [$?-40D] * "-"??_ ;_ @_

This is what i get for the Dollars
_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

Khalil
Click to expand...
 
Sorry,
Didn't work! any other suggestions?
I can attach a sample file!


Dave Peterson said:
First, change a line in the UDF:

If Application.IsNumber(myCell.Value) Then
becomes
If Application.IsNumber(myCell.Value2) Then

(It's the way excel/VBA treats currency.)

For the dollars:

=sumbynumberformat(A1:D10,
"_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)")

Notice how the double quotes are treated--double them up.



Khalil said:
This is what i have for the Israeli Shekel
_ [$?-40D] * #,##0.00_ ;_ [$?-40D] * -#,##0.00_ ;_ [$?-40D] * "-"??_ ;_
@_

This is what i get for the Dollars
_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

Khalil

Dave Peterson said:
Select a cell that has the numberformatting that you want to know
about.
Go to the VBE
hit alt-g to get to the immediate window
type this and hit enter
?activecell.numberformat



Khalil handal wrote:

Hi,
I did it step by step but have a zero answer in the cell.
I think i am missing something inthe "numberformat string" #,##0.00
???
I am not certain to how this should be??? Is it to be done from custom
formating?

There's nothing built into excel that lets you do this kind of
thing.

But you can use a User Defined Function (UDF):

Option Explicit
Function SumByNumberFormat(rng As Range, NumFmt As String) As Double

Dim myCell As Range
Dim mySum As Double

Application.Volatile
mySum = 0
For Each myCell In rng.Cells
If Application.IsNumber(myCell.Value) Then
If LCase(myCell.NumberFormat) = LCase(NumFmt) Then
mySum = mySum + myCell.Value
End If
End If
Next myCell

SumByNumberFormat = mySum

End Function

Be aware that if you change the numberformat of any of the cells,
then
this
formula cell won't change until your workbook calculates.

If you're new to macros, you may want to read David McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=sumbynumberformat(A1:A5,"#,##0.00_);(#,##0.00)")

You'll change the numberformat string to whatever you need.


Khalil Handal wrote:

YES, the numbers in the range B3:B33 are divided into three
different
formats and want to sum by format for the three formats.

Are you saying the numbers in B3:B33 have one of three formats;
you
wan
to
sum by format?

--
www.stfx.ca/people/bliengme


:

Range B3:F33 has differen values. Cells are formatted as
accounting
for 3
different currecy: Hebrew (NIS), Dollars and Euros

In cell B35 I want to have the sum of the NIS Hbrew Shekels
In cell B36 I want the summ of Dollars
In cell B37 I want the sum of the Euros from the range.
Is This possible???
Click to expand...
Click to expand...
Click to expand...
 
Not until you explain "what didn't" work means.

It worked fine for me.

Khalil said:
Sorry,
Didn't work! any other suggestions?
I can attach a sample file!

Dave Peterson said:
First, change a line in the UDF:

If Application.IsNumber(myCell.Value) Then
becomes
If Application.IsNumber(myCell.Value2) Then

(It's the way excel/VBA treats currency.)

For the dollars:

=sumbynumberformat(A1:D10,
"_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)")

Notice how the double quotes are treated--double them up.



Khalil said:
This is what i have for the Israeli Shekel
_ [$?-40D] * #,##0.00_ ;_ [$?-40D] * -#,##0.00_ ;_ [$?-40D] * "-"??_ ;_
@_

This is what i get for the Dollars
_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

Khalil

Select a cell that has the numberformatting that you want to know
about.
Go to the VBE
hit alt-g to get to the immediate window
type this and hit enter
?activecell.numberformat



Khalil handal wrote:

Hi,
I did it step by step but have a zero answer in the cell.
I think i am missing something inthe "numberformat string" #,##0.00
???
I am not certain to how this should be??? Is it to be done from custom
formating?

There's nothing built into excel that lets you do this kind of
thing.

But you can use a User Defined Function (UDF):

Option Explicit
Function SumByNumberFormat(rng As Range, NumFmt As String) As Double

Dim myCell As Range
Dim mySum As Double

Application.Volatile
mySum = 0
For Each myCell In rng.Cells
If Application.IsNumber(myCell.Value) Then
If LCase(myCell.NumberFormat) = LCase(NumFmt) Then
mySum = mySum + myCell.Value
End If
End If
Next myCell

SumByNumberFormat = mySum

End Function

Be aware that if you change the numberformat of any of the cells,
then
this
formula cell won't change until your workbook calculates.

If you're new to macros, you may want to read David McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=sumbynumberformat(A1:A5,"#,##0.00_);(#,##0.00)")

You'll change the numberformat string to whatever you need.


Khalil Handal wrote:

YES, the numbers in the range B3:B33 are divided into three
different
formats and want to sum by format for the three formats.

Are you saying the numbers in B3:B33 have one of three formats;
you
wan
to
sum by format?

--
www.stfx.ca/people/bliengme


:

Range B3:F33 has differen values. Cells are formatted as
accounting
for 3
different currecy: Hebrew (NIS), Dollars and Euros

In cell B35 I want to have the sum of the NIS Hbrew Shekels
In cell B36 I want the summ of Dollars
In cell B37 I want the sum of the Euros from the range.
Is This possible???
Click to expand...
Click to expand...
Click to expand...
 
First, I don't see a difference between the formatting for the Shekel and Euro.
How you going to get different sums based on the same format?

Second, it worked fine for me.

I'm not sure what "NO answer" means. If the cell appears empty, you're doing
something wrong.


Khalil said:
Hi,
Sorry for the trouble. I have an answer for the $ currency (the wrong
answer), NO answer is seen for the Euro and Shekel.
The formulas in each of th cells are:
Dollars:
=sumbynumberformat($B$2:$E$10,"_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)")
Euro
=sumbynumberformat($B$2:$E$10,"#,##0.00_);(#,##0.00)")
Shekels:
=sumbynumberformat($B$2:$E$10,"#,##0.00_);(#,##0.00)")

It seems that I am missing something!!!!!

Dave Peterson said:
Not until you explain "what didn't" work means.

It worked fine for me.

Khalil said:
Sorry,
Didn't work! any other suggestions?
I can attach a sample file!

First, change a line in the UDF:

If Application.IsNumber(myCell.Value) Then
becomes
If Application.IsNumber(myCell.Value2) Then

(It's the way excel/VBA treats currency.)

For the dollars:

=sumbynumberformat(A1:D10,
"_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)")

Notice how the double quotes are treated--double them up.



Khalil Handal wrote:

This is what i have for the Israeli Shekel
_ [$?-40D] * #,##0.00_ ;_ [$?-40D] * -#,##0.00_ ;_ [$?-40D] * "-"??_
;_
@_

This is what i get for the Dollars
_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

Khalil

Select a cell that has the numberformatting that you want to know
about.
Go to the VBE
hit alt-g to get to the immediate window
type this and hit enter
?activecell.numberformat



Khalil handal wrote:

Hi,
I did it step by step but have a zero answer in the cell.
I think i am missing something inthe "numberformat string"
#,##0.00
???
I am not certain to how this should be??? Is it to be done from
custom
formating?

There's nothing built into excel that lets you do this kind of
thing.

But you can use a User Defined Function (UDF):

Option Explicit
Function SumByNumberFormat(rng As Range, NumFmt As String) As
Double

Dim myCell As Range
Dim mySum As Double

Application.Volatile
mySum = 0
For Each myCell In rng.Cells
If Application.IsNumber(myCell.Value) Then
If LCase(myCell.NumberFormat) = LCase(NumFmt) Then
mySum = mySum + myCell.Value
End If
End If
Next myCell

SumByNumberFormat = mySum

End Function

Be aware that if you change the numberformat of any of the cells,
then
this
formula cell won't change until your workbook calculates.

If you're new to macros, you may want to read David McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=sumbynumberformat(A1:A5,"#,##0.00_);(#,##0.00)")

You'll change the numberformat string to whatever you need.


Khalil Handal wrote:

YES, the numbers in the range B3:B33 are divided into three
different
formats and want to sum by format for the three formats.

Are you saying the numbers in B3:B33 have one of three
formats;
you
wan
to
sum by format?

--
www.stfx.ca/people/bliengme


:

Range B3:F33 has differen values. Cells are formatted as
accounting
for 3
different currecy: Hebrew (NIS), Dollars and Euros

In cell B35 I want to have the sum of the NIS Hbrew Shekels
In cell B36 I want the summ of Dollars
In cell B37 I want the sum of the Euros from the range.
Is This possible???
Click to expand...
Click to expand...
Click to expand...
 
Hello,

Just format the summing cells in the *same* formats as you are using
in your range B3:F33,
press ALT + F11, insert a module, copy the macro below into the
module, go back to your worksheet and enter =smf(B3:F33) into all your
summing cells.

Function smf(r As Range)
'Sum my format: sums up all values in r which have
'the same format as calling cell (where this
'function is called from).
Dim v

With Application.Caller
For Each v In r
If v.NumberFormat = .NumberFormat Then
smf = smf + v
End If
Next v
End With

End Function

Regards,
Bernd

PS: Do not copy the formula =smf() with CTRL + c and CTRL + v :-)
 
Hi,
Sorry for the trouble. I have an answer for the $ currency (the wrong
answer), NO answer is seen for the Euro and Shekel.
The formulas in each of th cells are:
Dollars:
=sumbynumberformat($B$2:$E$10,"_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)")
Euro
=sumbynumberformat($B$2:$E$10,"#,##0.00_);(#,##0.00)")
Shekels:
=sumbynumberformat($B$2:$E$10,"#,##0.00_);(#,##0.00)")

It seems that I am missing something!!!!!


Dave Peterson said:
Not until you explain "what didn't" work means.

It worked fine for me.

Khalil said:
Sorry,
Didn't work! any other suggestions?
I can attach a sample file!

Dave Peterson said:
First, change a line in the UDF:

If Application.IsNumber(myCell.Value) Then
becomes
If Application.IsNumber(myCell.Value2) Then

(It's the way excel/VBA treats currency.)

For the dollars:

=sumbynumberformat(A1:D10,
"_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)")

Notice how the double quotes are treated--double them up.



Khalil Handal wrote:

This is what i have for the Israeli Shekel
_ [$?-40D] * #,##0.00_ ;_ [$?-40D] * -#,##0.00_ ;_ [$?-40D] * "-"??_
;_
@_

This is what i get for the Dollars
_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

Khalil

Select a cell that has the numberformatting that you want to know
about.
Go to the VBE
hit alt-g to get to the immediate window
type this and hit enter
?activecell.numberformat



Khalil handal wrote:

Hi,
I did it step by step but have a zero answer in the cell.
I think i am missing something inthe "numberformat string"
#,##0.00
???
I am not certain to how this should be??? Is it to be done from
custom
formating?

There's nothing built into excel that lets you do this kind of
thing.

But you can use a User Defined Function (UDF):

Option Explicit
Function SumByNumberFormat(rng As Range, NumFmt As String) As
Double

Dim myCell As Range
Dim mySum As Double

Application.Volatile
mySum = 0
For Each myCell In rng.Cells
If Application.IsNumber(myCell.Value) Then
If LCase(myCell.NumberFormat) = LCase(NumFmt) Then
mySum = mySum + myCell.Value
End If
End If
Next myCell

SumByNumberFormat = mySum

End Function

Be aware that if you change the numberformat of any of the cells,
then
this
formula cell won't change until your workbook calculates.

If you're new to macros, you may want to read David McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=sumbynumberformat(A1:A5,"#,##0.00_);(#,##0.00)")

You'll change the numberformat string to whatever you need.


Khalil Handal wrote:

YES, the numbers in the range B3:B33 are divided into three
different
formats and want to sum by format for the three formats.

Are you saying the numbers in B3:B33 have one of three
formats;
you
wan
to
sum by format?

--
www.stfx.ca/people/bliengme


:

Range B3:F33 has differen values. Cells are formatted as
accounting
for 3
different currecy: Hebrew (NIS), Dollars and Euros

In cell B35 I want to have the sum of the NIS Hbrew Shekels
In cell B36 I want the summ of Dollars
In cell B37 I want the sum of the Euros from the range.
Is This possible???
Click to expand...
Click to expand...
Click to expand...
 
I tried with this formating for the Euro:
=sumbynumberformat(B2:E10,"_([$?-2] * #,##0.00_);_([$?-2] *
(#,##0.00);_([$?-2] * "-"??_);_(@_)")

and obtained an error: #VALUE!


Dave Peterson said:
First, I don't see a difference between the formatting for the Shekel and
Euro.
How you going to get different sums based on the same format?

Second, it worked fine for me.

I'm not sure what "NO answer" means. If the cell appears empty, you're
doing
something wrong.


Khalil said:
Hi,
Sorry for the trouble. I have an answer for the $ currency (the wrong
answer), NO answer is seen for the Euro and Shekel.
The formulas in each of th cells are:
Dollars:
=sumbynumberformat($B$2:$E$10,"_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)")
Euro
=sumbynumberformat($B$2:$E$10,"#,##0.00_);(#,##0.00)")
Shekels:
=sumbynumberformat($B$2:$E$10,"#,##0.00_);(#,##0.00)")

It seems that I am missing something!!!!!

Dave Peterson said:
Not until you explain "what didn't" work means.

It worked fine for me.

Khalil handal wrote:

Sorry,
Didn't work! any other suggestions?
I can attach a sample file!

First, change a line in the UDF:

If Application.IsNumber(myCell.Value) Then
becomes
If Application.IsNumber(myCell.Value2) Then

(It's the way excel/VBA treats currency.)

For the dollars:

=sumbynumberformat(A1:D10,
"_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)")

Notice how the double quotes are treated--double them up.



Khalil Handal wrote:

This is what i have for the Israeli Shekel
_ [$?-40D] * #,##0.00_ ;_ [$?-40D] * -#,##0.00_ ;_ [$?-40D] *
"-"??_
;_
@_

This is what i get for the Dollars
_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

Khalil

Select a cell that has the numberformatting that you want to know
about.
Go to the VBE
hit alt-g to get to the immediate window
type this and hit enter
?activecell.numberformat



Khalil handal wrote:

Hi,
I did it step by step but have a zero answer in the cell.
I think i am missing something inthe "numberformat string"
#,##0.00
???
I am not certain to how this should be??? Is it to be done from
custom
formating?

There's nothing built into excel that lets you do this kind of
thing.

But you can use a User Defined Function (UDF):

Option Explicit
Function SumByNumberFormat(rng As Range, NumFmt As String) As
Double

Dim myCell As Range
Dim mySum As Double

Application.Volatile
mySum = 0
For Each myCell In rng.Cells
If Application.IsNumber(myCell.Value) Then
If LCase(myCell.NumberFormat) = LCase(NumFmt) Then
mySum = mySum + myCell.Value
End If
End If
Next myCell

SumByNumberFormat = mySum

End Function

Be aware that if you change the numberformat of any of the
cells,
then
this
formula cell won't change until your workbook calculates.

If you're new to macros, you may want to read David
McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=sumbynumberformat(A1:A5,"#,##0.00_);(#,##0.00)")

You'll change the numberformat string to whatever you need.


Khalil Handal wrote:

YES, the numbers in the range B3:B33 are divided into three
different
formats and want to sum by format for the three formats.

message
Are you saying the numbers in B3:B33 have one of three
formats;
you
wan
to
sum by format?

--
www.stfx.ca/people/bliengme


:

Range B3:F33 has differen values. Cells are formatted as
accounting
for 3
different currecy: Hebrew (NIS), Dollars and Euros

In cell B35 I want to have the sum of the NIS Hbrew
Shekels
In cell B36 I want the summ of Dollars
In cell B37 I want the sum of the Euros from the range.
Is This possible???
Click to expand...
Click to expand...
Click to expand...
 
You didn't double up the quotes inside that string.

I don't know if there's anything else wrong.

Khalil said:
I tried with this formating for the Euro:
=sumbynumberformat(B2:E10,"_([$?-2] * #,##0.00_);_([$?-2] *
(#,##0.00);_([$?-2] * "-"??_);_(@_)")

and obtained an error: #VALUE!

Dave Peterson said:
First, I don't see a difference between the formatting for the Shekel and
Euro.
How you going to get different sums based on the same format?

Second, it worked fine for me.

I'm not sure what "NO answer" means. If the cell appears empty, you're
doing
something wrong.


Khalil said:
Hi,
Sorry for the trouble. I have an answer for the $ currency (the wrong
answer), NO answer is seen for the Euro and Shekel.
The formulas in each of th cells are:
Dollars:
=sumbynumberformat($B$2:$E$10,"_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)")
Euro
=sumbynumberformat($B$2:$E$10,"#,##0.00_);(#,##0.00)")
Shekels:
=sumbynumberformat($B$2:$E$10,"#,##0.00_);(#,##0.00)")

It seems that I am missing something!!!!!

Not until you explain "what didn't" work means.

It worked fine for me.

Khalil handal wrote:

Sorry,
Didn't work! any other suggestions?
I can attach a sample file!

First, change a line in the UDF:

If Application.IsNumber(myCell.Value) Then
becomes
If Application.IsNumber(myCell.Value2) Then

(It's the way excel/VBA treats currency.)

For the dollars:

=sumbynumberformat(A1:D10,
"_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)")

Notice how the double quotes are treated--double them up.



Khalil Handal wrote:

This is what i have for the Israeli Shekel
_ [$?-40D] * #,##0.00_ ;_ [$?-40D] * -#,##0.00_ ;_ [$?-40D] *
"-"??_
;_
@_

This is what i get for the Dollars
_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

Khalil

Select a cell that has the numberformatting that you want to know
about.
Go to the VBE
hit alt-g to get to the immediate window
type this and hit enter
?activecell.numberformat



Khalil handal wrote:

Hi,
I did it step by step but have a zero answer in the cell.
I think i am missing something inthe "numberformat string"
#,##0.00
???
I am not certain to how this should be??? Is it to be done from
custom
formating?

There's nothing built into excel that lets you do this kind of
thing.

But you can use a User Defined Function (UDF):

Option Explicit
Function SumByNumberFormat(rng As Range, NumFmt As String) As
Double

Dim myCell As Range
Dim mySum As Double

Application.Volatile
mySum = 0
For Each myCell In rng.Cells
If Application.IsNumber(myCell.Value) Then
If LCase(myCell.NumberFormat) = LCase(NumFmt) Then
mySum = mySum + myCell.Value
End If
End If
Next myCell

SumByNumberFormat = mySum

End Function

Be aware that if you change the numberformat of any of the
cells,
then
this
formula cell won't change until your workbook calculates.

If you're new to macros, you may want to read David
McRitchie's
intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=sumbynumberformat(A1:A5,"#,##0.00_);(#,##0.00)")

You'll change the numberformat string to whatever you need.


Khalil Handal wrote:

YES, the numbers in the range B3:B33 are divided into three
different
formats and want to sum by format for the three formats.

message
Are you saying the numbers in B3:B33 have one of three
formats;
you
wan
to
sum by format?

--
www.stfx.ca/people/bliengme


:

Range B3:F33 has differen values. Cells are formatted as
accounting
for 3
different currecy: Hebrew (NIS), Dollars and Euros

In cell B35 I want to have the sum of the NIS Hbrew
Shekels
In cell B36 I want the summ of Dollars
In cell B37 I want the sum of the Euros from the range.
Is This possible???
Click to expand...
Click to expand...
Click to expand...
 
Back
Top