Sum by Currency Code
Did you know
that you can sum by currency code?
When you format sums with the Accounting Number Format, Excel
automatically adds the currency code that you chose.
Pic 1: Sums with currency codes (added by the Accounting number format)
However, This
code is inseparable from the number, which means that you cannot extract it from
the number, as it is, so to speak, “built-in”.
So, actually, so far we haven’t been able to sum by currency codes.
However, I
found a method by which you can pinpoint which currency code “resides” in any “Accounting”-formatted
cell and thus sum these sums by their currency codes.
Step
1
Press CTRL+F3
to open the name manager
Add a new name
by pressing New
In the Name
Textbox write: Curr (or any other valid
name that you prefer)
In the Refers
to Textbox write: GET.CELL(7, Sheet1!A2)
Pic 2: Defining The Curr formula
Step 2
Select cells
B2:B21 and type =Curr in B2
Pic 3: Before entering the Curr formula in cells B2:B21
Step 3
Press Ctrl+Enter
This is what you are going to see in cells B2:B21.
Don’t panic 😊
Pic 4: After entering the Curr formula in cells B2:B21
If you look
closely at these cells (B2:B21) you’ll be able to notice that each cell displays
the number format of the adjacent cell on its left (A2:A21), including its
currency code.
Now we can
easily extract the currency code of each and every cell of the original dataset
in cell A2:A21
We hide
column B and use a simple formula to accomplish this.
Pic 5: Extracting the currency codes of cells A2:A21
Now we have
the currency codes, but alas, Excel does not let us sum up these numbers by the
codes in column C.
So I found two
bypassing methods to achieve the desired result: Summing up by currency codes.
Method
1
Excel does
not let us execute the SUMIF on cells C2:C21, but we can use the
adjacent cell to select only the currency code we need.
Column B is
hidden, but we don’t need it in order to know the currency codes it “hides”.
For each currency we want to sum, we can use the cell in (hidden) column B which
is exactly to the left of the code we want.
For example,
if want to sum only the $ currency code (which appears in C3 and elsewhere), we can select B3 as the second argument of SUMIF:
=SUMIF(B2:B21,B3,A2:A21)
Which yields
the correct result 😊
Pic 6: Sum only cells A2:A21 with currency code $
Another example,
summing only the Euro currency code €
Pic 7: Sum only cells A2:A21 with currency code €
Or summing only the Yen currency code ¥
Pic 8: Sum only cells A2:A21 with currency code ¥
Method
2
An alternative
method is using a Validation List, as follows:
Pic 9: A validation List in cell J1
It is
interesting to see that although the list contains 20 values, the Validation
list in cell J1, displays only unique values.
Now we can
use J1 as the second argument to the SUMIF formula:
=SUMIF(A2:A21, J1, B2:B21)
It is quite
easy to identify the currency code in each item of the list, so if the select
the first item (¥) the formula will yield
the sum of only Yen currency code numbers.
Pic 10: Using the validation List in cell J1 to sum only ¥
Or, if we want
to sum only the British Sterling Pound (£)
Pic 11: Using the validation List in cell J1 to sum only £
A-m-a-z-i-n-g!!!
Be sure to save the file an .xlsm (macro) file and not as an .xlsx file, since
the GET.CELL command (used in the Named Range) is a Macro 4.0 function.
Wish you a happy, healthy,
and prosperous year
אין תגובות:
הוסף רשומת תגובה