I'm trying to find the sum of a range of values based on multiple criteria, and the criteria is that the fields all have to be identical, then sum them. I've attached a brief example spreadsheet that has the fields
A=City
B=State
C=Values
What I want the formula to do is first find the range of all the matching states, then find the range of all the matching Cities within the states, and then sum the values based on them having matching city values.
I've been able to do that with one criteria using SumIf, I'm not sure if this will help paint an image of what I want to do:
=ROUND(SUMIF($B$3:$B$11,$B$3:$B$11,$C$3:$C$11),0)
But I can't figure out the way to do multiple criteria against itself. Most of the results I get from Google using multiple criteria are using a set few values, and I can't seem to figure out how to alter those methods to work with my situation.
I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.
I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).
The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.
As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.
The log code in column "H" Is based on this criteria: First Letter of the Unique ID in column "E" - O, M, or L Program Type in Column "F" - U or R 1. O-U = U 2. O-R = RU 3. M-U = U2 4. M-R = R2U 5. L-R = R
You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.
This is the area where my skill at using scripting dictionaries fails.
The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words
if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")
So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)
Here is my code so far.
VB: Option Explicit Sub test() Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long Set dic = CreateObject("Scripting.Dictionary")
I need a macro that will create a sheet at the end of the workbook.
Sum data from a variable amount of sheets and display that data on the created sheet.
Here is a step by step:
Starting on sheet 5.
Column D has a variable amount of part numbers in it. These part numbers would be different between the ascending sheets.
Column T, U, V has an inputed number in it that would need added up across all duplicate part numbers in all the sheets.
(Note: The data would also need started on row 4. Everything above row 4 is headers)
Here is a small example:
D E T U V 13019090W Part A1 68705500 Part B1 64202900 Part C-11 59634600 Part D1 26005300W Part E1
I need the macro to start with sheet #5(starting on row 4). Check to see if there is data in column T, U or V. If there is, to create a new sheet at the end. And copy the entire line into that sheet (starting on row 4).
After that, to check every sheet after (excluding the newly created one, starting on row 4) for data in Column T, U and V. And then check for duplicates in Column D on the newly created sheet. If there is a duplicate to add/subtract that number in Column T, U and V to the SUM in column T, U and V in the newly created sheet. If there is no duplicate, to copy the entire line to the new sheet.
So that when finished. On the new sheet, you have the SUM of T, U and V for everything that has data in T, U or V for all of the previous sheets, plus the entire line of the first instance (excluding the first 4 sheets).
I have a worksheet where I have around 300 rows, each with 7 columns. What I want to do is add a checkbox to each column. I plan on setting non-applicable checkboxes to mixed status and locking the worksheet. I will unlock applicable checkboxes and sumif or countif their value according to row-based scoring, for example, each checked checkbox represents a value of 3. I do not know VBA and have chose to use the form control checkboxes rather than ActiveX.
I believe that a formula for this would be something like: =SUMIF(B1:B3,True,"3") or =COUNTIF($B$1:$B$3,True)*3
I am wondering firstly if I have that right and secondly if there is a way to stop my checkboxes from displaying labels. Currently, if I click on one it displays True behind the active checkbox. If I uncheck it, it displays False.
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode() ' 'Sheets("Summary"). Select If Range("a7") = "complete" Then ' Sheets(Array("1", "0")).Select Sheets(ArrSh(1)).Activate
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not.
I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K.
I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.
I need a bit of help with the below macro which I am trying to create. I recorded the below vlookup, which works perfectly. It checks a list on sheet “Map” and returns a value depending on whether the reference is one of the 6 or not. These 6 are likely to change over time so I would prefer to declare them as variables rather than build them directly into the macro
I am looking to create a macro which, when executed, will:
1) search sheet 1 column B for any instances where a "batch #" from sheet 2 column B appears 2) add in the corresponding "Destruct Form #" from Sheet 2 Cell B1 into sheet 1 column b
Therefore after the Macro is run, sheet 1 will look like this:
Need exact VBA code syntax to assign a workbook (to be closed) sheet's cells J4 to J72 values to a variable called "ColJValues" to be assigned to another sheet (to be opened later in the macro). The values are all dates. Once the other workbook is opened later in the macro, need the exact syntax to assign the value in the above variable, "ColJValues", to it's cells J4 to J72.
how to set a cells value into a variable, using .value, then set another cells value equal to that variable without using copy/paste
What I can't figure out is how to see the value of multiple cells to a variable and place them into another range of the same size using .value. It would be nice to free up the clipboard.
Search for the word "Clear" in the Task column. If the word "Clear" is found, go to Value column. Add all values starting from "Clear" down to the end of the list. If it is larger than 10, return the word "OK". Otherwise "Poor". If larger than 20, return the word "Good". The length of the list is variable.
Sumif formula. I need to somehow put a loop on the criteria or something...
The formula is sumif(range, criteria, sum range). The problem I am having is that I have about 20 criteria to select, and sumif is designed for one criteria. I know that I can type the exact same formaula in and have something like this...
This is OK for a few variables but some formulas have multiple criteria in excess of 15-20. the other thing is that the criteria list will grow over time as more variable are added, and instead of changing the formulas throughout the spreadsheet, I would like to have a big range that I can slowly fill up as I go along
The ideal situation would be if I could put a range in ( ie A6:A30) and then when I get a new criteria I just add it to this range. This will make administration eaaseier by giving me one location to update data.
I will show you an example.. I want to calculate the amount of petrol I use based on three criteria - BP, Shell, Caltex ( the criteria being petrol stations that come up on my bill).
Column A/ B / C/ D Row / description / Amount/Variables 1 / Shell Petrol / $10/Shell 2 / Cat Food/$13/BP 3 / BP Store/$24 / Caltex 4 / Dog Food/$23 5/Shell Petrol / $98 6/Caltex Petrol/$31 7/BP stuff/$30 8/Shell Store/$70
If I could somehow get the formula to choose the whole column D as the criteria range and get the 'sum if' to somehow loop so it goes down the d column and does the same calculation, but just changes the criteria variable each time, then instead of changing the formula, I could just add to the D column each time I had a new criteria I wanted to check.
I looked into DSUM and Pivot Tables. DSUM seems to work well with numbers, not variables and Pivot tables did my head in, especially seeing I know that there is a way to do this in a formula based way..
I'm trying to get a value to return based upon three criteria, sales rep, compensation plan and calculation method.
For each sales rep (there are 20 reps), there is an option for one of five comp plans and for every comp plan, there are six calculation methods (these six methods are the same for every one of the five comp plans).
I know I need to use the Indes(Match()) set-up, but I'm having the darndest time figuring out how to set up the source matrix to do this right.
I have successfully been able to rank items using two criteria but I'm unsure of the syntax for adding a third or fourth set. I'm new to the use of Sumprodoct.
I have attached a file. Where column C and E have the same value, I want Excel to look at column F and assign a rank (formula is in column K) in ascending order.
I want to lookup values from one rawdata worksheet and have values appear on another sheet, but using 3 lookup values as the criteria. I cannot do this function with Vlookup as it only takes one criteria. I have looked for similar threads in this forum and came across an Index/Match function, but I cannot get it to work. I am attaching file. The data is in the 'rawdata' worksheet. I want values to appear in 'Input' worksheet, grey area using the 3 criterias (Dept number, GL number, and Seg code number).
Spreadsheet 'Raw Table' has many, many rows, and a dozen columns of raw input data. Each row has date, number, and text fields.
Spreadsheet 'Dashboard' is a somewhat simple one page summary of one day's worth of data entry from Raw Table. Dashboard isn't a simple table, but more form-like in appearance (not sure if that matters). Essentially, the values from Raw Table are spread out in different locations in Dashboard, and not just in a row.
The Dashboard fields gets their values from Raw Table based multiple criteria, the most important being a manually inputted date entry at the top of the Dashboard. The other criteria are simple numbers (ie, 1, 2, 3, 4....) built into the formula. So a set of criteria for one particular cell in the dashboard could be: 4/6/2014, 1, 3. Those 3 criteria are unique identifiers: there is only one match, ever. If those criteria are met, then a value from a specific spot in that row from Raw Data is placed in the dashboard field.
Here is one formula example for one such field in Dashboard:
The first sumproduct criteria is a match for the date in Raw Data's column A with the manually inputted date in Dashboard located at D1.
The second criteria is match for the value in column B with a value of 1. The third criteria is match for the value in column C with a value of 3. (Again, this is an example. The 1,3 set can be any specific combo from 1,1 to 8,8. Thus, a day's data can have no more than 64 records of a dozen date, number and text fields.)
The Index function is obviously array,row,column. So this example index function results in the contents of column D in the row that the sumproduct function generates.
This dashboard spreadsheet works perfectly everywhere except four fields (out of 100+ instances of the same formula). In the faulty spots, the value that the index function is returning in Dashboard is basically the contents of column D in Raw Table in the row that corresponds to the place the formula is located in Dashboard. It's almost like there's an error in the formula result, and the index is defaulting to the row number in Dashboard. But I've tested this 8 ways til Sunday. I can even straight-up copy these four faulty fields to another part of the dashboard, and the results are magically correct. And I've checked to see that the actual sumproduct function result is returning the correct row for these fields. I don't have an answer as to why it's defaulting to the formula row number in just these four locations, and not the row value that the sumproduct function is correctly producing.
I've thought maybe it was a data type problem in Raw Data, but that route produced a dead end. It has to be something with the Dashboard. I'm going to start over with a new Dashboard spreadsheet, and see what happens. One thing I did was, early in development of the dashboard, use ctrl-shift-enter in the first faulty field, then went back and changed the formula wholesale to something different, and copied that cell to (at least I think) the three other faulty spots. But if I went back and completely changed the formulas and did away with the brackets, the cells should be fresh, no?
I'd like to extract the data from Sheet 2 (Data) that falls within the selected date range but the formula I've entered in F$9 (see below) is giving me an error
Have spent ages on this to no avail. Need the attached sheet to verfiy the values in the "Reconciliation" are correct when referencing the "Prices" sheet. I need column F (Reconciliation sheet) to lookup the "Code" in column B in the "Prices" sheet and then further look up the "Payment Frequency" (column D in the Reconciliation sheet) in the "Prices" sheet to verify that the price is correct.
Due to slight decimal point issues, if the value has a greater than 0.02 difference then display "ERROR" otherwise 0.
I am looking to count the unique amount of customers who are listed in column C based on the criteria that they purchased the product on the 1/08/2014 and that the product came from Department 3. The output I am looking for in this example would be 3. I can do it for this example but when I have 300 different customers it starts getting tricky and I'm a bit stumped on how to incorporate an array formula into a countifs() function or whether there is an alternative.
Date DepartmentCustomer 1/08/2014 3 A 1/08/2014 3 B 1/08/2014 2 C 1/08/2014 3 D 4/08/2014 3 A 5/08/2014 2 A 5/08/2014 3 D
With the first two values as criterias, I need to find the 3rd and 4th value Example: If I have the criteria as 30 and 1800 , I should get the result as 0.04and 3
I'm trying to get a count of unique values in column B when criteria matches for columns A,C and D. The worksheet I'm trying to complete looks like this:
2366 2005 T [number of corresponding unique values of B]
For example:
--D---C---A-B 2366 2005 T 2 (as opposed to 3)
A B C D T 655 2005 2366 T 656 2005 2366 T 656 2005 2366 W 659 2005 2367 W 659 2008 2369 W 659 2006 2370 F 659 2005 2370 W 660 2005 2370 W 660 2008 2371 W 660 2006 2371 T 660 2005 2371 W 661 2005 2372 W 661 2007 2372
What I am trying to figure out is how to populate cells N4:N5 and N8:N9 for unique locations by Fruit & Vegtables by Area A and Area B. I just have some basic data for this example as the spreadsheets are 30,000+ lines long.
I am working with an aging report where data is formatted in column. In the last column I would like to assign a value based on criteria from the adjacent columns (ex: If balance > $10,000 then value = "Hold").
My idea was that I would asign variable to the column, start a for next loop, and insert the criteria in an if then else loop. Is this the best way to go about it, should I be using case select, and how do best assign the variable to column so that I can loop down the entire report.
