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
10,
"_($* #,##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???
