Formula To Put An Amount Into A Cell Next To Only Unique Item
Col A contains SS#'s and Col B contains names. Quite often there are duplicates and sometimes triplicates because they may be sending payments for multiple providers. We charge one fee per client (unique SS#), regardless of the number of times they may be on the spreadsheet.
I can get a grand total by summing the unique items by using this formula:
=SUM(IF(LEN(A2:A4970),1/COUNTIF(A2:A4970,A2:A4970)))
and then doing a CTRL-SHIFT-ENTER to put brackets around it so it will "take."
However I now need to show the fee by each unique name. Right now I am inserting Col C, typing in 3.00 into C2 and copying on down, but then have to manually remove the amounts in the rows which contain duplicate ss#. Long painful process. How can I put a formula in Col c so that it will only show fee one time by the first unique ss#?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Count Duplicates As Unique Record, Sum Amount
I have a spreadsheet will a large amount of invoice numbers, some of which are multiple occurrences of the same number. I need to count the duplicates as one unique record and sum but I need to sum the total $ amount of each amount attached to each occurrence. Please look at the sample to see what I mean. W234678 has three amounts that are added to give a total amount for that number and it is added to the count as one record. I had this code kindly borrowed from someone else which helped me find the duplicates but it is not meeting my needs. Dim rCell As Range, rRng As Range, vKey, lrow As Long Set rRng = Range("F2:F199") With CreateObject("Scripting.dictionary") .comparemode = vbTextCompare
View Replies!
View Related
Cell Value (number To Change If I Put A Formula In)
I have a cell containing a mixed text and number value e.g wk16 and would only like the number to change if I put a formula in. For example, if i change my master cell A1 to wk15, I would like A2 to automatically change to wk18 or wk12 etc depending on the the formula Every time I try = A1+3 I get #VALUE! appear in the cell. I cannot change the cell so it contains a number as I am using Lookup functions.
View Replies!
View Related
Find Column That Has Value And Put Formula In Cell
I had posted this earlier but it was moved. I think this post will explain it better. I put data into a worksheet called "Budget" each week. When I put the data in I type a date in Cell "BL7". In another sheet called "EV Report" row 8 has week ending dates (i.e. Sunday of each week). I want to run a macro after I change the date ("BL7") and data in the "Budget" sheet that will search row 8 of the "EV Report" sheet to find the date in cell "BL7" of the "Budget" sheet. Once it finds which column that date is in I want it to put a formula in row 13 of the same column (the one found based on the date of BL7) that will add the value in row 12 of the same column with the value in row 13 of the previous column. I don't want the formula to be there until the macro is run. I'm doing this because I have a graph of the formula and I don't want the graph to show the other cells.
View Replies!
View Related
Formula To Show A Date Range Based On The Amount Shown In Another Cell
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc. I added a new cell called Track Back on the employee search sheet. What I want to do is only show variances for the amount of days back selected in the Track Back cell. For example if I select the last 30 days, only the last 30 days would show up below in the sheet. I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
View Replies!
View Related
Reference Cell & Add Amount If Positive & Subtract Amount If Negative
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do: I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
View Replies!
View Related
Capping Amount In Formula?
is there a way to create a formula that caps at a certain amount? i am trying to create a spreadsheet for a group of doctors and their insurance coverage. i want to display that a certain amount of coverage caps at a certain price over the course of a number of years. so basically, can i create a formula that automatically adds the data and max's out at a certain number?
View Replies!
View Related
Put An IF Formula Together?
I am trying to put an IF formula together so that if: between the range of G18:I58 there are either of these words displayed: "Recommended" or "compulsary" then the cell containing the formula will display "Your Journey starts here". if all the cells in the range are blank then the cell containing the formula is also blank.
View Replies!
View Related
Formula To Zerorize The Amount That Is Within The Threshold Value
my data will look like table below: amount (A1) threshold(10) (B1) threshold(15) (C1) threshold(25) (D1) 9.80 0.00 0.00 0.00 0.26 0.00 0.00 0.00 0.53 0.53 0.00 0.00 0.40 0.40 0.00 0.00 0.77 0.77 0.00 0.00 1.20 1.20 0.00 0.00 0.33 0.33 0.00 0.00 3.40 3.40 0.00 0.00 5.67 5.67 5.67 0.00 4.00 4.00 4.00 0.00 1.20 1.20 1.20 1.20 3.10 3.10 3.10 3.10 0.53 0.53 0.53 0.53 1.73 1.73 1.73 1.73 i need a formula to zerorize the amount that is within the threshold value & just display the same amount as input if exceed. column A is my input, colum B,C,D is my working area where there are few threshold value. for example, the B column give the threshold value = 10, thus cell B2 & B3 were zerorize. ( not B2 only)
View Replies!
View Related
Adding 1 More Item To This Formula
Here is my current formula: =IF(ISERROR(INDEX(Sheet3!$D$5:$D$200,MATCH(E5,IF(Sheet3!$C$5:$C$200=C5,Sheet3!$A$5:$A$200),0))),"0",INDEX(Sheet3!$D$5:$D$200,MATCH(E5,IF(Sheet3!$C$5:$C$200=C5,Sheet3!$A$5:$A$200),0))) I would like to add one more item to match. I need to match column H in my current sheet, to that of column E in sheet 3. Just not sure how to go about it.
View Replies!
View Related
Put A Picture Into A Formula
I would like to find out how to put a picture into a formula so that when: example if A1 equals a certain number then a picture will pop up on the worksheet. So in otherwords - a picture will automaticly pop up - so I need a formula to include the picture.
View Replies!
View Related
Delete The Formula In The Amount The Unite Price Comes #value
(To keep things simple from left to right Column A-H) The Amount Column seems to be my problem, it has the formula =H98*B98 just a simple multiplication formula to get my unit price x my qty. When I delete the formula in the amount the unite price #value! error goes away. and all that is in the other error box is =IF(P98>0," per piece","") it just puts "per piece" in the box when something is typed. I have a vlookup formula in Column F (thank you VoG) =IF(ISNA(VLOOKUP(E98,Products!$A$34:$I$81,9,FALSE)),"",VLOOKUP(E98,Products!$A$34:$I$81,9,FALSE)) To pull prices from another worksheet.
View Replies!
View Related
Formula To Subtract Invoices From Total Amount Available
I have 2 tables, one with invoices, the other with purchase orders. I would like to have a "PO Amount Remaining" column on the invoice table that looks up the PO listed on the invoice with the PO $ amount on the PO table. Once it is matched, I would like to subtract the total invoices to give me the amount of the PO that is left. Basically I would like the "PO Amount Remaining" column right now to have $4,200 listed in each row for invoices 1 & 2.....
View Replies!
View Related
FORMULA TO ADD AMOUNT BASED ON TWO Or MORE CONDITIONS
i need a formula to add amount based on two or more conditons. From the drop down list on each change i want to do the sum to be displayed in C18; see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas. Also tell me any other array formulas like this which are useful.
View Replies!
View Related
Formula To Add Amount Of Rows In A Column
I know a bit about excel nut not a hugh amount! i need to know a formula that adds the number of rows used in a column. Does that make sense?? ok NAME rob dave jim darren adam lee i need a formula that will tell me there is 6 rows used in that column (not including the title).
View Replies!
View Related
Count Formula One Record With Amount Is E, F, &/or G
Need count formula to count records with amounts in either columns E, F, or G. For example Need a formula (not VBA) in cell F2 to return a count of 5 records counted that have an amount in column E, F, or G (but only count as one record when amounts exists in multiple columns): __|____E___|____F___|___G___ _7 | 1200.62 | 1500.53 | -0- _8 | 1000.00 |________|_1620.00 _9 | 7000.00 |________|________ 10 |________|________|________ 10 | 2000.00 | 3000.00|________ 11 | 8000.00 |________|________
View Replies!
View Related
Formula That Calculates The Total Rent Amount For Particular Dates
I need a formula that calculates the total rent amount for particular dates. Example would be a formula that includes a date starting at the first of the month (October 1, 2009 to September 30, 2010) and also would include a date starting at a date later than the first (October 3, 2009 to September 30, 2010). The figures needed would be the monthly rent (ex: $1,000) and a pro rated rent amout for a lease that starts after the first of the month. The pro rated amount would also have to include the years with a leap year. I have been working on this but I know there has to be an easier way.
View Replies!
View Related
Summation Across Of A Variable Amount Of Columns VBA Formula
I'm trying to enter the summation formula into three columns that vary in placement based on the value of num_objs. Basically, the number of grey columns in each of the three sets will equal num_objs. I want the user to be able to enter values into the grey columns in the middle section and the values of the two rightmost white columns (with '0's in them currently) will change accordingly. My current code is below. However, I keep getting a compile error saying "Expected: end of statement". I've been searching for a solution online for a couple days now, and I don't see how my code is different than what I've been seeing of other people using variables in their formulas.
View Replies!
View Related
Formula Doesn't Provide Amount As Expected
My sheet called 'Report' finds a type using a vlookup. I then have a sheet called labor, where I want to find put the total amount for labor using this formula: '=SUMIF(Report!$K$2:$K$65000;LABOUR;Report!$G$2:$G$65000)', but it only returns a '-'. I want the formula to return total amount from column G, if column K is type 'LABOUR'. Is my formula incorrect?
View Replies!
View Related
Formula To Calculate Percent Change, Varied By Amount Of Months
I need to figure out a formula for cell F17 that will calculate a percentage change only for the months that have data in 2009. The way it is set up right now I have to go in every month and change the cell reference of the formula to include the latest data. Since the 2008 data is totally populated the formula gets messed up if I include the months of 2009 that have not yet occurred.
View Replies!
View Related
Put A Formula In The First Worksheet That Will Compare The Property Numbers
I have a worksheet with property numbers in colum A and first names in column B. Another worksheet has property numbers in A, first names in B, and last names in C (different order, all mixed up) I want to put a formula in the first worksheet that will compare the property numbers and when it finds a match put the last name from the second worksheet into the first. Does that make any sense? I have tried vlookup but i'm obviously doing something wrong. example below.... # first last 123 x 124 y 125 z another sheet looks similar but has info in the "last" column. the numbers between the two sheets are not in the same order so i need to search by number and put the info from the second sheet into the first.
View Replies!
View Related
Index/Lookup Formula (dont Match Put 0 Instead Of #N/A)
=INDEX($AS$9:$AT$33, MATCH(B4,$AS$9:$AS$33,), MATCH("PERCENTAGE",$AS$9:$AT$9,)) That's all fine but some of the items in the list will not be able to match the lookup table and in this case I want it to say 0 (zero), but instead its saying #N/A which I understand means data unavailable. Is there a way of saying if so and so doesn't match put 0 instead of #N/A?
View Replies!
View Related
Formula (retrun The Right Cost For An Item, Depending On The Delivery State)
I have 3 different costs for an item, 15 different items. The costs very depending on the delivery state. Is it possible to write a formula that will retrun the right cost for an item, depending on the delivery state? if ap10 = "CA" then look up in range AH2 to AH18 if ap10 = "GA" then look up in range AI2 to AI18 if ap10 = "ME" then look up in range AI2 to AI18 if ap10 = "PR" then look up in range AI2 to AI18 if ap10 = any other state then look up in range AG2 to AG18 cell AP10 being the cell that the state is listed in Cell U10 being the cell that item is in (a drop down menu) This is the vlookup formula that will get one price. =IF(ISNA(VLOOKUP($U10,TABLE!$AF$2:TABLE!$AI$18,2,FALSE)),"",VLOOKUP($U10,TABLE!$AF$2:TABLE!$AI$18,2, FALSE))
View Replies!
View Related
Sum Formula: Count Of How Many Gallons Of Gas I Put In Car Each Week
I need to be able to keep a running count of how many gallons of gas i put in my car each week. Each week is one column. Column A is where i want the total to show. column B,C,D,E...etc is where i put the numbers in this is all in row 1 for now. currently i have =sum(B1:BB1). But something is not right because it is not adding the numbers together it will only add what is already there not any numbers that i put in after the formula is made. Do i have the wrong formula or something else wrong. My goal is to see how many gallons i put in at the end of the year, month, quarter, and so i have several other reason for this info.
View Replies!
View Related
Check Each Cell In Range For Value & Put Text In Offset Cell
I'm trying to look at a value in one column (Column A) and if it matches a set of criteria, I'm trying to change the value of another cell 6 columns to the right.(Column G) I've been trying for a couple of days now to get this working, but to no avail. I've tried to modify the code below that I've found on this site but can't quite figure out what I'm doing wrong. Sub RatingsFix1SP() Dim FindWhat, rngCell As Range, i As Integer FindWhat = Array("BB", "B", "CCC", "CC", "C", "CCC+") For i = 0 To 3 For Each rngCell In Range("A2", Range("A" & Rows.Count).End(xlUp)) If InStr(rngCell, FindWhat(i)) <> 0 Then rngCell.Offset(0, 6) = 0.15 End If Next rngCell Next i End Sub To try to clarify a bit: If column A in the attached spreadsheet has a rating code of BB, B, CCC, CC, C, or CCC+, I want the Haircut % rate in column G to be 15%. It seems the code that I'm using changes the % rate to 15% if any "B" or "C" shows up in any part of the string in column A.
View Replies!
View Related
Cell In Any Row Has A Color Of Red, Put A Yes In The Cell
I have a worksheet that has grey cells and white cells. Whenever I have an error in any cell in any row or column throughout the worksheet, the cell is colored red. This would be for things like; missing data, incomplete data, wrong data etc. One of my columns is named "Problem" which is NEVER in a fixed position, so the macro MUST look for this column by name, rather than by column position. This column has no data in it whatsoever. This column is there in anticipation of this macro. What I would like is for Excel to put the word "error" in the "Problem" column in the same row that a red cell/cells are found. Regardless of which column or columns the red cell/cells are in.
View Replies!
View Related
Put Both Text And Cell Value From Another Sheet In A Single Cell..
I'm trying to put both text and cell value from another sheet in a single cell. I've tried: ='sheet'!cell"text" Doesn't work. Also, I understand this is somehow it's supposed to be done ="Total: "&A12 But I need the cell data to be from another sheet and also I need the text to succeed the cell value. Going by the above example, I tried: ='sheet'&cell"text" But that also doesn't work.
View Replies!
View Related
If Statement To Accumulate An Amount In A Cell
In a cell I have a description, say "FOOD" then another cell with amount say £2, then in another cell I want it to be looking and if it sees "FOOD" entered, it takes the amount £2 and adds it to a running balance. Cell A1 = Food Cell B1 = £2 Cell H1 = (looks and if Food) adds £2 to cell
View Replies!
View Related
Putting In A Minimum Amount In A Cell
I have a row that is a percent of sales with this: =(F4*0.2+G4*0.1)*I4 "I4 will either be 1.0 or 0.5" Now I want to have it so if this equation equals less than 100 with I4 being 1.0 to equal 100 OR if I4 is .5 then it should be 50. I have looked all around and I am no excel wizard so this is troubling me significantly.
View Replies!
View Related
Limit The Amount Of Characters Per Cell
Is there a way of limiting the amount of characters you can enter in each cell? Also is there a way to apply this to an existing sheet that has cells with more characters in it than I would like? ie. If an existing cell has 25 characters in it, could i cut it down to the first 10?
View Replies!
View Related
|
TRACKER
