# Formula To Select Data Using Multiple Criteria

Feb 11, 2010

In the attached vacation accrual spreadsheet, I created two tables: Exempt hours earned, and also, Non Exempt hours earned. I have all employees on one worksheet. The hours accrued (Vacation Level) are different for Exempt and Non Exempt. I need: What is the best formula to autofill the Vacation Level column (H):

It needs to choose exempt or non exempt from column (C) then reference the # Years of Service column (F) to find the correct vacation days earned as defined in the two tables on the right, or those numbers could be used in the formula itself. . . I JUST DON'T KNOW! Nothing I am trying is working!

create a formula! Use the spreadsheet to maybe make name ranges? nested IF statements, or LOOKUP, or even VLOOKUP??? Or something that works. not to repeat, but. . . Nothing I am trying is working!

## Select Multiple Cells Based On Multiple Criteria?

Sep 25, 2013

I would like a macro that select all cells based on multiple criteria. It's a big sheet, but I've made it smaller in the following example.

In row 1, there are 2 headers, in B1 (electricity) and C1 (gas) In column A, there are 3 houses, in A2, A6 and A10. The blocks of cells in between (e.g. B2:B5) have 2 headers, 'period' in B2, and 'account number' in B4. B3 and B5 have a list of options in them.

I want to select cells that meet certain criteria. Either electricity or gas, and either period or account number. So, if in cell D1 I choose electricity and in D2 period, I would like to select B3, B7 and B11. If I choose gas and account number, I wish to select C5, C9 and C13.

## Formula To Retrieve Data With Multiple Criteria And Dynamic With Dropdown List

Jan 6, 2014

I have a complicated situation with Excel since I need a formula that can SUM data from the Pull 1 worksheet into the summary Sheet. I attached my sample for your reference because it is a bit complicated therefore I can not explain. It is better if you look at my Summary Sheet, need formula that can retrieve the same information.

Formula Test.xlsx

## Macro To Select Multiple Files And Copy Them Based On Criteria?

Mar 10, 2013

This is what I am trying to achieve:

1) Prompt user to select a file (or multiple files)

2) Copy the files that meet certain criteria to a folder

My attempt (fail):
VB:

Sub FILES2SFTP() Dim FileNames As Variant Dim I As Integer Dim fso As Variant Dim Data As String ChDrive "G:" ChDir "G:TEST" Data = InputBox("Enter the date", "Enter the date", Format(Application.WorksheetFunction.WorkDay(Date, -1), "yyyymmdd")) Set fso = [code]....

If fso.getfilename(FileNames(I).Name) = ("Name1" & Data & ".xls" Or "Name2" & Data & ".xls") Then

## Select Multiple Criteria From Validation List Dropdown (populate Same Cell)

Aug 8, 2012

I have a validation list drop down box set up to reference data in a defined name list. I would like to be able to select multiple values from this list to appear in one cell (preferably separated by a comma). For example, the defined name list contains the following data:

Choice 1
Choice 2
Choice 3
Choice 4

I would like to be able to populate one cell with the following via the drop down list: Choice2, Choice 4

Is this possible and if so, how?

## Formula To Have Excel Select Date Of Month Based On Certain Criteria?

Feb 24, 2014

I am looking for a formula that will select a date in the month based on certain criteria. Found the choose function but not sure if I can really get that to work. I basically have a list of clients, with zip codes, restricted days of the week - and would like the system to group them by zip code and select the best day of the month to schedule an inspection but not pick he restricted day. The goal being have zip codes scheduled together - but on a day other than garbage day. Is this even doable?

## Coding To Select Data Criteria Wise

Jul 29, 2013

I need to filter data on criteria wise and to move on specific sheets accordingly.

Example: sheet 1 contains all the details such as starting with AAA - 10 rows, BBB - 12 rows, CCC- 15ROWS ,DDD-13 ROWS etc in column 1.

I have created separate sheets for AAA, BBB, CCC etc

now I need all the details of AAA (in sheet1) to be moved to specific sheet AAA which i have created and so on.

I tried the below coding but there are few dependencies found.

Selection.Insert Shift:=xlDown
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="AAA"
ActiveWindow.SmallScroll Down:=-6
Rows("169:184").Select
Selection.Copy
Sheets("AAA").Select
ActiveSheet.Paste

## 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.

## Multiple Select Cells With Formula Only

Sep 29, 2008

Is it possible to select cells with conditions below using formula only?

Yes, VBA is easier, but I want to know if this is possible using formula.

1. selected cells (requested): A1, A3, A6, A10, A15 .... total 20 cells.

2. the increasement between them are: 2, 3, 4, 5, 6, 7, 8 ,,,,

## 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.

## 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

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.

## Excel 2007 :: Using Formula On Filtered Data - How To Select Only Visible Data

Jun 1, 2011

I have a 5000 line table I am filtering by a few columns, and I'd like to calculate an exponential trendline value.

=INDEX(LINEST(LN(R1059:R1167),W1059:W1167),1)

But I actually don't want all the values from R1059 to R1167 - I want to select only the displayed values (R1059, R1068, R1077, etc). Is there a way to select only display values to use in a formula? The problem is it would be a lot of manual work to select them all - there are 50 or so instances I would have to select 13 manual values.

I am using Excel 2007 on XP.

## 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‎

## Multiple IF And AND Criteria Within Formula

Jan 16, 2014

I need to create a formula that takes into consideration 9 possible scenarios using IF and AND. I have 3 performance measures (exceeds requirements, meets requirements, improvement needed) and 3 potential measures (low, medium, and high). I have a spreadsheet where each individual is rated for both. Each combination correlates to a numeric rating on a 9 box grid. I need to include every option in my formula so the correct rating is determined for each individual. I've tried several versions on my own, and can't get past one set of conditions.

Performance PotentialRating
Exceeds ExpectationsLow4
Exceeds ExpectationsMedium7
Exceeds ExpectationsHigh9
Meets Expectations Low2
Meets Expectations Medium5
Meets Expectations High8
Improvement NeededLow1
Improvement NeededMedium3
Improvement NeededHigh6
Unacceptable Low1
Unacceptable Medium3
Unacceptable High6

## Sum Formula With Multiple Criteria

Aug 17, 2007

I'm looking for a formula which will filter the area expiring in any given year per property. An example would be (from the attachment) - what percentage of area of Property 2 is expiring in 2010? Answer is 50.94%.

## Vlookup Formula With Multiple Criteria

May 12, 2014

I am trying to modify an existing nested vlookup formula to include one more condition. I attached the excel data file. There are two tabs:

Tab #1 - Performance
Column B (Email Send Date): can be a repetitive date, something like
Row 2. 4/25/2014
Row 3. 4/25/2014
Row 4. 4/25/2014
Row 5. 4/25/2014
Row 6. 5/2/2014
Row 7. 5/2/2014
Row 8. 5/2/2014
Row 9. 5/9/2014
Row 10. 5/9/2014
Row 11. 5/9/2014

Column F (Product ID): can be same product for different Email Send Date. For instance, Row 2 & Row 9 have the same product ID - 128 and Row 5 & Row 10 have same product ID - 131.

Row 2. 128
Row 3. 129
Row 4. 130
Row 5. 131
Row 6. 567
Row 7. 897
Row 8. 987
Row 9. 128
Row 10. 131
Row 11. 234

Column R: Units Sold - need to retrieve the units sold value from Column D - Units Sold in UnitsSoldOnlineVlookup tab.

The formula needs to lookup the Units Sold from a table in a different tab, named UnitsSoldOnlineVlookup. This table contains the following columns:

Tab #2 - UnitsSoldOnlineVlookup
Column A - Email Send Date
Column B - Product Description
Column C - Product ID
Column D - Units Sold

Before Product IDs were different for each Email Send Date and I successfully used this formula:

=IF(ISERROR(VLOOKUP(F2,UnitsSoldOnlineVlookup!\$C\$2:\$D\$31000,2,FALSE)),0,
VLOOKUP(F2,UnitsSoldOnlineVlookup!\$C\$2:\$D\$31000,2,FALSE))

Now I need to embed one more condition to this formula - lookup Units Sold for the Product ID as well as the email date:

lookup Units Sold for a Product ID for a corresponding Email Send Date in UnitsSoldOnlineVlookup table and return Units Sold into the corresponding cell in the Performance tab.

I thought to use MATCH function in addition to IF and ISERROR functions but I it doesn't work - I know it is wrong.

=IF(MATCH(B2,UnitsSoldOnlineVlookup!\$A\$2:\$D\$31000,0),
ISERROR(VLOOKUP(F2,UnitsSoldOnlineVlookup!\$C\$2:\$D\$31000,2,FALSE)),0,
VLOOKUP(F2,UnitsSoldOnlineVlookup!\$C\$2:\$D\$31000,2,FALSE))

## Formula To Sum Multiple Match Criteria?

Jan 28, 2014

I am needing a formula to sum info from 1 book to another based on two lookup criteria. Its the sort of info you can easily get with pivot tables or filters but my colleague needs a copy and pastable formula so it can be automated.

There are two workbooks Order Index example.xlsxCash Flow book example.xlsx; one called "Order Index" has a list of orders placed along with supplier name (Col C), date due for payment (Col H) and value of order (Col I). The other workbook, used by a colleague is called "Cash Flow" and contains a list of supplier names (Col B) with row 1 containing months (1st Jan 2014, 1st Feb 2014 etc).

Under each month on the "Cash Flow", I need to show the sum of orders due for payment within that month, for each supplier named (in Col B). In other words, sum the figures from the "Order Index" based on supplier name and date due for payment.

Often, on the Order Index there is more than one order per month from a supplier and the dates due are specific days (10th Jan, 21st Jan etc) and these need summing up for the 1st Jan 2014 Column in the Cash Flow book.

## Multiple Criteria Met In A Sumproduct Formula.

Oct 1, 2009

I have 2 columns of data being populated by vlookups

Column H is both numbers and text. Column I is Text and blanks. I need to be able to find only numeric values in column H greater than 0 and compare those occurrences with the corresponding cells in column I and if column I has a text entry (not a blank space) than to count that and at the end give me a total number of times these 2 criteria are met. As an example.

If column H has a text entry then don't count it.
If column H has a number less than zero then don't count it.
If column H has a number greater than zero but column I is blank then don't count it.
If column H has a number greater than 0 and column I has a text entry then count it.

I've tried using many variations of a sumproduct formula and none of them work.

This formula counts all instances where column I has a text entry without checking column H for a number greater than 0.

=SUMPRODUCT(--(H2:H110>0),--(I2:I110<>" "))

Or it's possible that the formula is counting the text entries in column H as a number greater than 0 but I'v tried excluding text using this..

=SUMPRODUCT(--(H2:H110>0&<>"*"),--(I2:I110<>" "))

but this causes an error in the formula somehow that I can't figure out. I even tried this

=SUMPRODUCT(--(H2:H110>0&"*"),--(I2:I110<>" "))

and I get a formula that counts only the times text appears in column H and column I together which is not what I want either.

I'm self-taught on Excel so I know there's a lot I'm not understanding about creating formulas like this but I need to have this working by Friday and I just want it to work.

## Sumif Formula Multiple Criteria

Jan 27, 2010

Can anyone tell me what is wrong with the formula on page 1 cell F56? it is not adding the 666 gallons at the end of column F for 426 - cc. Any ideas?

## Multiple Criteria For Greater Than In One Formula?

Nov 15, 2013

formula stating that:

if a number is greater than 1 add one 1 box (250) and if the number is greater than 9 add 2 boxes and is its greater than 18 add 4 boxes

i want this all in one formula,

## Formula Array With Multiple Criteria

Dec 6, 2012

How could I add ">0" to the below formula array?

{=IFERROR(AVERAGE(IF(Reports!\$A\$1:\$A\$4000=A5,Reports!\$H\$1:\$H\$4000/86400)),"-")}

Reports!\$A\$1:\$A4000 = column A lists multiple agent's names
A5 = a specific agent's name
Reports!\$H\$1:\$H\$4000 = column H contains a number which has been converted from time to a number

86400 = number of seconds in a day (the cell where I am placing the formula converts the number back to a time format)

The average I'm looking for will give me an average of all times which are greater than zero.

## Multiple IF Criteria - To Set Calculation Formula

Mar 10, 2014

Attempting to calculate assignment hours based on three fields -

A - Date
B - Txt
c- Txt
D - Time [Start]
E - Time [END]
F - Number [Off]
G - Number [Travel]
H- Number (Calculated Total Project Hours)

I HAVE THE CALCULATION WORKING WITH THIS FORMULA
=(E5-D5)*1440/60-F5+G5

My goal [If Activity is NOT NULL and Start Time is NULL] Time would Default to 8.0 - ELSE use the formula to calculate [ Some projects I don't have a set start/end - and default to 8.0 hrs.

## FREQUENCY Formula With Multiple Criteria

Jan 10, 2008

I hope someone can help me with adding an additional criterion to the formula below, which works and provides an accurate answer. The array formula is:

{SUM(IF(FREQUENCY(IF('Sheet1'!\$O\$5:\$O\$315="",IF(Sheet1'!\$B\$5:\$B\$315"",MATCH("~"&'Sheet1'!\$B\$5:\$B\$315,'Sheet1'!\$B\$5:\$B\$315&"",0))),ROW('Sheet1'!\$B\$5:\$B\$315)-ROW('Sheet1'!\$B\$5)+1),1))}

Where Column O has either a date or is blank.
Where Column B has a unique ID number which occurs more than once.

What I need to add is one more criteria from Sheet1,
Where Column P has a unique names which occurs more than once.

I tried two changes:
1. Adding an additional IF statement as follows: SUM(IF(FREQUENCY(IF('Sheet1'!\$P\$5:\$P\$315=A\$8,IF('Sheet1'!\$O\$5:\$O\$315="", ... Which I couldn't get to take; and
2. Adding a IF AND statement as follows: SUM(IF(FREQUENCY(IF(AND('Sheet1'!\$P\$5:\$P\$315=A\$8,'Sheet1'!\$O\$5:\$O\$315=""), ... Which took but gives me a #VALUE error.

## Select Multiple Items From Data Validation List?

Feb 4, 2014

I have created a DAta Validation List and it only select one item. I want it to select as many items that is on the list but i can't get it to work for my main worksheet I am working on. I can't figure out why I can't get it to work for the work sheet "This Don't" but it works for the worksheet "This Works" why and how I can change it so both Reason (Column F and H can) select mutliple items on there. Data Sheet.xlsx

## Formula For Averaging Array Given Multiple Criteria

May 19, 2014

I am looking for a formula that averages the numbers in an array if they match the row and column text-based criteria. Based on another thread, I found and edited the following formula. However, it is giving me incorrect numbers.

[=AVERAGE(IF((\$A\$3:\$A\$275=\$P6)*(\$B\$2:\$M\$2=Q\$4),\$B\$3:\$M\$275))]

I have attached a sample workbook that includes the broken formula.

Average Formula Error.xlsx‎

## Array Formula For Multiple Criteria Average

Jul 9, 2014

I need an array formula that can take average based on multiple criteria present row wise and column wise.

Check the attached sheet for detail. I need array formulae in yellow space. I have given one result that I need.

Average.xlsx‎

## Index Match Formula For Multiple Criteria?

Dec 3, 2013

In the attached I have a pricing list on sheet 2 based on various criteria and on sheet 1 dropdown lists to match the criteria.

In cell B12 i have an index match that I can not get to return a value.

Is Index Match the correct method of returning a result? Or have I just got the formula wrong?

## Formula To Count Based On Multiple Criteria

Dec 7, 2013

What I require : Column F to be blank as long as the following is true:

1) Column A (Order) cannot be blank.
2) Column E (Status) has to be equal to either "Completed" or "Successful".
3) Column D (Sales) has to have the same date as Column C (Contact), but due to bad data quality each order number can have several rows and as long as the Sale date from one of the other "Duplicate" rows equals the contact date it needs to be counted. This is as long as there is not already a row for the Order that already has Sales / Contact day matching.

In the attached spreadsheet, I have highlighted the rows that should be blank in column F in green. My attempts at formulas to count this are in the columns highlighted in red.

WORKAROUND.xls‎

## Formula For Counting 2 Different Criteria In Multiple Columns?

Jun 17, 2014

Fixed data labels over a number of columns and i want to count the number of times 2 of these appear however when ive tried to use the countifs function it will only count if in the first colum of the range selected and for example if A2 says 'FRANCE' and D1 'RED'

Ie i have been using COUNTIFS('Datasheet'!BB:BE,A1,'datasheet'!BE:BH,D1)

## Index Match Formula With Multiple Criteria

Feb 5, 2013

Is it possible to use an Index/Match formula that looks at multiple criteria?