Sum Based On Matching Criteria
Jul 6, 2006
How do I return an offset value within a named range using a formula? For example, my range "RngTest" is from C3:J43. If a value of "Product123" is found within the range, (lets say its found in cells C3; E5 and E7), I need the accumulative values in the cells 'below' (in cells D3; F5 and F7) added ( SUM) and returned to cell L3.
Therefore if Cells D3; F5; and F7 have the respective values of 3; 4 and 5,
the value of cell L3 should be 12. Also, do I need to express "RngTest" or "C3:J43" or does it matter?
View 4 Replies
ADVERTISEMENT
Jul 25, 2014
I am looking for a formula that returns the latest Sale date for a each model of car. Below is sample data which I am trying to use the formula. I tried with below formula, but not successful.
=MAX(INDEX($A$1:$C$40,MATCH(A2,$A$1:$C$40,0),3),1)
MakeModelSale Date
AudiA4 11-Jan-14
AudiA4 quattro 12-Jan-14
AudiA4 quattro3-Jan-14
AudiA5 Cabriolet 14-Jan-14
AudiA5 Cabriolet q15-Jan-14
AudiA4 16-Jan-14
AudiA4 quattro17-Jan-14
AudiA4 quattro18-Jan-14
AudiA6 quattro19-Jan-14
AudiA4 10-Jan-14
AudiA4 quattro09-Jan-14
AudiA4 quattro10-Jan-14
AudiA4 11-Jan-14
AudiA4 quattro11-Jan-14
AudiA4 quattro11-Jan-14
AudiA8L 11-Jan-14
AudiA8L 11-Jan-14
View 3 Replies
View Related
Jan 9, 2007
I have a worksheet with two tabs.
First Tab
Account Name
Account Number
Second Tab
Account Name
Account Number
Account Ship Location Number
On the first sheet each account name and number only appears once.
On the second sheet there may be multiple entries for each Account Name and Number.
On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.
View 9 Replies
View Related
May 26, 2009
I have an items list on page 2. On page 1 I want to create a drop down menu in C2 that references the list on page 2. Then when I choose an item, I want the additional information in cells D2 and E2 to come in automatically. The list on page 2 will be expanding daily, and when I expand it I can simply insert a new row so I correctly alphabatize it instead of constantly having to resort it.
With that said....
1. How do you put in the drop down box that references information on a different sheet?
2. What is the best way to reference the information in my ever expanding situation: Index/Match, Vlookup, Lookup, or something I havent mentioned?
View 14 Replies
View Related
Jul 1, 2008
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
View 8 Replies
View Related
Dec 23, 2008
I have an output table that looks like the followings:......
There is 1 input for the spreadsheet: What month? Enter 9, 10, 11 etc. I need it to pull from a data sheet that is set up as such:......
So effectively it needs to vlookup the name in column A, (A, B, or C), and then it needs to match that to the column that has the same name as the output table (Crit.1 or Crit.2 and the number of the month above it (10,11,etc). I attached an excel sheet to better demonstrate
View 2 Replies
View Related
Dec 23, 2009
If you look at my attached example. I'd like the date line/columns in Sheet "8002372008" to be populated automatically with data from Sheet "Data".
Is this possible? VLOOKUPs and MATCH are what I was thinking, but I'm not sure how to do it.
I know it could probably be done in VB, but I don't want to have to click on each "800 Number" sheet to update it every time, so that my summary sheets are accurate.
View 10 Replies
View Related
Jun 30, 2007
I need to match data with specific criteria. Details of the problem are as follows:
I have 2 sheets: "Main" and "Sub"
In "Main" there are 3 Important Columns: Col "C", "I" and "AF"
In "Sub" there are 2 Important Columns: Col "I' and "N".
Value in column "C" of "Main" is same as value in column "I" of "Sub", but not in same order.
Now the specific criteria:
If value in Column "I" of "Main" is not equal to "Zero", then the macro should pick up the value from the corresponding column "C" of "Main" and search for the same in column "I" of "Sub". After matching the same, it should find corresponding value in column "N" of "Sub". Then the macro should pick up the value from column "N" of "Sub" and put the value in the column "AF" of "Main".
This is basic requirement.
I am unable to retrieve the right value for the same.
Fine tuining will be:
If the value in column "N" of "Sub" is "Fully Automated" then it should only write "Automated" in column "AF" of "Main". And If the value is "Manual + ...." then it should write "Manual" in column "AF" of "Main".
View 14 Replies
View Related
Feb 9, 2006
Col cCol dFormula
Lease TermCompany #6023%
3637%
4843%
6052%
12620%
247FALSE
368FALSE
48105%
609FALSE
Residual Table
Company #
127
438
569
10
Col b
Lease TermResidual %
1210%20%9%
245%9%6%
364%7%3%
483%5%2%
602%3%1% ......
View 10 Replies
View Related
Apr 22, 2012
Modifying the following line by replacing the row number in AC2 and Q2 by an incremental variable? I mean something like “AC”&Rw , “Q”&Rw, &Rw, Rw being my incremental variable used to go through the rows.
ElxJobNo=Evaluate("Index(Index(Jobnumbers,0,1),Match(AC2&Q2,Index(Jobnumbers,0,3)&Index(Jobnumbers,0,4),0))")
View 3 Replies
View Related
Mar 20, 2007
I am trying to solve a small problem that i have, I am not that good with VBA and need somehelp.
heres the situation.
Sheet 1 is filled in col.1= title, col.2= table# (ex. 1,2,...11)col3.= grid location (ex. a-1, a-2...a-10,b-1...b-10....d-10). I will enter a title then pick a table then a grid location.
On sheet 2 also known as table 1. col.1 =grid location (ex. a-1, a-2...a-10,b-1...b-10....d-10) col. 2 = title.
I want to create a macro or function that can evaluate sheet1 and pick out when col.2=table 1 and col.3 = grid loc. A-1.... through D-10 then give me what is in col.1.
this will have to be done for 11 sheets 1 sheet for each table.
View 9 Replies
View Related
Jan 28, 2010
I have a cell (A1) that contains text.
That text will match a single value in a reference list (i.e. range of cells e.g. Z1:Z26).
When the match occurs (e.g. A1 = Z16), I want the format of the reference cell (fill color, text color) to apply to the cell of interest (A1).
The reference cells are manually formatted.
I know that I could create a long list of conditional format formulas instead of using the reference, but it seems that there must be some way to tell excel: if the cell value contents are equivalent, the formatting should be as well.
View 9 Replies
View Related
Apr 5, 2013
I'm trying to build a formula on the matching text as below, im getting an value error, however when i press the FX key it shows the correct result.
Column A
Column B
Column C
Column D
[Code] ....
( I would like to match the below two criteria with the Row 1 and Row 2)
Co1
#VALUE!
Latest Forecast
Formula-->
IF(MATCH(A6&A7,B1:I1&B2:I2,0),"Ok","Not ok")
View 1 Replies
View Related
Jan 30, 2014
I'm trying to create an overview which shows a breakdown of the total minutes for each task on each given day.
I need to get a formula to work in the grey area which sums the total minutes (column C) based on matching two criteras of task (column A) and day (column b).
View 5 Replies
View Related
Jan 30, 2009
I have two sheets where I dump data into. Widget sales and Widget Accessory sales. I'm trying to display for the widget salesmen the amount of dollars they sell in widget accessories ONLY for the transactions in which they also sell a widget at the same time.
So I need a formula or process (taking and comparing the data from both sheets) to do the following
IF
1. The dates of a widget sale and the accessory sale match (in both sheets)
2. The salesman ID in both sheets match
3. The names of the customer in both sheets match
THEN
1. Add up the total dollars by salesman for sales that match the above criteria
I've tried using sumproduct... I've tried using countif and concatenating pieces of data to make unique sales codes... I've exhausted my meager excel skills .
View 4 Replies
View Related
Jan 8, 2014
I have a sheet containing a list of client name,the date they've been assigned, the type of account they have, the status of the account, and the last update date.
What I'm trying to accomplish is to extract the client name and the type of account on that table that shows as "complete" in status. However, it gets complicated since I need to separate the data that had been 'completed' under the same month, with those completed the other month.
For example, I have been assigned 2 clients this January. I closed one in the same month, and the other closed at February. The first client would be listed under the first table (Completed within the month) and the other would be on the second (Completed on previous months)
Since this would be monthly, I am thinking if it would be more efficient to include the table per sheet (as one sheet corresponds to a month), or consolidate everything to a single sheet... I'm not sure which one would be best.
View 2 Replies
View Related
Aug 31, 2013
I need to compile a 'sumifs' formula to add sales for the salesmen.
Below is an example table. My dilemma is determining that in addition to the sales I only want to 'sum' the 'CANCEL' sale of a customer that initially purchased a 'GOOD' Status and not a 'PENDING' status.
How can i write a formula to determine that (for example) Customer3 'cancelled' the 'pending' sale, therefore I do not want the 'cancel' to sum in my calculations.
Date Salesman Customer Product Status Sale
Jan 8th John Customer1 Apples Good $500
Jan 9th Mark Customer2 Pears Good $200
Jan 8th Kevin Customer3 Oranges Pending $250
Jan 15th Mark Customer2 Pears Cancel $200
Jan 16th Kevin Customer3 Oranges Cancel $250
View 4 Replies
View Related
Jun 28, 2014
I have a spreadsheet where I want to match the PO info with the invoice info. I want to match the cost center, location, PO number, item number and the price.
Period
Cost Center
Account
Item No
PO No
Amount
Test
DEC-12
5000AF
000
M0002648
13579
(59.89)
5.11
[code]....
The formula I used in the test column
=SUMPRODUCT(--($B$2:$B$13&$C$2:$C$13&$D$2:$D$13&$E$2:$E$13=B2&C2&D2&E2),$F$2:$F$13)
As you can see in that the formula is showing the first 4 rows with a difference even though that the difference is in the first two. How can i fix this?
My second question, on the lines with the 989.04 difference, how can I make the same formula to say "Fix the account" or to fix the column with discrepancy
View 4 Replies
View Related
Jan 3, 2007
setup a vba code to find values which were separated by a certain value, thus there would be pairs of numbers sought after. What occured was that the first value is bolded, followed by the second not bolded, this pair would be separated by 75. Now I want to apply the same code, but instead of copying just the two cells which were separated to someone on the current sheet, I would like it to copy the entire row (as there is more data associated with it now) with the same bold/not bolded pairing to a new sheet, called "Numbers". I would imgine it be straight forward, yet I am very new to this and still tinkering.
Heres the ....
View 9 Replies
View Related
May 11, 2007
I have been using the code found here
Sub DeleteRowsFastest()
Dim rTable As Range
Dim lCol As Long
Dim vCriteria
On Error Resume Next
'Determine the table range
With Selection
If .Cells.Count > 1 Then
Set rTable = Selection
Else.............................
to delete rows that match the given criteria. I am now wanting to do the opposite, keep the rows matching my given criteria and delete all others.
View 3 Replies
View Related
Jun 25, 2007
I have been asked to analyse the contribution / performance of each student in a team. What i am trying to do is to count the number of times each student has work together and then try to work out their contribution. Got stuck on the 2nd bit for like 3 weeks flat. have been trying but not luck... seems to give me 1 or 0. so i assume my attempt is only a true or false answer! All i need is a count of how many times each student work with each other, and the best backbone formula is "count". This situation is best explained by my example, D15 is the closest i got
View 3 Replies
View Related
Jun 9, 2013
I have a spreadsheet that contains two input tables: Parallel and Perpendicular. Next, the user can select a group which a certain entry belongs to. Now what I want is a "summary" of these entries in a table that has no blank rows and combines both Parallel and Perpendicular entries by the Group. Please note that there is no fixed number of inputs for any one group i-e the solution has to be dynamic.The groups are numbered from 1 to 6 and number of groups is fixed i-e 6.
A sample file illustrating the inputs and required output is attached.
I also posted this on: [URL] ....
Sample_file_summary.xlsx
View 2 Replies
View Related
May 2, 2014
how to figure it out this lookup problem (lookup using partial string of match)...
View 5 Replies
View Related
Dec 11, 2012
The matrix:
Col A Col B Col C
2012-11-08 07:26:16Check-InJohn
2012-11-08 13:23:58Check-OutJohn
2012-11-08 13:24:01Check-InJohn
The goal:
Since i have a given:
1. Name(which exists in column C)
2. Date (which exists in column A - formated has (AAAA-MM-SS HH:MM:SS)
3. Event (which exists in column B)
4. Hour*
I want to know the nearest hour that matches all criterias.
In the example on top, if i give:
John, 2012-11-08, Check-In, 14:00:00
I want the result: 13:24:01
If, for example, the date doesn't exist, the result could be a simple error message.
View 3 Replies
View Related
Feb 3, 2013
I've got an array of data about 30x1500 cells in size, I want to use this data in a table I'm making on another worksheet.
Normally I'd just use VLOOKUP and this works great in most cases but I want to do something a little bit more complicated. Basically I want to (in normal English) have excel look at the table, find a cell that matches criteria in this data array, then compare to another cell on the same row in this array. If the criteria for these two match then display the data from another cell on the same row. The criteria for matching the first cells would be an exact value, both being strings of text.
I've tried using a couple of nested VLOOKUP and IF statements but failed .....
View 4 Replies
View Related
Dec 10, 2008
Matching criteria from a Virtual ( Non-Existing Range)
I need help on two things:
1> Virtual Range
2> Displaying TRUE or FALSE
I want to perform a Lookup Using the Regular Match Function with the following Syntax for checking Dates falling in a Fixed Range...
View 14 Replies
View Related
May 28, 2009
How would I write a sumif function to only add consecutive cells that match the criteria? Here is the formula I am using right now, but it adds all of the cells that match the criteria, not just the consecutive ones. Also they need to be consecutive cells from the current cell (in the formula below i would be referring to A80)
=IF(A81<>A80,SUMIF(A$5:A80,A80,H$5:H80),"")
More data:
Column A is a group of dates, but not in order, they are slightly mixed up
Column H is a monetary value.
View 8 Replies
View Related
Jul 14, 2009
I have a spreadsheet schedule that pulls quantities from another spreadsheet's cells. if the part number of the reference row matches the schedule cell's part number (row), and the date of the reference cell row is 2 workdays after that of the schedule cell's column, the quantity is put in the right place in the schedule. that part works. the problem I have is that sometimes there is more than one quantity with the same part number and date (due to the parts belonging to different jobs). when this is the case, only one of the quantities is picked up by the schedule. i want the schedule cells to sum the quantities of the reference file with matching criteria. i also don't want to have to change the reference file at all in order to do this. below is the function that works (besides summing the duplicates). the part after the " " is what i would need to alter.
HXS is the reference file where D is the part number column, I is the date column, and 5 is the index column referencing the quantity.
C is the part number column in the schedule
K is the date row in the schedule ....
View 10 Replies
View Related
Aug 9, 2012
I have two excel sheets. Lets say one is for May and another for June month. I have some data like name of account, account number their ratings . I wish to find if the account present in June month was present in May month sheet or not and if yes, then i need to extract a rating entry( column) from it and paste it in another sheet where i have copied all the data of June month so that i am able to see if the rating has changed over the months or remain same for that particular account...
i am trying vlookup but it is not able to search the name of the account even when it is present in the sheet( like WBIDFC) ... it seems the problem may be because of the format but i am not sure.... moreover i tried to vlookup via account no. but problem is that sometimes the account number is given and sometimes not.. Moreover , the format also does not match sometimes..
View 9 Replies
View Related
Apr 25, 2013
Currently I have two tabs one with six columns of data. On a separate tab I have a matrix of two of the criteria. I would like to map the company data to the matrix, but recognizing the primary representative has more than one company he/she manages. Hence I don't just need the first entry, but all of his/hers maps to the size columns.
View 5 Replies
View Related
