I need to get the sum of a column where two conditions have to be met. I have tried doing something like: Sum(Sum(AND(V1:V1500="BEBLT", H1:H1500=17) +-Sum(AND(V514:V1512= "NVLIV", H514:H1512=17)))
In a nutshell I want to find the difference between the sum of a column whose criteria is 17 and BEBLT and the sum of another column whose criteria is 17 and NVLIV. This has been extremely frustrating since I am using SQL commands to pull the info from the workbook. Typically I would use
"Select SUM(Z) from Sheet1$ WHERE (H1:H1500=17 AND V1:V1500);"
Unfortunately for me my SQL command brings back an error so I must resort to finding the total in excel and then using the command to bring it into my VBA.
What I have is 4 columns of data broken down as follows Column A = Date Range B = Name C = City D = Amount of People.
What I want to achieve is to be able to go through my list of data and for all rows that match specifically matches A,B,C it will total column D and output to a cell. I also need for it to reflect the name somehow .. so in attached example A2-C2 matches A8-C8 so I would need the output of D8 + A8 (which should be 54) but I need for this to some how reflect a name combination City + Name. So I would know that abc & California have 54 people and that def and New York have 56.
I have attached a basic spread sheet of data; keeping in mind that in the complete data there could be hundreds of combinations for data to be matched.
I want to write a sub that captures the existing lists in the attached file in two arrays then create two new arrays of customer names and amounts spent for customers who spent at least $500. After these arrays have been filled, I want to transfer their contents to the columns D and E.
I am trying to include a series of vlookups to find products by a product code from a database, an example is attached.
I want to type a code into the box at the top and find the results containting but not uniquely being the input.
eg typing 15 in automatically finds 15 using "=VLOOKUP(TRIM($C$6),'Main Database'!1:65536,2,FALSE)" but my database also contains 15eurd and 15rf etc and i want these to appear too.
can i do this with vlookups with some clause that excludes a previously chosen entry?
I have eight locations that appear on a single spreadsheet/Text file. I need to separate based on the Property ID for each location and then paste the result into a separate sheet for each one. I have arrange the code to look for each property ID and then copy the found data, but I am having a problem. The first find and Copy drops the first row but adds two to the end, so I am getting data for the next company. The second problem is that the second set of found data is appended to the size of the first found set. ( The first set is 3,570 rows, the second set is 2,646 rows. When Resized 6,216 rows are copied ) I can't seem to figure out how to reset the found range.
Sub Test() Dim RngSize As Long Dim srtHere As Long Dim endHere As Long 'Find The First company RngSize = Cells.Find("337x2", [B1], , , xlByRows, xlPrevious).Row 'Select columns to copy by resizing the found area to include additional Columns Set b = Range("Test").Find(what:="337X2").Resize(RngSize, 9) 'Copy Found area to the destination........................
I've inherited a reporting process which I need to convert to Excel but my knowledge of excel functions is fairly limited. I've attached a sample worksheet which sets out exactly what I'm trying to achieve in the comments. However to summarize here I need to add the data which meets set criteria together and display the result on a different worksheet. The problem I'm having is that the "criteria" the data needs to meet is dependent on info from other cells.
For example I only the totals of the data in column D where the date in column A is 01/01/08 and the data in column E is 1 and the result is to be displayed on a separate worksheet if that makes sense. It's difficult to describe without seeing the example workbook.
I have a "SUMMARY" sheet and then five other sheets. I need to be able to extract the data which is >0% from each of the individual sheets and capture the results in the relevant section of the "SUMMARY" sheet.
For example, the data from sheet "PAYG" would need to be entered into cells F148:AJ155. In each instance it is just the "model" detail I need to capture (column D)
My goal is for any of my team members to be able to use a macro to delete all of the unwanted text from a fairly large worksheet and only show their specific information. I created it for myself and posted a slimmed down version of it below. Instead of the macro deleting any rows that do not have BRANDON SCOTT in column D, I want a pop up box to enter your name, and then any rows that do not have your name in column D will be deleted. I’m sure it is something simple, but I have not been able to find another forum with this information.
Sub MyTeam2() With Columns("D") . AutoFilter field:=1, Criteria1:="<>BRANDON SCOTT*" .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete .AutoFilter End With End Sub
I'm working on a spreadsheet at work and I'm trying to sum only the values in a column that meet criteria in the column next to it. For example, in the attached spreadsheet, I would like to have a totals line at the bottom of the spreadsheet for all three Facilities and the total next to them. I know I could do a pivot table but I know this approach would be more aesthetic and easier for my supervisor.
we would like to get results from a formula that looks at several cells and provides the cost for a product.
Example
If we choose Cell A3=Transport (from drop down list) Cell A4=Entrance Facility (from drop down list) Cell A5=Bandwidth (from another drop down list) returns the cost for this product in cell A6
We would also like to restrict the lists to the different catergories: if transport is selected you only have the option of 2 of 5 facility types that will work with transport products. Do I need to separate my lists?
I'm trying to use the SUmif with Offset, but the width argument of offset doesn't seem to work within the SumIf function. I have 14 columns
A Employee B department C-N jan through dec salaries per employee per month
41 rows
1 titles 2-41 names of employees
now in May I want to know the salaries per department for the month may only, but also cummulative from jan up to may. Getting may per department works fine, but getting the cummulative doesn't seem to be working
=SumIf(B2:B40;"account";offset(C2;;4)) gives me the sum of department Account in the month of May =SumIf(B2:B40;"account";offset(C2;;;;4)) starting point zero (= january) plus 4 columns width, just gives me January.
I've tried several options, but every time he only sums 1 column. Without the SumIf it works fine but for the whole company, not per department ofcourse.
how a SUMPRODUCT function can resolve this. In this sample I want to have 1120-32188 & 1120-32188W be treated the same for the Total sum qty. In this case =10. This will compute but how do I include items in column A that have 'W' suffixes for computing Total Qty? Formula: =IF(A2=A1,"",SUMPRODUCT(($A$2:$A$100=A2)*($C$2:$C$100)))
I have an Excel sheet with 3 worksheets. On the first sheet I want to enter a formula that will check the third sheet. On the third sheet I need to compare all the entries in column N with a value in Cell B2 of the front sheet. If these match then I need the formula to add up the relevant values in column N. (column N has a 4 letter code, I am only interested in comparing the first letter, as such the value in cell B2 is H*, then in column U it has numbers of minutes, I want a total of the minutes of all those entries with a code that begins with H)
On the attached file, I would like the values from the Transactions worksheet to show up in the appropriate cells in the XYZ Expenses worksheet. The catch is, I would like the XYZ Expenses worksheet to only show the values from the past week. Past week could be literally, but I'm guessing that might not be possible and it would have to use the past week from the most recent transaction date - which would be fine. So if it worked right, XYZ Expenses > F7 would show -$80, which would be from Transactions H3+H6 (and not -$130, from Transactions H3+H6+H9).
Problem: Range A2:C10 contains the login and logout times of various ID's. Each ID could log in and out a number of times a day. How could we find the first time a specific ID logged in and the last time that same ID logged out?
Solution: For each of the ID's in range A2:A10, enter two Array Formulas. To find the first login time (Column B) enter the following formula: {=1/MAX((A14=$A$2:$A$10)*($B$2:$B$10 To find the last logout time (Column C) enter the following formula: {=MAX(($A$2:$A$10=A14)*($C$2:$C$10))}
I have been having problems all morning and have not been able to solve it!
I am sure this is very simple....
What I would like to do is assign the result of a sum to a variable. This part is easy what I cannot fathom or find any help with is the multipule criteria bit!
Here is the detail. Column A - Employee Number Column C - Area Column F - Type Column D - Amount paid.
I would like to sum the amount paid based on the 3 criteria, Employee Number, area & type and assign this to a variable to be used later in a sub I'm playing with.
im trying to set my workbook up so that it is updating itself daily. Currently it has about 4000 rows of invoices from multiple departments. What I want is a formula that when an invoice reaches 31 days that it goes to a separate worksheet. I don't want it deleted, I just want it to be moved in to a worksheet called 'WatchList'.
Any body know how this can be done. What Ive done right now only puts the row in the same row from the main sheet. So if row 2000 reaches 31, it shows up on row 2000 in 'WatchList'. Im wondering how I can move row 2000 to the top and whatever other row to the top once it reaches 31.
I would like to seek help on how to delete a cell that has data in it and that has colourfill. E.g. Cell A1 shows "Occupied" and Cell A1 has a blue colour fill.
Need help on how to delete that cell's data and remove the blue colour without having to manually do so.
I am trying to delete rows - based on 1 field in each row not being equal to ANY of the values stored in an Array. e.g. my array has 3 values, CAT,DOG,MOUSE
cells are: A1 = CAT A2 = HORSE A3 = DOG A4 = DOG A5 = BIRD
i want to delete rows not containing CAT,DOG,MOUSE. I think the autofilter could be the trick but cant quite get my head around it.
I have a table with characters in the cells (QC, PS and PK) - it is an employee scheduling table - to the far right on the table, I want to summarize the employee's hours by each type - each character (QC, PS and PK) represent 1/2 hour - i cannot figure out how to do this
Searching through hide/unhide topics seem to yield more complicated codes then my meager needs. Within my worksheet I have a column BD which contains a list of values,
ie:
BD 1 2 3 6 3 2 1 etc..
I've created a toggle button "HideRow", which I would like to use to hide/unide only the rows containing value "2" within column BD.
I have 2 columns of data in one sheet that list dates (say Master Dates) and transactions (say Master Transactions).
In another sheet i would like a formula that can choose the correct transactions that fall between certain dates (call Date Beginning and Date End) and generate a list.
I need to write an excel function/macro/code to assign yes or no to a cell if the Description fits a certain category. I have attached a sample workbook and will explain what I mean.
In the sample workbook, there is data on a company. This included a unique ID, city, state, and description. What I want to do is fill out the yes/no column. So if a particular company has 2 of the 4 necessary "Descriptions", then column G will return yes, otherwise No. It must return yes for a particular company/ID if 2 of the "Descriptions" are "Sale", "Service", "Business", or "Par". I only want it to count at least two unique descriptions.
For example, in the sample workbook, Company A has all 4 of the necessary descriptions, thus everything in column G for this specific client should be yes. For Company B, only 1 of the 4 descriptions matches, so it returns No for all Company 2 columns. Company C has 2 of 4, so it returns yes for these columns. Note, it doesn't matter for Company C that "Business" was listed twice. It only needs to count the first unique time that description occurs.
I have a worksheet that contains 2 Rn managers per 3 Site and 2 TSR managers per 3 Site. I want to be able to sort these managers by Site into 6 tabs: 3 RN tabs and 3 TSR tabs. I want to keep the original worksheet as is and copy the appropriate rows to the designated worksheets labeled SACRN SACTSR VALRN VALTSR and so on. The data range is A:M
The columns of value is A (JOB) and B (SITE) and here is the sample of the data
JOB SITE TEAM RN SITE SAC GASTON SUSAN TEAM SAC BERNAL RN SITE SAC GASTON SUSAN TEAM SAC CASTILLO RN SITE SAC GASTON SUSAN TEAM SAC CASTILLO RN SITE VAL RANADA TERESA TEAM VAL RINCON RN SITE VAL RANADA TERESA TEAM VAL RINCON RN SITE VAL RANADA TERESA TEAM VAL RINCON RN SITE SAC MCINNIS JOHN TEAM SAC SMITH RN SITE SAC MCINNIS JOHN TEAM SAC LEMON RN SITE SAC MCINNIS JOHN TEAM SAC JACK TSR SITE SAC GRECO DAVE M TEAM SAC KEN TSR SITE SAC GRECO DAVE M TEAM SAC ROGERS TSR SITE SAC GRECO DAVE M TEAM SAC LOPEZ TSR SITE SAC SHAVER KELLY TEAM SAC JONES TSR SITE VAL FRANCIS TEAM SAC YANG
I have a list, 50000 rows long with phone numbers and the service provider it belongs to. I want to extract some of this information. I'm looking for a formula which can give me the following: How many unique phone numbers is there in column B from service provider number 4 and 5? The following pic is just an example of how the list is compiled. How can I get sum up only the numbers from 4 and 5 and not the whole bunch? If I use this formula
Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas =SUM(N(FREQUENCY(D1:D51112,D1:D51112)>0)). I sum up all the unique phone numbers, but I don't know how to set a "if service provider is 4 or 5" if you get what I mean. This was the first part. The second part is finding out how many times each unique number from service provider 4 and 5 is listed.
