If Match Then SUM? - Wanting To Add Multiple Values If Criteria Matches
Column A would be Recipe Numbers, Column B the Planned Total.
I want to search column A for all matching recipe numbers.
Then, any recipes that match, look in column B for the planned totals, adding all incidents where they match.
Then the most difficult part, which just occured to me now.
I only need the Total Planned Total and Recipe name reported once.
I will not be able to sort these lists.
If-Match-Sum? to display:
Recipe#............Total Planned Total
I've researched for the first part... and it seems like Match will only find the first match and not look further? Am I wrong on this?
How to display the results hadn't even occured to me yet, since I was still trying to figure out how to GET the result.
View Complete Thread with Replies
Related Forum Messages:
Multiple Criteria Matches
I am trying to set up a worksheet whereby two critera when matched from a drop down list will populate cell 'x' with a coressponding answer. if you have Destinations then a From and To column with various locations listed below, then in a thrid column you have an output cell which is kilometres. What formula can i use to match the various location 'to' and 'from''s with the correct kilometre match?
Populating Cell Only If Multiple Criteria Matches ...
I have two worksheets. The first contains a list of software (some having duplicate listings) in column A and a list of comments in column B. None of the software titles that have duplicates will have comments.
The second worksheet contains a condensed list of software (the previous list without the duplicates) and a column for the comments.
I need to have a formula that populates the second worksheet comments cells with the corresponding comments from worksheet one only if:
(1) The software title in worksheet two matches the software title in worksheet one
(2) The comment cell in worksheet one contains a comment (or text)
I have had only partial success; my obstacle being the duplicate software titles in worksheet one.
Use Multiple MATCH Criteria?
I have been trying for the last 2 hours to write a formula that does the following;
If a number appears in this table (on another worksheet named VAT Codes) then return VAT but if it appears in this table (on another worksheet named VAT Codes) then return NO VAT. The following formula returns VAT but N/A when it should say NO VAT; =IF(F3="","",IF(MATCH(F3,'VAT Codes'!$I$7:$I$19,FALSE),"VAT",IF(MATCH(F3,'VAT Codes'!$K$7:$K$143,FALSE),"NO VAT","")))
Index Match - Multiple Criteria
I have a couple of desired outcomes.
1. I want to find the “Close” price based upon “Date” and “Time” input (search criteria).
2. I want to know when (what time) a “Price” (input) falls between the “High” and “Low”, on a specified date.
VLook Up Match Multiple Criteria
On sheet one, I am trying to match the employees job code for that particular week in cell K2. The vlookup/match is trying to find the particular job code for that employee number for that particular week ending.
Cell A2 (sheet 1, shows their employee #),
Cell J2 (sheet 1) has that particular week ending.
Cell K2 has this formula in sheet 1:
Column A = employee numbers
Column C = job codes (which I need to appear in cell K2 for sheet 1)
Column D = has the week endings to match against column J in sheet 1
Match Multiple Criteria From Different Arrays
I'm trying to create a template that will be able to return a sales persons call target based on the category they sell and the current level they are. It works when I only ask it to look for one or the other, but I'm getting stuck trying to make it use both. I've attached a small sample. My original equation is as follows
and this works perfectly fine. The problem I've now got is I need to add in a second criteria for setting the row_num. I've tried the following but I get a ref error. =INDEX($C$2:$Q$51,AND(MATCH($B3,$A$2:$A$51,0),MATCH(D3,$B$2:$B$51,0),MATCH(G$1,Telesales!$C$1:$Q$1,0)))
Match Value Of 1 Cell To Multiple Cells And Give Multiple Values
Sheet 2 is the problem.
I need to match cells e3:h3 whenver the dates change in in cells c5:c9 and then put there result of cells b5:b9 into the corresponding cells of e5:h9.
Basically what I'm saying is that I want to keep a track of all previous pay amounts from each pay period. So when the next period changes the date it also copies the new pay amount to the corresponding date of the previous pay section.
I have included a sample.
Formula - Average Values That Match Criteria
The attached workbook has two tabs:
1. Burn Rate - this is where I need my formula to calculate
2. prorder - this is where the table will be
What I need:
1) from 'Burn Rate', get the 'PO ID' we will look up in the table.
2) go to 'prorder' - when the 'PO ID' there matches that same 'PO ID' from the other worksheet - take the value in column F - this is the number that will be averaged.
In other words - in 'Burn Rate', for a given 'PO ID' (column A), I want to average all of the values that are found in column F in 'prorder', and return that average to 'Burn Rate' (column B).
Index & Match Formula: Multiple Row Criteria
I am trying to get my INDEX & MATCH formula to retreive data from my table.
This is what I can do so far:
But I am trying to get it to get another row to look up as well.
I want it to look up the color then the 100 or 250, then the date.
I think i need to insert another match code in the row section but cant seem to get it to work.
Multiple Criteria Lookup With Date Range Match
I am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant. I have attached my sample spreadsheet.
Chronological Listing & Match Criteria From Multiple Columns
I cant get this one off the ground. Normally I am pretty good to find bits and pieces of advice from other posts and put something together, but this one has me stuck from the get-go. I want to create several lists based upon the row(s) containing at least one set of criteria (day and time, contained in two differnt columns). Each row allows for up to 3 sets of days and times (i.e. columns B and C, columns D and E, and columns F and G) Each row is date and time stamped as to when the data was entered in that row (column H).
I want one list for each set of the various day/time combinations allowed in columns B thru G. The list should provide the name(s) contained in column A, in chronological order based on column H, when a criteria match is made in columns B:C, D:E, or F:G. This is far easier to understand with the attached sample. The lower section of the sample represents my desired output that I cannot seem to achieve.
MATCH For Multiple Criteria While Ignoring Empty Array Cells
I'm working with a large amount of data (A21:BZ1503) and I'm trying to identify unique situations where any pre-defined combination of multiple columns in one row is flagged by producing a pre-defined value. For example:
I have my pre-defined criteria in worksheet 'X' hidden in my workbook -- note that there are many blank cells.
On worksheet 'A' the various users enter data -- each row is a unique group with data entered into the columns -- again note a cell can be blank:
Index & Match Multiple Items/Criteria: Finding The Nth Occurrence
I've read the how to for finding the nth occurrence using index/match but the example given does not really help solve my issue. The file I've attached is a condensed version of the actual file, which has more columns but I deleted all but the necessary ones for clarity. What I am trying to accomplish:
On sheet1 there are three columns, Business, Amount, and Closing Date.
Not all the business names have a closing date and the spreadsheet is sorted alphabetically by business name, so sorting by closing date, and using the method used in the topic " find the nth occurrence in excel", is not an option.
On sheet2, I would like to see ALL the business names that have a closing date in the respective month, as opposed to just the first. Then to the right of the business names I have the sum of all the amounts in that month, but I figured out how to get that one already.
Count Rows Meeting Multiple Criteria Of Multiple Values
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.
Multiple Match Values - Index Dynamic Table
I'm trying to extract the selected suppliers orders from a 1000+ rows list ; like appearing with the example of supplier "D" ; and the new one should be dynamic , it means if any data added to the source orders list and if the supplier was one of the selected the new table automatically update ; or can be refreshed
Ranking Values Against Multiple Criteria
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.
ItemLife LeftTot CostBalRank
signs1 250 250 1
paint curb1 500 500 2
tree work1 2,500 2,500 3
landscape2 9,000 7,200 4
siding2 9,000 7,500 4
Here is my formula:
I've attached a file.
Lookup Values- With Multiple Criteria
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).
Match Combinations & Show Those With Greater Than X Matches
Match Combinations & Show Those With Greater Than X Matches
Dim Data_1() As Integer, Data_2() As Integer
Dim N1 As Integer, N2 As Integer, Nx4 As Integer, nRow As Integer
Dim I As Integer, J As Integer, K As Integer, L As Integer
Application. ScreenUpdating = False
N1 = Range("H1").Value
N2 = Range("P1").Value
Redim Data_1(N1, 6), Data_2(N2, 6)
nRow = 1
Do While ActiveCell.Offset(nRow, 17).Value <> ""...................
Counting Unique Values With Multiple Criteria
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]
2366 2005 T 2
(as opposed to 3)
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
Sum By Multiple Criteria & Use Cell Values As References
I want a total of column F based on criteria in column D and column K from the worksheet named in cell C9. I have put in the indirect function in the first part but am messing up the syntax in the second and third part of the formula. "Indirect(C9&" should be where 'Oct2009' is shouldn't it. Where am I going wrong with the following formula?
Sum Values If Multiple Variable Criteria Are True
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
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:
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.
Searching For Values Based On Multiple Criteria
I have a problem with excel that I have been trying to solve for the past 5 days! I have 2 tables (one with 500 rows, the other with 15000) that contain information about pipes.
The smaller table, Drift List, is to be used as a reference to fill up the bigger table.
Here is how Drift List looks like:
Size NominalWeight WallThickness APIDriftDiameter AlternateDriftDiam.
5.5 14.00 0.244 4.877 7.625
5.8 14.88 0.253 4.915 ----
The other table, Pipe Database, Looks like:
Size NominalWeight WallThickness DriftSize DriftType
5.5 14.00 0.244
I have to fill up Drift Size using APIDriftDiameter from the first table, BUT if AlternateDriftDiameter is available, I have to use that instead.
DRIFT TYPE column has to indicate either API or Alternate (Depending on which one I have used).
Here are the two files:
Multiple Criteria - Vlookup For Numerical Values
I have the following 'numerical values'
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
Values In Column Based On Multiple Criteria
Dim lastrow As Long, t As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
For t = lastrow To 1 Step -1
If Cells(t, 8).Value <> "" Then
If Cells(t, 9).Value = "Y" And Cells(t, 10).Value = "" And Cells(t, 12).Value > _
6 And Cells(t, 12).Value < 60 Then Range(t, 25).Value = 20
Alright, the above code is not working. I am not sure if it is the write part (t,25 value) that is wrong. I want the Y column to be written with a method numbered "20" if the conditions (H is not null, J="Y", K="", and 6<M<60). I have numerous other methods to put in. The reason I'm not doing Case Statements is this is jsut to write the basic code, and then I will have to move it over to ReportSmith using ReportBasic.
Match 2 Columns Across Two Sheets & Copy Rows Of Matches
I would like to match column data in a source spreadsheet to column data in a target sheet. If a match is found, I would like to copy the corresponding row range from the source sheet to a separate, third sheet. For values where no match in found in the a target sheet, I would color the unmatched cell in the target sheet red. If a match was found, the cells would be colored green. The data in the Source sheet is in column A, while the Data in the Target sheet is in Column T. The data will be pased in the third sheet in Column T preserving original formats
I have this code, gleaned from several postings on this forum that somewhat works. The problem is that I get false mismatches (i.e. some cells get colored red even when there is a match and the data got copied to the third sheet) even though there are no duplicates. I have made sure that the formats are identical in both Target and Source sheets to try to fix this. Also, I don't want to cut the entire row , but just copy and paste a row range onto a third sheet. The column and row ranges are variable. I am attaching a file.!!
Dim i As Long, k As Long, n As Variant, r As Range
Application. ScreenUpdating = False
Set r = Range(.Cells(1, 9), .Cells(65536, 6).End(xlUp))
k = 0
i = 6
While Not IsEmpty(Sheets("Target").Cells(i, 20))
n = Application.Match(Sheets("Target").Cells(i, 20).Value, r, 0)
If IsNumeric(n) Then
Sheets("Target").Cells(i, 20).Interior.ColorIndex = 35
k = k + 1
Sheets("Target").Cells(i, 20).Interior.ColorIndex = 3
i = i + 1
Application.CutCopyMode = False
Application.ScreenUpdating = True
Finding Top 2 Values Per Group Based On Multiple Criteria
I am trying to find the top two values per group based on multiple criteria. The list I'm working with is not sorted and would be better for it to not have to be sorted as on-the-fly sorts will likely often occur from the raw data and I wouldn't want that to mess up the results I'm looking for here.
As an Example, here's what I'm trying to do:
Make Model Rating
Ford Bronco 64
Chevy Corvette 94
Dodge Intrepid 83
Chevy Chevette 34
Dodge Viper 72
Ford Escape 21
Ford Expidition 53
Chevy Impala 67
Ford Fairmont 11
Dodge Dart 33
Count Multiple Values In Cell With Range Criteria
I'm trying to figure out how to count by two criteria, only one of the criteria has a value among multiple values in a cell and the other is a range. I tried to use sum but it doesn't count the value and count doesn't recognize the range. I attached the file so you can see what we're trying to do.
Return Multiple Values Across Columns And Rows Based On Criteria
I have a table with column headings of product ID Numbers (eg.1111) and row headings of Store number (Eg.1) with data showing the time each product was last sold at that store, I need something to consolidate for each store which Product ID's were sold prior to 5pm and what time they were sold.
Store 1 1111 16:40
Store 2 1111 15:05
Formula/Function To Return Multiple Values Based On Criteria
I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?
Finding Last Row In Data Row That Matches Criteria
have two worksheets. sheet1 has order information on it with orders, dates, customer names. sheet2 has customer name list. How can I (via vba) search through the order sheet and find the most recent order date for each customer in the customer name list. post that most recent date next to the customer name on sheet2.
Replace Column With Certain Criteria Matches
4 columns a-d. now if i find either 1 of the 2 particular strings "hello" or "bye" in column A, I want to move column b into its place and leave the rest of the columns untouched
dog, circle, blue, 123
hello, frog, green,223
pig, triangle, red, 445
bye, elephant, grey, 322
dog, circle, blue, 123
frog, blank, green, 223
pig, triangle, red, 445
elephant, blank, grey, 322
Return Row Number That Matches 2 Criteria
I have a produced an Excel workbook which uses a VBA sign in/out userform.
Once you sign in on the Userform the sheets update. A list is completed of the times people enter and leave.
To make the code easier I currently have the name being returned to the excel sheet and performing a “match” function to return the row number. This row number is then used to carry out what I need to happen in this row. However, as you can see from attached doc (and the brief example below), based on IDnumber "2", the match function returns row 5 not row 8.
I need to have the row number returned for the IDnumber where the Out cell is blank.
This should be the last occurrance of the IDnumber
1 ID# In Out
2 1 9:00 13:00
3 4 9:00 13:00
4 3 9:00 13:00
5 2 9:00 13:00
6 5 9:00 13:00
7 6 9:00 13:00
8 2 14:00
Delete Rows If Column Has Fewer Than X Matches To Part String Match
I have data sets on multiple worksheets within a workbook (over 70). Based on the begining of a string, I need to count all instances with that begining, and if there are fewer than 12 instances, delete the entire row.
Data set example:
In this example, I need to count each instance of rows starting with 1/* - 9/*, and delete rows as mentioned. In other data sets the string might start Gi1/*, and so on. In the following code, if I do not loop, and only test against 1 value type, and ONLY run it once, it works. As soone as I try to loop through all possibilities, or run the macro a second time, it blanks out the entire worksheet, starting at row 3.
Dim c As Long
c = Worksheets(i).Range("A" & Rows.Count).End(xlUp).Row
With Range("A3:A" & c)
If Application.WorksheetFunction.CountIf(Worksheets(i).Columns(1), "1/*") < 12 Then
. AutoFilter field:=1, Criteria1:="1/*"
.Offset(1).Resize(.Rows.Count - 1, 1).EntireRow.Delete
I have also tried to concatenate a variable with my CountIf criteria, but that fails miserably.
Count If Column Heading Matches Criteria
I have data (coming straight out of another system, so whilst is seems messy, its the easiest way to have it in Excel).... where there are a number of rows (one for each employee) - then every column represents a date in the month (so there are about 30 columns)....the cells contain a number of hours worked by that employee for that day.
I have figured out how many hours they worked for each day of the week, but my client now wants to know:
for each employee - how many Mondays did they work?- how many Tuesdays? and so on
I am happy to figure out the day of the week for the first date in the month and then manual set up another column heading above the date that tells me what day it is - but I feel that Excel may actually be able to work out what day of the month it is - although the dates come across from the other system as text - April 26, 2006 etc...
So I would like a summary in my sheet that has column headings
Mon, Tues, Wed, Thurs, Fri, Sat, Sun
and then for each row for each employee - I would like the number of days that employee worked for the month by these Days of the Week.
I have looked at Count if - but I need to be able to select the range of cells to count, and also the range of cells to evaluate for the criteria and I cant figure it out!
Two Criteria - Multipule Matches That Are The Same - 1 Answer To Be Return
I've been looking through past threads and I can't find what I need to do anywhere. There was one formula that came close: =SUMPRODUCT((A2:A100="Aimbry")*(B2:B100="LW")*(C2: C100)). but this adds up all the matching results.
What I need to do is match the info in columns A and B and return a number in column C. The problem is that there are multiple rows with identical entries and I just want to return one of them. So I was thinking a version of the above that produced a mode or average would give the desired result.
Count Data That Matches Criteria In 2 Columns
I am trying to use a "=COUNT(IF(..." formula to count the number of hourly employees that have start dates older than 3 years. Column A has the start dates and column B has the salary status (S/H). =COUNT(IF(AND(A2:A1000<DATE(2004,1,1),B2:B1000="H"),A2:A1000)) This formula returns 1 no matter what I change the data to.
Forcing Advanced Filter To Cancel Criteria If Found No Matches.
I have a advanced filter that works pretty much close to how I want it to. However I would like it to either cancel the new filter or copy everything if it finds 0 matches.
If the copied location is blank it breaks my sheet. So I need to find a way for it to never be blank, either by canceling it, copying everything, or finding some other way I haven't thought of.
Also for some reason my Advanced Filter does NOT Ignore blank "OR" cells. If I place a word in the top cell, then leave the bottom blank, it searches for the top cell or anything and I end up with everything. Its quite frustrating.
Forcing Decimal Places (wanting NUMBERS Not TEXT)
I am wondering if there is any way to "force" a number to have three decimal places instead of two and still have that number valued as a number for use in formulas later (like AVERAGE, etc.).
What I have is a combination of HLOOKUPs, IF statements, and Conditional Formatting.
I have a Master Grades sheet and individual Class sheets. I use an HLOOKUP, matching a Student Number in both sheets, and looking in the Class sheet for the P/F indicator. If the indicator is a F, the HLOOKUP is to return the Final Grade from the Class sheet and input that into the Master Grades sheet, forced to three decimal places (whereas the numbers in the Class sheets are at two decimal places) If the indicator is P, the HLOOKUP simply returns the Final Grade from the Class sheet and inputs it into the Master Grades sheet with two decimal places.
I have the failure grades forced to three decimal places so that the Conditional Formatting in the Master Grades sheet highlights the failed grades for teachers and anyone else can see right away that a student failed, but the number still allows the Master Grades sheet to calculate the overall AVERAGE for the student as well as the AVERAGE for that class.
If any of this is not quite clear, please ask and I can further expand on what I am doing.
Here is the code I have right now that is not working correctly (and I know why it's not): ....
Compare Two Cells In A Row With Criteria. Count Rows That Match Criteria
Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.
I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
I am trying to match data on two different reports. Both reports have month, location and supplier number. I would like to match a quantity on one report to information to another. Here is an example: (A-D are columns in excel)
A B C D
Month Location Supplier # Quantity
A B C D
Month Location Supplier # Quality
I would like to match the quality on the second report to the data in report one. I would have several months of data and want to match on month, location and supplier number. But there could be occurences where a match is unable to be made - for example having report with quantity but no corresponding quality on the other report for the month.
Identify Matches And Return Values
1) Check to see if a value in column A of worksheet A appears in column A on worksheet B.
2) If it DOES, then display the value from the same row from column B on worksheet B, in column B on worksheet A.
3) If it DOESN'T, display the value "EACH" in column B on worksheet A.
Vlookup: Returning Multiple Matches
I am trying to connect a special date and a price to a special name (please see the attached file).
In sheet 1 I have the following:
- In column A I have a set of different names.
- In column B I have a set of different dates.
- In column C I have a set of different prices on bonds that off course refers to the different names at their respective dates.
In rest of the sheets I want an overview of the different bonds, and what their prices are at the exact date. The problem is that if I use the vlookup it is only the first value that will be extracted. How can I get all of the prices corresponding to the different dates to the correct bond. I also want the possibility to update this every day.