Sumif function

diagea · Mar 16, 2010

Dear experts,
I'm working as an economist and a week ago I had a task that I couldn't solve.
I'll be very happy if you could help me with this.
I have a ONE column with prices: some of them are in Dollar, some of them are in Euro ( like 600 lines).
I should sum separately dollar total sum and separately Euro total sum.
I don't know how to do it!
I thought about function "sumif", but I don't know how to determine the true or false that on current square is a dollar value or Euro value.

I asked this question many IT guys, but they don't know.

All my numbers on "Currency" formatting with the symbol of dollar or euro.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
I tried to work with the function LEFT to determine the symbol, but it see only the number.<o

></o

>
<o

> </o

>
I really don't want to sum this numbers one by one, but if I not find the solve this time, please advise me how should I format the number (without adding columns) next time.<o

></o

>
<o

> </o

>
Thanks a lot.<o

></o

>
Diagea

the_original_invisible · Mar 16, 2010

As a rule I wouldn't show the currency in the same field. Keep it in a different column and you won't have this problem

Anyway, in another column use this function to query the currency then you should be able to sort yourself out

Function GetFormat(cell As Range) As String
On Error Resume Next
GetFormat = ""
GetFormat = cell.NumberFormat
End Function

Hope it helps

Lee

Tom Urtis · Mar 16, 2010

The issue is, if your cell values are all numbers, then it will require VBA to sum the different currency types.

Example, if in cell A1 you enter the number 6 and format it to look like
$6.00

and in cell A2 you enter the number 4 and you format it to look like
€4

Then Excel will only see the true underlying value in the cells. Formatting only affects what the cell looks like, not what the cell actually holds as an entered value.

So, please clarify which column this is in and give a couple examples of a typical looking currency cell and a typical looking euro cell, so a macro or udf can be whipped up to give you a solution.

diagea · Mar 16, 2010

$16.90

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>$3.93</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>$27.53</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19> </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 align=right>€ 14.00</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 align=right>€ 801.00</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 align=right>€ 15.00</TD></TR></TBODY></TABLE>
The column look like this

Tom Urtis · Mar 16, 2010

Follow these directions carefully.

Step 1
From your keyboard, press Alt + F11 (careful, that's ALT + F11).

Step 2
From the menu at the top, click on Insert > Module

Step 3
Copy this code and paste it into the large white pane that is the new module:

Code:

Public Function sumFormats(rng As Range) As String
Application.Volatile
Dim cell As Range, dblDollar#, dblEuro#
dblDollar = 0: dblEuro = 0
For Each cell In rng
If Len(cell.Value) > 0 Then
If Left(cell.Text, 1) = "$" Then
dblDollar = dblDollar + cell.Value
Else
dblEuro = dblEuro + cell.Value
End If
End If
Next cell
sumFormats = "Sum of currency: $" & dblDollar & "; Sum of Euro: " & ChrW(8364) & dblEuro
End Function

Step 4
Press Alt + Q to return to your worksheet.

Step 5
I asked you what column these numbers are in, but you did not answer my question. Therefore, in some unused cell in any column other than the column holding these numbers, enter the following formula:

=sumFormats(A1:A600)

Notice, I have A1:A600 as an example range. Maybe your numbers are in J8:J50, so if that's the case, your formula would be

=sumFormats(J8:J50)

diagea · Mar 16, 2010

Dear Tom,
It is working!
BUT I have addition symbol: <TABLE style="WIDTH: 61pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=81><COLGROUP><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 61pt; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2247921 class=xl66 height=19 width=81 align=left> ₪ 600.00 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=left> ₪ 280.00 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>$150.00</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 align=left> ₪ 107.00 </TD></TR></TBODY></TABLE>

The numbers is on column B.

Thanks.
Diagea.

Tom Urtis · Mar 16, 2010

is that additional symbol supposed to be included in any calculations.

If so, which one

if not, why are the numbers there in that column with that symbol.

mgirvin · Mar 16, 2010

If your numbers and formatting will not change and you have consistent formatting applied, you can add a new column with the CELL function and the use SUMIF.

However, if you change the formatting you may have to update the formulas by use the keyboard shortcut:

F9, which calculates the worksheet
or
Shift + F9, which calculates the workbook.

The CELL function would look something like this:

=CELL("format",A2)

where cell A2 has the number. CELL will deliver a code for the formatting. To learn about the codes, look in help for full details. To get to help: with the cell with the CELL function selected, click on the Fx button on the Formula Bar, then click on the blue link "Help on this function”.

Then you can use SUMIF like this:

Dollar total = SUMIF(range with CELL function,"C2",range with numbers)

Euro total = SUMIF(range with CELL function,",2",range with numbers)

The criteria to use for SUMIF will be determined by what the CELL function delivers and what you see in the Help menu. In this particular example, the CELL function is delivering a “C2” for the Currency Number format and a “,2” for the Euro Number format. If you had extra characters (post #6), you could either change the formatting or add a third SUMIF if that was part of your goal. As Tom, said, if they are not supposed to be there, why are they there?

Here is an example:

Excel Workbook

A

B

1

Number

Cell

2

$1.00

C2

3

$1.00

C2

4

$1.00

C2

5

1.00

,2

...

Cell Formulas
Range	Formula
B2	=CELL("format",A2)
B3	=CELL("format",A3)
B4	=CELL("format",A4)
B5	=CELL("format",A5)

Excel Workbook

D

E

1

C2

,2

2

Dollar

Euro

3

SUM

4

$3.00

1.00

...

Cell Formulas
Range	Formula
D4	=SUMIF($B$2:$B$5,D1,$A$2:$A$5)
E4	=SUMIF($B$2:$B$5,E1,$A$2:$A$5)

Remember:
1) Formatting is applied consistently.
2) If you change things in sheet, use F9 to update.

I hope that helps.

diagea · Mar 16, 2010

Tom,
₪ is a symbol of Israeli new shekel - the currency of the Israel.
Every table included there symbols: euro, dollar and ₪.

It will be great if you can write module with the option to show the sums on column and not on row.

Thanks a lot.
Diagea

Tom Urtis · Mar 16, 2010

My code sums what you tell it to sum. That is why in the formula (for example)
=sumFormats(B1:B100)
you are the one who enters that, meaning if you want to enter for a range of rows, then enter (assuming it is row 5)
=sumFormats(A5:Z5)
or whatever columns are involved.

Sumif function

diagea

New Member

the_original_invisible

Board Regular

Tom Urtis

MrExcel MVP

diagea

New Member

Tom Urtis

MrExcel MVP

diagea

New Member

Tom Urtis

MrExcel MVP

mgirvin

Well-known Member

diagea

New Member

Tom Urtis

MrExcel MVP

Similar threads

Share this page

Sumif function

New Member

Board Regular

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Well-known Member

New Member

MrExcel MVP

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock