If Statement Based On Multiple Criteria
Dec 23, 2013I am looking for a way to say
IF A2>9 then B2 returns bronze
IF A2>24 then B2 returns Silver
IF A2> 49 then B2 returns Gold
IF A2>99 then B2 returns Platinum
ADVERTISEMENT
If Statement With Multiple Criteria
Nov 11, 2009My concern is in row 4 and 5 of the attached spreadsheet. The problem is that not all postings for the allocations will take place on or before the date the allocation starts. Therefore, i need a logical formula that builds on what already exists and determines if the "entered" date is > the "start date". and then catches up how far it was behind. Example:
contract 12,000
Contract starts January 1st and ends December 31 (12 Months)
Entered Feb 1
In the January column there chould be zero in feb there should be 2,000 and the rest of the months should be 1,000
If Statement: Multiple Criteria
Oct 31, 2006I want to expand this calculation to include
IF(J5>100,VLOOKUP(A5,INDIRECT("'"&E5&"'!$A$4:$CY$31"),100,FALSE)
=IF(B5=0,0,IF(N5=0,0,IF(B5=1,IF(E5="INSERT",VLOOKUP(A5,INDIRECT("'"&E5&"'!$A$4:$CY$31"),J5+2,FALSE),IF(B5=1,IF(E5="ADVERT",VLOOKUP(A5,INDIRECT("'"&E5&"'!$A$4:$CY$31"),J5+2,FALSE),0),0)))))
If Statement Based On Various Cell Criteria
Aug 11, 2009G5 - can contain True or false
G6 - contains text but the if is on the basis of this cell being "Hand Delivered" or not
I have tried the following to illustrate what I after:
SUMIF Statement With Multiple Criteria In Same Column
Jan 26, 2004I'm trying to create a SUMIF statement that that has an embedded OR statement within, and am unable to make it work.
Basically I want to say if "column Q" equals one of 2 criteria ("cat" or "dog") then sum the corresponding number in "column P" I've tried the following statement, but it is just resulting in a zero:
=SUMIF(Q9:Q32792,OR("cat","dog"),P9:P32792)
I've seen support on statements with multiple criteria in DIFFERENT columns, but not if searching for multiple criteria in the SAME column.
IF And OR Statement (populate A Cell Based On Which Number Meets The Criteria I Define)
Dec 26, 2008I am trying to populate a cell based on which number meets the criteria I define. This is based on sales revenue, so if the revenue is less than $6.5M, I want to use a certain value. If the value is equal to $6.5M but less than $8M I want to use another value and finally if the revenue is greater than $8M i want to use another value. Here's my formula, but it returns $0.
=IF(B2<6500000,Bonuses!J45)*OR((B2>6500000)*AND(B2<8000000),Bonuses!F45)*OR(B2>=8000000,Bonuses!B45)
Multiple Options Cell Values Based On Multiple Criteria Variable Inputs?
Mar 18, 2013I 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].....
Send Multiple Files Based On Criteria To Multiple Email Addresses?
Jun 29, 2014I 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.
Populating Fields In Multiple Cells Based On Criteria In Multiple Columns?
Feb 24, 2014Im 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.
Return Multiple Results In Multiple Cells Based On Criteria In Yet Another Cell?
Feb 1, 2012Worksheet 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
Auto-Populating Multiple Values Based On Two Criteria Based In Comboboxes
Aug 7, 2013I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
View 2 Replies View RelatedSumming Data From Multiple Workbooks Based On Multiple Criteria
May 14, 2014I 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.
Multiple IFs To Display One Result Based On Multiple Criteria
Mar 18, 2014I'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...
Multiply Multiple Columns Based On Multiple Criteria
Jan 23, 2012So, 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
Select Multiple Cells Based On Multiple Criteria?
Sep 25, 2013I 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.
Setup Sumif Statement With Two Criteria Where If Second Criteria Is True
Nov 25, 2013I am trying to set up a sumif statement with two criteria where if the second criteria is true, the total in the sum range returned is divided by two.
I currently have this:
=SUMIF($G$6:$G$41,"digital",I6:I41)
I want to add in "digital/creative" as a second criteria (from the same criteria range) but I only want 50% of the result of these to be totalled.
is this possible?
Add Multiple Values Based On Criteria?
Jul 16, 2014Add Multiple Values Based on Criteria
Search for the word "Clear" in the Task column. If the word "Clear" is found, go to Value column. Add all values starting from "Clear" down to the end of the list. If it is larger than 10, return the word "OK". Otherwise "Poor". If larger than 20, return the word "Good". The length of the list is variable.
Here is the sample worksheet (Please click).
Delete Multiple Row Based On Criteria
Dec 15, 2009if in column A in some row is text "today" then delete this row and delete all rows until in row B wont be empty cell. I have start (If in A is "Today" then ... ) but thas's all
View 5 Replies View RelatedVLOOKUP Based On Multiple Criteria?
Oct 1, 2011I'm trying to create an Excel based dashboard for my company. One part of the dashboard is a Top 20 Accounts based on territory, region, or district. Each of these three areas has source data in a seperate table. I need the top 20 vlookup to pull from the territory table if I enter a territory name in cell B20, pull from the region table if I enter a region name, and so forth. There are 10 different regions, 2 different districts, and 51 different territories.
I'm thinking that the answer somehow involves an IF statement. e.g. If cell B20 equals a region name, then perform the vlookup on the region table. If it contains a district name, pull from the district table. Otherwise pull from the territory table. I'm also thinking I may want to list all of the territories, regions, and districts in their own seperate column so the formula can say something like "If cell B20 is found in column M, pull from the territory table, if it's in column N, pull from the region table.
Search Based On Multiple Criteria
Aug 29, 2013An example of my data is:
Column A Column B Column C
ABC DEF Answer 1
ABC GHI Answer 2
ABC JKL Answer 3
I need a formula that checks both column A for text (ABC) and column B for text (JKL, two different criteria) and give 'Answer 3'. There will a lot of data and it will not always be the bottom answer.
Delete Row Based Upon Multiple Criteria
Dec 7, 2006I am attempting to create a macro that will delete an entire row, based upon multiple criteria. The data looks something like this:
-----Column A-----------Column B---
1.-- Computer_X ------- Software_A
2.-- Computer_X ------- Software_B
3.-- Computer_X ------- None
4.-- Computer_Y ------- None
5.-- Computer_Z ------- Software_A
6.-- Computer_Z ------- None
I would like to delete the entire row if:
1) Column B entry is "None"
AND
2) Column B has entries other than "None" that match Column A
So I do not want to delete a row with a unique name in Column A and "None" in Column B. I only want to delete the rows that have "None" in Column B, and multiple entries for the match in Column A.
Using the data example above, Row 3 and 6 would be deleted. Row 4 would not be deleted, since Column A (Computer_Y) had only one row of data, with "None" entered in Column B.
Dsum Based On Multiple Criteria
Dec 15, 2008Ihave the following headings in a database
CSAMeeting DateMeeting typeCompanyInhouse Products/FundsJV Product 1JV Product 2VenueSales Manager2nd Manager (JP, KMcI SS)Inv. ManagerFilenote IssuedWorking DayNo. of AttendeesCancelledPack RequiredCompliance Required DivisionReason for no ComplianceDate sent to
CompliancePack/Literature
UsedPack Path
i want to use the Dsum to produce the total amount of managers met for a cvertain sales person for a certain type of meeting. I trued thr following but it is returninga #value
=DSUM(A27:V1028,13,AND(3=$BH3,9=BI$2,120))
where 13 applies to the number of managers column
3 is the meeting type
9 is the managers name
12 ensures that its only counted if teh meeting note is done.
Subtotal Based On Multiple Criteria
May 22, 2008I need a formula that will take this information and automatically insert a row, a total, and then another row every time the Product (Column C) or the Account Number (column B) changes. The totals should be under Columns D and E only. But I don't want it to duplicate the results when both the product and the Account number change.
Beginning data: ...
Bring Top 10 Unique Name Based On Multiple Criteria?
Apr 23, 2013In the below data set i have the data ranked by branch and center by value. I am trying to create a summary page that looks at the data set by branch center and rank and brings back the debtor code. The letters above headers represent the columns.
a b c d e f g
BR
CNTR
DEBTOR NAME
2012
Rank
[Code].....
Create List Based On Multiple Criteria?
Apr 13, 2014I am trying to formulate a commission form which looks up the name of the sales person and then displays a list of the deal names withing Q1.
View 1 Replies View RelatedFormula To Count Based On Multiple Criteria
Dec 7, 2013What 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
Display Message Based On Multiple Criteria?
Feb 10, 2014I have created a sheet that calculates a score. Based on that score a determination is made to do one of three things - Nothing, Peer/Manager Review, Review Required.
In addition it was requested now that I make an overall decision for "Review Required" based on multiple triggers. My thought was to create a table from outputs of each element on another sheet, and then trigger off the table. What I am stuck on is using the multiple choices to trigger the message - Below are the choices of which each or a combination of each should trigger "Review Required"
Calculate Review Required
Total Score 'Review Required
Element 1 "
Element 2 "
Element 3"
Element 4"
Element 5"
Element 6"
Element 7"
Element 8"
Element 9"
Element 10"
Element 11"
Element 12"
Element 13 "
Element 14 '20 or more = reveiw
Element 15 '20 or more = reveiw
Element 16 '20 or more =review
Element 17 'Sum 20 Total of above if less than 20 in each
Removing Data Based On Multiple Criteria
Mar 30, 2009I need to remove any rows containing "EEE" in the size column.
I also need to remove any discontinued ("D" in the "Type" column)styles that are currently not in inventory. For example 05-5490 does not have any available in either width "D" or "EW" and should be removed; however, 28-112 does have stock and should remain in the spreadsheet. Anything with an "F" in the "Type" column should be left alone.
Summarize Data Based On Multiple Criteria
Jan 6, 2010I have a worksheet with several columns of data that I have to summarize elsewhere on the sheet. Here are the columns:
ID
Status
Joined
Name
Name can take on 4 possible values. For each value in Name, I want to count the number of cells in a column that fit certain criteria. Here are the counts I want to make:
Status OK: Status is a date.
Joined OK: Status is a date and Joined is a "1".
There are going to be about 500 records to count. I've already implemented these counts in a Windows scripting language that interfaces with Excel, but I know there must be a more direct and faster way to do it entirely in Excel, whether with VBA or writing functions into cells.
Conditional Format Based On Multiple Criteria
Feb 16, 2010I have a spreadsheet that requires me to manually hunt down records that match based on several different criteria. this is very time consuming and inefficient. i would like to find a way to highlight certain records that match based on several different fields. i have posted an example spreadsheet that also has a more detailed description of what i am trying to do; which is located in the second sheet.
View 9 Replies View Related