I have a worksheet that contains codes in column A and values in column C
The last value in in cell C50. I want to add all the values in column Code, but exclude any value where the codes in column C contains 50, 51, 55.
for eg if the code is 5000, 5001, 5100, 5105, 5500, 5501, 5510 etc the value must be excluded.
I am trying to do is when a user enters in a GL (a 4 digit number) from the list (tab 2) I want it to appear in the summary box below. However the same GL number can be used multiple times so I will have duplicates. The data entered above will always change there is over 200 possiblities the user can enter. So I don't want the data to be specific it will populate based on what is entered by the user.
Can this even be done?????
Say if the user entered:
Column C Column G
4606 $20.00
4606 $20.00
2134 $15.00
2301 $35.00
4606 $100.00
The data in the GL and currency column in the summary box would appear as the following:
Column C Column G
2134 $15.00
2301 $35.00
4606 $140.00
I am trying to automatically add up data, excluding that last two data points in a row. And every month add the next data point based on the date of the spreadsheet (top left). see trail below... This sting is going in circles.
View 14 Replies View RelatedI've been asked by one of our Managers to generate a report on stock that hasn't been sold for x amount of time. Unfortunately with our accounting package the report I run also includes stock that we may have purchased recently but have not sold.
So what I did was generate a report for stock that hasn't sold in the last two years(NOTSOLD), and then a report for stock that has been purchased within the last six months(PURCHASED). I've put both of these reports into two worksheets.
What I'd like to do now is match up stock codes across the two worksheets and exclude the PURCHASED stock from the NOTSOLD stock.
My Excel knowledge is limited but to put it in beginners terms I'm basically looking to do a reverse VLOOKUP.
I am creating a quote sheet that will have data entry for: customer name: company name: (Can be blank if customer is private)
Customer billing address
street:
City:
Postal code:
[Code]....
I've got set of data in columns B, E,H,K, N, Q, T and W. Some of columns are with if-error and some are not (Attachment). I want to find the average excluding if-error data columns. Another way, columns with if-error data should not be counted when average is calculated(In column X).
View 3 Replies View RelatedI need VBA code that will do the following:
1) Clear all data in Column F , excluding formulas (eg =sum, =F9+F10) & Text
2) Where a value begins with an equals sign for eg = 18000+50000-15000, this must also be cleared
3) The Data must be cleared from row 9 onwards in column F.
In A1 to A10 I have numbers 1 through 10.
In B1 to B10 I have some of the numbers listed from column A, but not all, and in no particular order.
In column C, I would like to list only numbers from A that are not listed in B.
I am applying the sum function to a range of cells in a column. I've Auto Filtered my sheet to only include the data I need. Unfortunately, the sum function is adding everything in that column, even data from the 'hidden' cells that I've filtered out. I need to know what condition or parameter to apply to the sum function (or any other function for that matter), that will only add the data shown on my screen. Not the data that is hidden.
View 2 Replies View RelatedFirst I will post the data:
Weekly Roster
F98AKHTAR99DEEPAK100NAPA'A101OUKASH102ROBINSON103RUSSELL104SALUNI105S
PARTIATIS106SPICE107TAN108TORRES. Jr109VAN STEEN110ACCIARITO111112113114115116117118119120
In this data, as you can see there are a lot of cells/rows that are empty. I have a macro that will copy the cells F98:F120 to F99:F121 and then copy F121 to F98, and finally delete the value from F121. In this case, it will give me a blank cell at the top and the data hasn't rotated really. All the names will be in same position.
But I DON'T want that. I want the macro to look for the last cell/row with a valid value in it and rotate the cells so that I end up having ACCIARITO at the top and everybody else to move down one row.
I have a list generator that creates a set of data in a multi-column & row dataset. I would like a formula to create a list of the alpha data points only which excludes blanks and any errors.
Example:
AADD
#N/A
BB23EEFF
#N/A
#N/A12GG
CC
Results:
AA
BB
CC
DD
EE
FF
GG
I'm pretty new to Excel and trying to create a spreadsheet for a friend. For the part that I'm working on right now, I have a cell to track donations received for every day of the month for every month of the year. Donations can be split into three categories (Field trips, Speakers, and Misc), so next to the cell for the manually entered $ amount I have another cell that provides a drop down menu to select from these three categories.
What I would like to do is have three cells at the bottom of the page that will total the $ amounts for each category. I've attached the page that I'm referring to.
Attached File : Example.xlsx‎
The formula is in C4, and I am trying to sum the data when the value of B4 is matched in the table. see attached.
View 4 Replies View RelatedI’m trying to get the total number of participants in two different sets, with selection criteria attached to both (I’ve attached an excel file, as the example with those scoring +ve highlighted in yellow). The first set A (b1:b15) I want to select all values above 5, and the second B (c1:c15) set above 10. There other provisor, is that the A and B are further split into two groups (one above and one below 20).
In summary, I need to count all the A>5, B>10 (And both have to be above 20 in column D), but not to double count the ones that have Both A>5 and B>10. I don’t want any duplicates to be counted, so summing the total of A and adding it to B won’t work. In essence it’s an attempt to use the A Union B function that you’d observe when using venn diagrams.
Is there a simple way/formula of doing this?
How to sum numbers from multiple columns that match certain criteria.
My spreadsheet is a funeral team tracker and it's only the 1st day of the month, Each corresponding day will eventually have it's own tab once I nail down this first one.
Basically the page is laid out into 6 different details a team member can do. A team member gets their hours from the "Total Detail Time," that gets calculated from the show time til the return time. The team who worked the detail gets added to the bottom of the Detail they worked. I am just trying to total the "total detail time" each time a member works it. So, a member could work say Detail 1 and Detail 3, so I would just want to sum the total detail time from those two days where the member is shown.
I was trying to use =SUM(IF((Detail1,N13,B10)+(Detail2,N13,D10)+...
Note: I have range names for each bottom portion of the details to shorten the formula (i.e., Detail1, Detail2, etc.)
[URL] ...........
How do I edit the VBA so that the 1st data cell is selected, and then the program calculates the sum of the data values, and then stop summing when I reach end of data?
View 5 Replies View RelatedOn the "CurrentCustomer" worksheet tab I am trying to accomplish the following:
When the merged cells in the F column (F3, F25, F47...F443) equal the word "Contract", I need the corresponding total cells in the E column (E23, E45, E67...E463) to add up, if and only if the corresponding F column merged cell equals "Contract".
For example, using the first three ranges, if F3, F25, & F47 all equal "Contract", I need the corresponding E23, E45, & E67 to add up. If F3 & F47 equal "Contract", but F25 equals anything other than contract I need E23 & E67 to add up only.
I want to sum certain data, which meets two conditions.
My data set contains three columns and a lot of rows. The columns are the following:
PostalCodeDeparture
PostalCodeArrival
PassengersInCar
I want to sum the total number of passengers with departure postal code 5100 and arrival postal code 5110. (and I want to do the same for all other postal code combinations in the data set)
With "SUMIF" I can only include one condition.
I have a set of data maintained in a number of tables within an Excel Worksheet.
All of the tables are the same layout and occupy the same Columns.
I want to summarise data from certains Rows within these tables at the bottom of the Worksheet.
Is there a better solution than simply doing something like?:
=A1+A11+A21+A31+A41
I have a lot of paperwork that needs to be entered into Excel. When I finsih entering the data, there will be many workbooks containing many different worksheets. I know how to summarize each workbooks but is there a way I can add specific columns in each of those summary sheets into a new workbook? I guess the question is can I sum data from different workbooks into a new workbook?
View 5 Replies View RelatedSee attachment. For example, imagine data in cells A2:N25000 (obviously, this example in the attachment has been shortened). At this point, also imagine that rows 7 & 8 did not exist. So, there is a continuous stream of data for vaious counties in A2:N25000. For the purpose of context, the data contain mortgage information for all the lenders in a particular county for an entire US state.
Is it possible to create a macro that would insert two rows after each county in the data range (e.g., rows 7 & 8 in the attachment)? In other words, in the first row a macro would insert a row (row 7) that sums the results of the top 25 lenders in each county. Then, the macro would insert a second row (row 8) that sums the results of ALL the lenders in each county?
I have a set of data that I would like to break into groups, but I do not know what the groups are. I would like Excel to help me find the groups.
More specifically, my tax data consists of the following columns (I'm simplifying): parcel number, dollar value, tax amount, days late paid.
123435, $12000, $100, 20
234234, $23000, $230, 05
etc.
Of course my Excel "results" would omit the parcel numbers, but it would propose groups (and how many parcels in each group) such as: ...
I have a summary tab in which I am trying to sum data based on specific variables from 30 other tabs in the worksheet
- In the summary tab, I have months (one year worth) over the top row and consultant names in the first column
- Each other tab represents a project
- I want for consultant 1 in summary tab, to have:
For january, the sum of dollars spent in january in each project (so across all tabs)
Same for february to december.
I have attached an exemple Note that the consultant names will always have the same syntax but will not necessarily be in the same cell in each tab.
I'm looking for a formula that would look up the individual employee (i.e. Name1 for example) in the January sheet and then look for and sum any amounts that have a number and a symbol as reflected in A2 through A10 (8V, 4Vâ–²,etc), which would then be calculated with a similar formula for the February sheet, specifically the time period between 1/26/14 and 02/08/14 as indicated above.
As employees can switch shifts, the formula would have to locate now in February where employee (Name1) is and find that row for the same symbol/number calculations for this same time period in that given month. Currently I'm using an array formula which can be seen in the attached file in cell I4. The problem is that the formula assumes that Name1 is always in the same spot in all monthly sheets which isn't the case month to month. The formula looks like this without the vlookup portion:
[Code] .....
I also need to do a similar vlookup or match, etc with this formula too:
[Code] .....
Attached File : sample payroll.xlsm‎
I shall use a basic example to illustrate my problem - see attached - as the spreadsheet im actually working on is huge and contains sensitive data. So, In the spreadsheet attached, the problem is: I am mowing the grass of a football pitch and getting paid by the m3 of grass i mow. however i'm getting paid more when i mow at greater distances - defined by certain 'reaches'. so i need to know how many m3 ive mowed in each reach. I make a note of where I start on the pitch and where i end, and also how much grass i mow.
so im looking for a solution that is intelligent enough to distinguish where Ive taken grass from and how much, given that i know where i started, where i finish and how much i take.
Is there a way to SUM the result of the followig for B4:J4 and B16:J16, the other cell references remain static:
=(B28*(P28^2-IF(ISNUMBER(B16),ABS(B4-B16)^2,B31)))
I a currently using the following which is rather lengthy and not efficient to maintain/edit:
=(B28*(P28^2-ABS(B4-B16)^2))+(B28*(P28^2-ABS(C4-C16)^2))+
(B28*(P28^2-ABS(D4-D16)^2))+(B28*(P28^2-ABS(E4-E16)^2))+
(B28*(P28^2-ABS(F4-F16)^2))+(B28*(P28^2-ABS(G4-G16)^2))+
(B28*(P28^2-ABS(H4-H16)^2))+(B28*(P28^2-ABS(I4-I16)^2))+
(B28*(P28^2-ABS(J4-J16)^2))
formula that will find "John Doe" through sheets 2-10 and check the Win column for a "w"..if a W exists, Sheet 1 will calculate numericaly how many times it shows progressively, but only for his name and not others.
Sheet 1 B7=John Doe
C7 = total W's though sheets 1-10 for John Doe
Sheet 1
B7 C7
John Doe total W's
[Code]...
This is a alteration to an existing question I asked 2 Days ago, I redesigned a new spread sheet to start over with a new approach to it.
I have the following sheet with reg hours and ot hours. I need to insert a column that sums these two items. I used the macro recorder and it only works when the same amount of ID's or less. If there are more ID's it does not sum the remaining ID's. What it currently looks like:.........
View 3 Replies View RelatedI have circa 500 sheets (all with identical layout) in the same workbook. All the data is displayed in Columns "A" and "B". The entries in Column A are variable (5-6 digit numbers) B contains numbers between 1 and 200.
I would like to be able to add together the sum of the numbers in B against an entry in A- I have a list of all the entries in column A.
The data in column A is in ascending order but may not always be in the same cell reference.
E.g. "123456" and appears in 3 sheets
(sheet2) A9 ="123456", B9= 5
(sheet 234) A111="123456", B111= 7
(sheet 456) A87 ="123456", B87=3
"123456", total 15
I have a table of data: - In cell A1: to C7
A B C#Group Aug-091Merchant Bar2002Plate3Structural - HR1004RHS5Plate3006Structural - HR300
What I would like is a formula that sums up column C if Column B of that row is "Plate" for example