Ranking By Multiple Criteria

Oct 3, 2008

I have attached an example worksheet of something that I am working on. My references refer to this sheet.

Column Descriptions:
B = agents.
C = the agent's supervisor.
D = the agent's score.
E = the agent's overall rank in comparison to all other agents.
*F = the agent's rank in comparison to the other agents on the supervisor's team.

I need a formula (non-VBA) that will fill column F. That is, a formula that will compare agents on a specific team, based on their QA scores. Though the example data range is small, this will be applied to roughly 600 agents, split up among roughly 40 supervisors. I am currently using Excel 2007.

If anybody has any ideas, suggestions, or solutions, I would greatly appreciate the help, as I have been beating my head against the desk in my attempt to figure this out.

ADVERTISEMENT

Ranking Values Against Multiple Criteria

Oct 7, 2009

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.

ACEFK
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:

=IF(ISERROR(RANK(C8,C$5:C$105,1)+ SUMPRODUCT(--(C8=C$5:C$105),--(E8>E$5:E$105))),0,RANK(C8,C$5:C$105,1)+SUMPRODUCT(--(C8=C$5:C$105),--(E8>E$5:E$105)))

I've attached a file.

View 7 Replies View Related

Multiple Criteria Ranking To Ignore Duplicates

Apr 2, 2008

I posted a thread a while ago asking for a formula to rank based on multiple criteria (Rank On Multiple Criteria). The following; = SUMPRODUCT(--($A$2:$A$50=$A2),--($B$2:$B$50=$B2),--($D$2:$D$50=$D2),--($G2>$G$2:$G$50))+1)

works an absoulte treat, however is there a way to modify it so that does not allow duplicates? The data in the G column refers to time values acheived during testing of athletes, the problem arises when two athletes achieve the same score and thus they are ranked the same. However, body mass is also measured (F column) and in essence the athlete with the lower body mass has achived the better score and thus should be ranked accordingly.

View 3 Replies View Related

Ranking Without Zero And Ranking On Multiple Factors?

Aug 12, 2014

I am facing some problem with ranking formulae. Attached is a file illustrating them.

Problem 1: Ranking without zero I have a set of data for products. They have a number next to it. Some of the products have a ZERO value next to them. I want to rank the data in an ascending order while ignoring the products with ZERO value next to them.

Problem 2: Ranking on multiple factors I have a set of data for 50 products. There are 5 factors based on which I want to pick the best and the worst. Each factor has a value which can be a negative number, zero or positive number.

This is what I am doing presently: Ranking the products on each factor in a separate column. Points are assigned based on the ranking, i.e. if the ranking of "product A" on Factor 1 is 32, then it gets 32 points It is assumed that equal importance [weight] is given to each factor, thus average points is calculated based on ranking of each product on each factor. Average points are again ranked in descending order The top & bottom 10 are picked from this ranking.

Number of products and number of factors can be different for each analysis. Presently I am giving equal importance to the rankings for each of the factors, what if I have to give varied importance [weight] to the factors.

View 1 Replies View Related

Ranking With Two Criteria

May 6, 2014

Formulas for ranking by column "PTS" and then by "DIF" if the PTS are equal for two or more teams...

View 5 Replies View Related

RANKING Set Of Figures Using Criteria

Jul 11, 2012

I'm wanting to Rank a set of figures in one column but use criteria in another column to pick up the array which is used. I don't want any figures to be ignored so will have multiple rank 1's 2's etc. Would also be handy if I could use multiple criteria.

View 9 Replies View Related

Ranking Based On Criteria

Feb 27, 2014

I have a table that has multiple products and price points in it and I would like to add a field that ranks the products by price but for only against that product.

Product Price

Result needed is the Rank only by product based on Price

A
150
2

A
155
3

B
130
1

[Code] ........

View 2 Replies View Related

Ranking Based On Criteria With A Tie Breaker

Jan 15, 2010

I've got 3 columns of info.. Name, Org, #Sales, $$..

I need to rank the list by:

Org, $$, #of Sales (as the tie breaker)

So, I need it to look for a specific Org Dept in Column B, rank the Dollar Amounts in column C, and if there are any ties, go to #Sales in column D and use the biggest number there to break the tie.

I've found 2 formulas..

This one was awesome for looking for the specifc Org Dept and only ranking those numbers that matched the designated Org Dept. (but this can't break any ties)..

IF(ISERROR(MATCH($O3,LARGE(IF($P$3:$P$53=R$2,$O$3:$O$53),ROW(INDIRECT("1:"&COUNTIF($P$3:$P$53,R$2)))),0)),"",MATCH($O3,LARGE(IF($P$3:$P$53=R$2,$O$3:$O$53),ROW(INDIRECT("1:"&COUNTIF($P$3:$P$53,R$2)))),0))

Then there's this one that broke ties, but can't recognize a specific Org Dept that I need..

=RANK($O3,$O$3:$O$53)+SUMPRODUCT(($O$3:$O$53=$O3)*($F$3:$F$53>$F3))

View 9 Replies View Related

Change Ranking Between Two Rows Based On Some Criteria

Sep 5, 2013

I would like to change the ranking between two rows based on some criteria with macro.

View 6 Replies View Related

Ranking With Multiple Columns

Apr 26, 2014

I have a project that I am unable to figure out the best way how to accomplish. I have five players on our basketball that are having a contest. Each player took 25 shots. I need to rank each player's shots individually. For instance, Andrew made 24 and 23 (4th and 1st attempts respectively) so he should be 1st and 2nd while Curtis would get 3rd because he was next with 22 made shots on his last attempt.

I would like to rank the numbers multiple columns (columns B thru F) in column G (not shown but understood).

If this is not possible, is there a way to list ALL the numbers in column G using a formula (the number of students will continue to grow so I am unable to simply enter =B2 in cell G2 and restart with =C2 in cell G7 (after five entries) because the number of students will grow.

A
B
C
D
E
F

[Code]...

View 3 Replies View Related

Ranking Values And Ranking Duplicates The Same Rank

Mar 30, 2014

Ranking values and ranking duplicates the same rank I'm looking to rank a group of numbers that will possibly have duplicate numbers that I want to show as a tie but have no break in the rankings.

Example:
20
18
18
15
12
11
11
9

The ranking I am looking for is:
1
2
2
3
4
5
5
6

View 2 Replies View Related

Ranking Data With Multiple Columns?

Feb 14, 2014

I have a spreadsheet with values as per below

I can rank the data based on descending values in Columns B & C using the formula in cell B2

=RANK(B2,$B$2:$B$11)+SUMPRODUCT(--($B$2:$B$11=B2),--($C$2:$C$11>C2))

What I trying to do is also rank the data based on the values in column D as an additional tie-breaker

RankScore 1Score 2Score 3
1412315
2402420
2402422
4402030
5392015

View 3 Replies View Related

Ranking Based On Multiple Columns?

Feb 14, 2014

I have data in a number of columns in Excel which I want to sort on column B, then C, (Then D) however the formula I have entered is sorting based on column C below in ascending rather than descending order, how I can change this

=RANK(C2,$C$2:$C$4)+SUMPRODUCT(--($E$2:$E$4

View 2 Replies View Related

Ranking Multiple Columns And Second Place

Mar 25, 2007

******** ******************** ************************************************************************>Microsoft Excel - Win_calc.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutQ21=
QRSTUVWX21TickerLowestLowest*NameBOALEHBEARSecond*priceSecond*name22AT*US95/BEAR10210095**23ARW*US50/LEH605070**24BSX*US65/LEH666567**25CBS*US53/BEAR/MS555453**HVOL8*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

This is my general template. I get prices from BOA, LEH AND BEAR on the four companies listed. Then I use the Min function to tell me the best price (I'm buying). Then I use some IF statements to tell me who's the "Lowest Name" (lowest price):

=IF($G2=$C2,"/"&$G$1,"")&IF($H2=$C2,"/"&$H$1,"")&IF($I2=$C2,"/"&$I$1,"")&IF($J2=$C2,"/"&$J$1,"")&IF($K2=$C2,"/"&$K$1,"")&IF($L2=C2,"/"&L$1,"")&IF($M2=$C2,"/"&$M$1,"")

It checks if a cell is = the lowest cell value, then returns the header. This formula refers to different columns than my displayed sheet because Im hiding so cells and I just copied this to another section of my sheet. There's probably a better way to do this but this is what I have do far. I use this for 50 company names and 5 or six dealer banks (BOA, LEH, etc)

I'd like column that gives me the Second Place price and one that gives me the associated dealer (BOA, LEH).

View 9 Replies View Related

Ranking Multiple Columns By 1000th Inch

Jul 5, 2006

I have the following data (measured in 1/1000th of an inch)(I have rounded down in this example though!!!)

NameABCDEAGGReal RANK
AAAAA0.110.120.090.120.270.1422
BBBBB0.120.1140.080.1350.2610.1421
CCCCC0.350.170.170.160.110.1923

copied down to a minimum of 100 rows (names) The AGG is the sum(a:e/5)
When u use RANK(agg,agg1:agg100,1) It ties Names AAAA and BBBB as they both have AGG of 0.142. Problem is the rule book states ties are split by the smallest of the results for each competitor in columns A to E. In this example BBBB is 2nd as he has smallest result in Column C being 0.08.

I have tried the AGG+1/min(a:e) and still end up with incorrect results. The problem is the numbers are so small and you have to split ties all the way to the 100th person involved. I cant use sort either as the data has to be printed in alphabeticall name order all the way to 100 names with their ranks recorded against the names.

View 11 Replies View Related

Ranking Based Off Multiple Ranges In Same Column

Jan 1, 2014

I have a list of research material based off postcodes, travel time in those areas and what I want to do is rank the the travel times in those ares within a third column.

What I have so far in design terms is this:

Origin Base
Time
Time Ranking
AB10
00:10:00
AB10
00:12:00

[Code]...

So basically I'm trying to have column c look at a and check for the lowest value in b against all other rows that have the same constant value e.g. AB10 in column a. Then to distinguish them somehow for example marking the first '1'. Also then to be able to do it for second and thrid ranking value.

What do people think? Is this doable? I think maybe a min if array but keep tripping on the fact that the ranges are all locked in a single column and inconstant sizes.

The data comes in this format and is quite large so manual editing would be a last resort.

View 8 Replies View Related

Lookup Multiple Criteria & Sum Result On Multiple Criteria

Oct 21, 2006

I am trying to solve a problem. I am currently using this formula
= SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)

This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.

So the info looks like this in excel
MARKETING 200612 -10
MARKETING 200701 -25
MARKETING 200708 -50
ECONOMICS 200709 -30

The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.

View 5 Replies View Related

Multiple Criteria SUMIF Using Column As Negative Criteria?

Mar 24, 2014

I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.

View 1 Replies View Related

Macro For AverageIFS With Multiple Criteria In Same Criteria Range

May 18, 2014

Macro for AverageIFS, with multiple criteria in the same criteria range....

View 9 Replies View Related

Multiple Criteria Count Between 2 Dates & Other Criteria

Jul 30, 2008

I have a spreadsheet which lists letters issued, the date issued and the potcode. I need a formula that counts, on a weekly basis, the number of letters issued to postcodes in Sutton and Bexley.

I have a count sheet set up on one worksheet and the list of postcodes applicable to each area are listed on another worksheet. I have been playing around with sumif, sumproduct etc, but these don't seem to work as I am pointing the formula to a list of postcodes and not an individual postcode. For example, the formula for one week needs to tell me, the number of letters issed to Sutton between 26/07/08 - 01/08/08. This is what I tried -

=SUMPRODUCT(('MU63 NC'!$F$2:$F$10>=COUNT!B$2)*('MU63 NC'!$F$2:$F$10<=COUNT!B$3)*('MU63 NC'!$D$2:$D$10=Sutton!$A$2:$A$50))

But I get #N/A - if I change the last refernce to a specific cell instead of a range it works, but this will make the process very lengthy as there are lots of postcodes!

View 5 Replies View Related

Multiple Options Cell Values Based On Multiple Criteria Variable Inputs?

Mar 18, 2013

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")

[Code].....

View 1 Replies View Related

Excel 2010 :: Counting Cells With Multiple Criteria On Multiple Sheets In Workbook

Aug 5, 2012

I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$R1"),2*(AND("'"&$H$1:$H$43&"'!$E1">"'"&$H$1:$H$43&"'!$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$S1"),2*(AND("'"&$H$1:$H$43&"'!$G1">"'"&$H$1:$H$43&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$T1"),2*(AND("'"&$H$1:$H$43&"'!$I1">"'"&$H$1:$H$43&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$U1"),2*(AND("'"&$H$1:$H$43&"'!$K1">"'"&$H$1:$H$43&"'!$L1"))))

but it returns a value of zero each time. Clearly there is an error in the formula.

Here is some background:
-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.

View 1 Replies View Related

Pull Data From Multiple Cells And Concatenate In Single Cell Using Multiple Criteria

Aug 31, 2012

I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:

TABLE 1:

Project
Benefit Type
Delivered or Enabled
Benefit

PJ1
Financial
Delivered
Saving of $4M over 24 months.

[code]....

I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.

TABLE 2:

Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled

[code]....

So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:

Continued maintenance of hardware.

Increased capacity.

View 1 Replies View Related

Send Multiple Files Based On Criteria To Multiple Email Addresses?

Jun 29, 2014

I have a directory which contains many files, they are all names based on their locations. eg. Burwood-File1.xls,Burwood-File2.xls,Burwood-File3.xls etc

I have a master that which will contain the branch in the first column.

I have defined the directory location in a separate sheet as well as email template.

When I click on the Send Email button I want ti to attach the files that match the Branch name.

The Branch list will constantly grow.

View 2 Replies View Related

Populating Fields In Multiple Cells Based On Criteria In Multiple Columns?

Feb 24, 2014

Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.

In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.

CURRENT SHEET

ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89PENS ABC STATIONERY CO LLC [code]....

The attached example file may be a better alternative to view this problem.

View 2 Replies View Related

Return Multiple Results In Multiple Cells Based On Criteria In Yet Another Cell?

Feb 1, 2012

Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)

e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated

Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.

e.g.
AAA blue round elongated
BBB orange
CCC smelly

View 3 Replies View Related

Search Multiple Criteria If Match Append Multiple Cells To One Cell?

Apr 28, 2012

I am having such a difficult time creating a macro that will reduce the 5+hours I have to spend each week manually copying & pasting all of this data. I making an IMMENSE difference in this worker bee's life!

I have a workbook with two sheets (Sheet1 & Sheet2). Sheet1 has license #'s in column A and the state that the license belongs to in column B like this:

COLUMN ACOLUMN B11111Alaska11112Alabama11113Arkansas11114Arkansas
Sheet2 has three columns. Column A has the license #'s, column B has the state that the license belongs to and Columns C shows a line-of-authority tied to that license #.

COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty11111AlaskaCasualty11112AlaskaLife11112AlaskaHealth11112
AlabamaProperty11112AlabamaCasualty11113ArkansasLife11113ArkansasHealth11114
ArkansasLife11114ArkansasHealth12345ArizonaProperty

I'm trying to write a macro that will compare the license # and state in Sheet1 to the license # and state in Sheet2. If it matches, append the contents of Column C to the corresponding row in Sheet1.

Here's the thing...Sheet2 contains the entries for all licenses in the company (so this table is HUGE). And there are multiple entries for each state license # (notice how there's two entries above for AK license # 11111 - one for the Property line and one for the Casualty line.

After my macro is run, I want Sheet1 to show all the lines-of-authority on a single line. So if I ran my macro on the above example, after it's run I would have this in Sheet1:

COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty Casualty11112AlabamaProperty Casualty11113
ArkansasLife Health11114ArkansasLife Health

View 5 Replies View Related

Excel 2003 :: Formula For Counting Values Across A Range Using Multiple Criteria Across Multiple Sheets

Feb 9, 2014

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.

View 12 Replies View Related

Excel 2010 :: Lookup Multiple Criteria Across Multiple Sheets?

May 28, 2014

I have a Excel 2010 workbook used to rota in a large amount of staff for a call centre, which is split into four teams. Each sheet corresponds to a month of the calendar year eg Jan201, Feb 2014 etc..

What im trying to do is put in a sheet at the front of the workbook that I can select the team, which populates the list of staff in that team and then checking across a specified date range gives the shifts that those respective staff will be working for the set time period (probably be looking at a seven day period and a 1 month period). (This in turn will be printed out to give to the staff members.)

View 2 Replies View Related

Count Rows Meeting Multiple Criteria Of Multiple Values

Aug 10, 2009

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.

View 2 Replies View Related

Delete Rows Where Multiple Column Meets Multiple Criteria

Sep 29, 2011

Need to create a macro?

Delete rows where multiple column meets multiple criteria.

detail:
delete rows where
Column H is less than 10000
AND
Column C is empty(blank)

Those 2 criteria have to occur at the same time..

View 1 Replies View Related

INDEX With Multiple Criteria And Returning Multiple Rows But Only The Two Most Recent

Jun 10, 2013

I have a spreadsheet of prices (raw data), and I need to pull out both the most recent price for each item for a specific customer code for each item, as well as the second most recent price, (essentially the old price), for that customer code for each item.

I have columns for ITEM (code), PTC (Customer Code), PRICE, and EFF (Effective Date). For each item code, there are multiple prices for multiple customers, such as in the linked workbook.

I am currently using the formula, =INDEX(PRICE,MATCH(1,(F2=ITEM)*(G2=PTC),0)) to pull out the last price. This works because the last price is on top based on sorting. What I need is a formula that pulls out the next oldest price for that item/ptc combo. I tried using the SMALL function to no success.

[URL]

View 2 Replies View Related

Summing Data From Multiple Workbooks Based On Multiple Criteria

May 14, 2014

I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.

Sample:

Location A Class:AA Segment: Sports Bar Rep: John Smith

Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1

I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.

I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.

View 2 Replies View Related

Copy Rows Meeting Multiple Criteria On Multiple Columns

Dec 18, 2006

1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run.
2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L
3. In Column D, I want to specify 3 criterias namely A, B and C
4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo
5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet

View 9 Replies View Related

How To Use Match And Multiple Criteria To Fill Multiple Cells

Nov 21, 2012

I am using this formula but I don't know how to get it to populate more than one cell.

Here is the formula:

=INDEX(APPROVED!$A$3:$A$1000,MATCH($F$4&$H$1,APPROVED!$D$3:$D$1000&APPROVED!$C$3:$C$1000,0))

It has multiple matches but I can only see the first match in the cell.

View 1 Replies View Related

Grabbing Data With Multiple Criteria Across Multiple Sheets

Jun 24, 2014

I've attached a sample sheet to this message.

What I'm trying to do is grab data from the raw data sheet and paste it into the master sheet based off of certain criteria. I want to list any accounts that are not correct.

For example, I want to look through the Raw Data sheet, find the account that contains campaigns which are not correct, and then list the name of the account on the Master sheet. I only need the account name listed once on the Master sheet.

Sample Data.xlsx‎

View 4 Replies View Related

Index Match - Multiple Criteria And Multiple Matches

Dec 2, 2013

I would like to extract Bill cost from "rule" sheet and insert it to "data" sheet using index-match functions. My problem are multiple criteria and multiple matches.

The criteria are Column: Cost type, Power, Penalty Category (The logic goes like AND function).

And I want it to extract "Bills" value from "rule" sheet.

There are multiple matches in all columns (Cost type, Power, Penalty Category) which cause wrong extraction.

How do i solve this problem with index-match function?

View 3 Replies View Related

Search Multiple Values In A Cell With Multiple Criteria

May 22, 2009

I am trying to search for multiple values in a cell with Multiple criteria.

E.g: Please find attached the sample excel data.

I tried using VLOOKUP which is not working as I am having multiple values in column A.

View 9 Replies View Related

Multiple IFs To Display One Result Based On Multiple Criteria

Mar 18, 2014

I've got a matrix combining actions on the left hand side (Col A) and owners on Cols B to F. I have drop down list for each action/owner combo: N/A, OK, KO, TBD.

Owner #1
Owner #2
Owner #3
Owner #4
Owner #5
Results

Action #1
OK
TBD
N/A
N/A
N/A
OK

[code]....

I would like to create a formula in Col G that does the following:If any of the values in columns B to F equals OK, then OKIf any of the values in columns B to F equals KO, then KO I've been tinkering with multiple IFs and quite rightly got bogged down as too many criteria to take into account...

View 4 Replies View Related

Multiply Multiple Columns Based On Multiple Criteria

Jan 23, 2012

So, another question with regards to this sales sheet that i am setting up. I want to be able to take the information below and for each of the people listed (i.e. by their initials) i would like to...

1.) Take their associated % (Columns C, E, G)
2.) Multiply the % by the Revenue (Column H)
3.) Sum up the total for each of the names (Locatedin Columns B, D, F)
4.) Only sum up the ones that occur in Month 1 of the Year 2012

I have tried multiple ways with a Sumproduct but none of them have worked. I would love to be able to do this with a single formula without having to add another column, for instance, to track what MonthNum it is. I tried using some kind of offset, but i could never get it to work.. For instance to look through the range, find the initial go over 1 column to the right and multiply that % by the revenue if the Month is 1..

Date (A)Rec1Name (B)Rec1Pct (C)Rec2Name (D)Rec2Pct (E)Rec3Name (F)Rec3Pct (G)Revenue (H)1/1/12AD25.00%RU25.00%BF25.00%1000.001/1/12AD25.00%RU25.00%PNF25.00%2000.001/1/12BF50.00%00.00%00.00%1200.002/1/12BF50.00%00.00%00.00%1000.002/1/12BF50.00%00.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1500.004/1/12AD16.67%RU16.67%BF16.67%1500.004/1/12BF50.00%00.00%00.00%1500.00

View 3 Replies View Related

Sum Of Multiple Cells Within Multiple Sheets With Varying Criteria

May 5, 2013

I have a spreadsheet that has about 50 sheets and a summary sheet at the front the layout of the summary sheet is column A has skill sets from A4 to A20 and across the top it has week commencing dates from F3 to about BA3 (dates go from w/c 06/05/13 to 08/12/14). On the other sheets they have the dates in the same columns but the skill sets vary as to each project, some projects may have two of the same skill set in column A because there is two people needed who can do the same thing, I need to total up the days over the sheets for each week and skill set.

I have been using the SUMIF formula as follows:

=SUMIF('Sheet2'!A8:A10,'Summary'!A4,'Sheet2'!F8:F10)+SUMIF('Sheet3'!A8:A15,'Summary'!A4,'Sheet3'!F8:F15)

And so on for each sheet and then change the formula for the next skill set and then again for the next column.

Is there a way of doing this without having to enter a formula into each cell, at the moment I am coping and pasting the formula into word to do a find and replace to change the SummaryA4 to SummaryA5 etc and then into the next cell for the corresponding date.

So to make this seem like it makes any sense I am looking for say an 'Administrator' in all the project sheets over all the dates and for it to add it up for the summary sheet. Under the corresponding week against the skill set.

So it would be F4:F20 for w/c 06/05/13, G4:G20 for w/c 13/05/13 and so on until all skill sets.

View 5 Replies View Related

ADVERTISEMENT