How To Get Total Of Multiple Currencies Used In One Column
Jan 31, 2014
A$ SING $ Euro NOK UK US$
1.07001.25000.74186.10000.62081.0000
A 934.58
B 800.00
C 1,348.07
D 163.93
E 1,610.82
F 1,000.00
G 1,869.16
H 1,600.00
I 2,696.14
J 327.87
K 3,221.65
L 2,000.00
TOTAL 17,572.23
Values in column "H" (US$) have been divided by currency rates in Raw C2~G2 as we need the total in US $
But we also want to know the total amount of each currency used in column "H".
How to put a formula to find the total value of each currency used.
View 2 Replies
ADVERTISEMENT
Apr 28, 2009
I have a list of about 2,500 entries containing information of all my works leave requests for 2009. This list contains more than one entry per employee, as it shows all their individual requests for different weeks. The list is compiled pulling through data from other worksheets. It looks like this...
Sheet Name: MASTER
Column A = Line Managers Surname
Column B = Employees Surname
Column C = Employees First Name
Column D = Employees ID Number
Column E = Week ending
Column F to L = Days of the week
Column M to O = Request Type
Column P = Number of days requested for the week
I then have another sheet (Called REQUESTS) with all the employee names (1 entry for each employee), ID numbers and total requests for the year. This looks like this...
Column A = Line Managers Surname
Column B = Employees Surname
Column C = Employees First Name
Column D = Employees ID Number
Column P = Number of days requested for the year
Column Q = Number of days requested for the summer
I know how to locate and add up all the requests for each individual for the whole year (Column P) from the MASTER sheet to the REQUESTS sheet by using the flowing formula for each employee.
=SUMIF(MASTER!$D:$D,D2,MASTER!$P:$P)
But I don't know how to ask Excel to find and add up all the total requests for only the summer period (Column Q). What I want Excel to do is this................
View 2 Replies
View Related
Mar 20, 2014
I've produced a workbook for my business where we can do automatic quotations based on a simple vlookup formula. We do however quote in a number of different currencies, ie Sterling, US dollars and the Euro. Is there anyway we can pick these conversions up by using data validation to convert the relevant cells or is there an other way?
View 2 Replies
View Related
Sep 18, 2007
I am mapping this file on the basis of a lot of fields. But the end product has 15 currencies. My requirement is to separate the accounts on the basis of the currencies and then consolidate it on the basis of GL account. For example, first I have to find all the accounts that are in USD and then if there are 2-3 similar GL accounts, then I will consolidate on the basis of the GL accounts. Please find attached file.
View 9 Replies
View Related
Dec 29, 2010
I am creating a simple spreadsheet to catalog some magazines. One of the columns shows the cover price which is okay for post 1970 magazines as it is in Pounds Sterling, but is there anyway I can get Excel to recognize Pounds/Shillings/Pence as currency. So put in "8d" for example without setting the field to a text format?
I want to be able to add up the total cost of some items based on their cover price but if I set the required cells to text format I cannot do this automatically using the Sum forumla.
Is it possible to have both Pounds Sterling and Pounds/Sillings/Pence as currencies in the same table or is using a text field for the latter the only option?
View 2 Replies
View Related
Sep 13, 2009
Sub sumbotton()
Dim ar As Range
Dim rng As Range
Set rng = Selection.CurrentRegion
Set rng = rng.Resize(rng.Rows.Count + 1)
rng.Rows(rng.Rows.Count).Select
For Each ar In rng.Areas
ar.Resize(1).Offset(ar.Rows.Count) = "=SUM(" & ar.Address & ")"
Next ar
End Sub
View 9 Replies
View Related
Feb 20, 2008
I have a list of dates and the exchange rate at that time e.g.:
18/02/20051.449921/02/20051.451322/02/20051.441423/02/20051.444624/02/20051.4445525/02/20051.45228/02/20051.4508
What I need to insert are three formulas, 1 that works out the average for the last year, 1 for the year before that and 1 for between two given dates.
View 9 Replies
View Related
Apr 21, 2009
I can arrange for a new row to be automatically added to a spreadsheet after I have sorted the currencies in order?
So basically I am trying to record a macro that sorts the column into currency order then after the currencies have been sorted I would like an extra row added so that I can enter a total field in between....
View 9 Replies
View Related
Jul 10, 2014
I have a payment Excel sheet with thousands of lines Every line (one cell) has row payments data from mainframes like the following
Lhfskldhflshlshdflsd 234 234 sdljflsdjfkl 345.000,56 EUR lshflhshlfsld 2342 sdffgsd
Fsdlflsdf -384823 jsdlfsdlfjsdlfjldfjsldjf 43 50.000,56 EUR fsdgdfgdsfdg 23432
Jlflsdjflsjdfld 569 USD fjsldjflslfhsdlhdlshg 3-448 jlfsdkljfklsd
Etc.
Is there a way (UDF or Macro) to extract from every line(cell) (and put them in the cell to the right) these raw data only the amounts with the Currencies.
PS. (most of the times the amounts mentioned before are the biggest number in every line!)
View 4 Replies
View Related
Jun 27, 2013
I recently moved to Central America and now am working on models that are either in $ or in the local currency (Q). I do not want to change my default currency value in the regional settings of the control panel because it is more convenient for me to stay in $ but it is a pain everytime I want to set a worksheet in Q and not in $.
So, I am wondering how I can change the default values in the currency drop-down list on the Home toolbar under Numbers in Excel 2010.
Currently, it gives me the change to apply the following currencies:
- $ English (US)
- £ English (UK)
- € Euro (€ 123)
- ¥ Chinese (PRC)
- fr. French (Switzerland)
Instead of either the £, ¥ or fr., I would like to have listed the Q, my other frequently used currency.
View 3 Replies
View Related
Aug 6, 2009
Please see the attached sheet. I have columns B through a lot (B through O in my oversimplified example). In every 7th row in each of these columns there is either a 1 or a blank/zero. I need to multiply that 7th number by the Quantity in column A, to achieve a total (ie the sum of each result of 7th cell*quantity) for each column in the bottom row, labeled "Totals".
In the actual version of my sheet, there are far too many rows to select everything manually. I've been fiddling with combinations of COUNTIF/COUNTA and OFFSET, but I haven't come up with a way to check for the 1 in every 7th row, THEN multiply that 1 by the quantity in column A, THEN add up the results for each column. As you can see, there are 1's elsewhere in the columns that are irrelevant to this particular calculation, so something like LOOKUP would also have to look in every 7th cell and couldn't just look at the column as a whole.
If you can't provide an immediate solution, but can at least point me to a resource that would allow me to devise a way to isolate every 7th row (THAT part is the sticking point), I'll surely post the solution to my own thread with updated keywords if I need it.
View 8 Replies
View Related
Dec 20, 2013
Formula(s) to do as explained in the attached example.
Example_formula.xlsx
View 9 Replies
View Related
Jul 28, 2009
I would like to format cells with a currency based on a value selected at the top of my worksheet. Cell C3 is a drop down of currencies. And I would like cells range E11:E200 to format in whatever currency selected in cell C3.
View 9 Replies
View Related
Mar 6, 2006
i have a payroll grid. it has 3 cells with drop downs that have 39 options.
there codes for payments. i need a fourth cell to show a total dollor amount based of the codes selected.
separate question. i have a list of cells with the same four options (Job Types.ie service call, new connect...) i want to limit the cells mentioned before so that payment codes that do not pertain to the job type can not be selected.
View 9 Replies
View Related
Jun 10, 2008
My userform requires a user to enter amounts in 5 different textboxes.(textbox1-5) I have created a textbox6 to attempt to capture the totals (should be numerical) of textboxes1-5, even if this textbox figure is a 0 or a minus figure. I have browsed a few other posts with roughly the same issue and have come up with some basic code as per below... the code is pasted into each (textbox 1-5) textbox_change() code.
If TextBox1.Value = "" Then Exit Sub
If TextBox2.Value = "" Then Exit Sub
If TextBox3.Value = "" Then Exit Sub
If TextBox4.Value = "" Then Exit Sub
If TextBox5.Value = "" Then Exit Sub
TextBox6.Value = CDbl(TextBox1.Value) + CDbl(TextBox2.Value) + CDbl(TextBox3.Value) + CDbl(TextBox4.Value) + CDbl(TextBox5.Value)
View 6 Replies
View Related
Aug 4, 2008
I have in Column "A" a list of the QUANTITY of items bought by different people.
I have in Column "B" a list of the COST of each of the items from Column "A".
I would like to be able to get a total of the # of items in Column "A" that would be bought at each of the different COSTS listed in Column "B".
Example: Cell "A-1" shows 11 as the QUANTITY of a particular item sold.
Cell "B-1" shows that the COST each of the items from "A-1" is $25.00.
Cell "A-2" shows 3 as the QUANTITY of items sold with cell "B-2" showing the COST each of "A-2" being $15.00.
I have attempted to do a "LOOKUP" feature, but I'm not getting anywhere.
View 11 Replies
View Related
Apr 24, 2008
see attached file.
I am looking for a macro that will look at the data and for each Item Description (Column C) I want it to sum the values in Column J and put in cells M1:Nxx. See example.
i tried using the autofilter and creating tables, but makes the sheet large in size...
View 14 Replies
View Related
Nov 16, 2011
I have one workbook in that 6 sheets have.
Here I want match the total one sheet to another but each sheet there is no fixed ranges so that I have confused.
How to find out Each sheet total and match each other.
View 3 Replies
View Related
Feb 10, 2009
below the following lists (Name, Week, Amount) I am trying to calculate a sum of the Amounts column (Column D) for two conditions: a particular week # (Column C), for a particular color (Column A).
Is there a simple formula to SUM numbers when it is based upon two conditions (color and week #)? For example, there are two separate RED amounts that should be received in Week 4 - how do I write a formula for the cell that correlates RED and WEEK 4 that states "sum the amounts for the Name: Red and Week: Week 4"?
NameWeekAmount
GreenWeek 1 5,000
RedWeek 4 13,000
BlueWeek 1 1,500
GreenWeek 3 3,500
GreenWeek 2 4,200
PurpleWeek 5 1,200
YellowWeek 4 500
GreenWeek 3 25,000
RedWeek 4 13,000
RedWeek 3 12,500
RedWeek 1 10,000
BlueWeek 5 9,000
BlueWeek 3 14,500
YellowWeek 5 3,300
GreenWeek 2 1,000
YellowWeek 4 30,000
Week 1Week 2Week 3Week 4Week 5
Red
Yellow
Green
Blue
Purple
TOTAL
View 9 Replies
View Related
Jul 31, 2009
I have ~500 rows of data in columns A, B, C that is as follows, for example:.............
I am having trouble with coming up with a formula that will add up the total items shipped for each item. For example, Apples = 62 items shipped.
View 2 Replies
View Related
Nov 5, 2007
I have a workbook containing 120 sheets. Each sheet contains a column labelled "Subject", and a row below labelled "Totals:" with a numeric value in the intersecting cell.
I need a formula that will total the value in all these cells on the last sheet.
The trouble is, the cell address of the intesecting cell fluctuates somewhat from sheet to sheet because the column and row for the "Subject" and "Totals:" are not always the same.
******** ******************** ************************************************************************>Microsoft Excel - Book1.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF30=
ABCDEFGHIJKLM5**Check*Date*01/05/2007*to*09/28/2007,*Job*616003*to************6**6440009,...,Job#*642010*-*HEATHER*GLEN@MONUMENT*BO************7*************8***Record#****Check#**Period***Employee*****9****Comp*Code*********Hours**10*************11*************12*************13*Totals*by*Comp*Code:*************14*Comp*Code*************15*****Hours****Wages***Overtime**Subject***Rate*16*************17*5183***PLUMBERS*UNDER*$23*************18*****19.00****376.50****376.50***11.5400*19*5187***PLUMBERS*ABOVE*$23*************20*****5.00****120.00****120.00***6.5300*21*************22**Totals:***24.00**496.50*0.00496.50**Sheet8*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I want to be able to total the "Subject" wages for codes 5183 & 5187 (unfortunately, the payroll amounts are located one row below) for all 120 sheets in the workbook.
View 9 Replies
View Related
Jun 24, 2014
[URL]
Now what I need is a formula similar to the one provided in the above referenced thread; however, I need it to sum the totals of all Children (C) from column BW in the attached excel file, and return the sum values found in BW to the corresponding employee (E), before the next E occurrence, or blank, if the row below the last row containing a C value in row M in blank.
refer to the excel file, specifically the row highlighted in RED, and the explanation to the right of it, for further clarification on what I'm needing the formula to do.
Ultimately, I have a census of employees with rates, and some employees cover their children, and I'm needing a formula that will be able to total up the monthly premium for each employee's covered children, and bring that total up to the same row that the employee's data is located.
View 14 Replies
View Related
Jul 15, 2014
I have a formula in sheet code to get a total of the following columns. The formula in these columns is not working. I'm not getting any error. The code is working fine without a require output.
Attached File : Total.xlsm
View 4 Replies
View Related
Aug 28, 2007
I am trying to create a worksheet with a total to date column in it. I have done a similar thing before using Nested IF's but obviously there is too many IF arguments for that in this circumstance.
Please take a look at the .xls page I have uploaded. The period displayed in cell AP5 is linked from another sheet where the user selects the period from a Data Validation DropDown box. The periods are as follows: January - March2008, April - July2008 etc etc.
You can see how the periods are formatted across the top of my main worksheet I wish to be able to take the period provided and then get a figure of the total to date for each row within the "Total to Date" column based on the figures upto that period. If you get what I mean?
View 12 Replies
View Related
Apr 22, 2009
i am setting up a material iventory using excel 2007 i require a worksheet to contain a column for stock in (+) and stock out (-) and a running total column, but i am struggling to create a formula for this. my column's would be (J) for stock In (k) for stock out and (L) as Running total
View 9 Replies
View Related
Apr 5, 2007
I've got an excel spreadsheet that is using imported figures from another source. I'm just trying to create a macro that will locate the last cell in Column N, Then will total all the cells above it. I've got the below code to select the last cell in Column N.
myrow = Range("N1:N" & Range("N65536").End(xlUp).Row).Count
For i = 2 To myrow
Range("N" & i).Select
But I can't figure out how to do the sum part. I've tried to record a macro but to no avail. Since the number of rows will change each day, I need to somehow catch the range. This is part of a bigger project, but if I can get this part working, I think I'll be home free. Attached is a small example.
View 6 Replies
View Related
Jan 15, 2008
I have 2 columns that are created as part of VBA code, A is a list of names, B has a corresponding set of numbers. I need to add some code that will remove the duplicates in column A and sum the values in Column B.
for example
A....................B
Dog................ 1
cat ................ 2
bird ................ 1
dog ................ 3
Would return
A .................... B
Dog ................ 4
cat ................ 2
bird ................ 1
View 3 Replies
View Related
Feb 26, 2009
I'm creating a spreadsheet that has:
- budgeted dollar amount
- entry each week for that week's total expense
- total expense to date
Question: how can I hold the value for "total expense to date" so that when the new week's info is added the previous total is not lost?
View 13 Replies
View Related
Jun 12, 2014
i am looking for formula in my macbook pro excel where i have purchase apple 50kg for $50 and grapes 20 kg for $30 and orange 10kg for $15 and am doing this daily and in end of month i want to see separate kg's and amount for whole month and later year of apple, grapes and orange without sorting it
View 1 Replies
View Related
Sep 29, 2009
i'm tracking hospital census with days of month in columns and patients 1 - XX in rows. this is basically what i'm working with.
A...........B........C...D...E...F...G...H...I...J...AG...AH
Patient...Payor...1...2...3...4...5...6...7...8...31...Total
1...........MCR.....x...x...x...x...x...x...d...............6
2...........COM.........x...d..................................1
3...........SP............s...s...s....s...d....................4
4...........MCR.............................x...d..............1
5...........MCR.............................x...x..d...........2
Total.................1...3...2..2...2...3....1..0.........14
The "total" cell formula is =COUNTIF(G7:AK7,"X")+COUNTIF(G7:AK7,"s")
S and X just identify where that patient is med/surg vs. skilled.
I'm looking for a solution that will reference column cells B2-BX and add the total from that row (AH) if the payor of that row is MCR, then another cell that would do the same for SP, COM and so on. so in this scenario it would be something like
COM...7.1% (SUM col AH where col B = "COM" ie. 1/14)
SP......28.6% (SUM col AH where col B = "SP" ie.4/14)
MCR...64.3% (SUM col AH where col B = "MCR" ie.1/14)
View 8 Replies
View Related
