Several Criterias Equal Different #

Mar 8, 2008

I am having trouble creating a formula that will do what I need it to do. In C4 the I already have a formula that states if B4 is greater than 94.99% then it will equal 5. What I need it to do is if it is equal to or greater than 95% then =5. Also if it is equal to or less than 94.99% - 92% =3 and if less than 91.9% =1. Here is the worksheet I am using....

View 15 Replies


ADVERTISEMENT

More Than Two Criterias In Autofilter

Nov 3, 2008

i am trying to filter data based on more than one criteria (8 to be precise). I have some data in one worksheet and i need to transfer it to other worksheets depending on certain criteria. for example if cell A1 has A or B then it should go to "temp1" spreadhseet, if A1 has C,D, E, F, G or H then it should go to "temp2" worksheet etc.

Is there a smart way of doing this rather than writing a number of with statements using 2 criterias each and hence copying data in more than one attempt (and thus slowing down the macro)?

I did think of using creating a dummy column, then using If statements to write True or false in that column, using true & false to filter and copy the data and then finally deleting the column. but as i understand i can not have more than 7 nested if statements but i have 8 criterias.

View 8 Replies View Related

Criterias For IF Statement

Jan 12, 2010

I've got a line of code where the excel sheet creates a report by picking up certain values from the cell. One of the code is as follows

If (strGroup Like "Computers*") Then
GiveTo = "Youngsters"

where Youngsters is a emailgroup created for the report. I want to add certain extra things to this strTask Like "Help*" Then GiveTo = "Volunteers".

View 5 Replies View Related

Countif Between Two Criterias

Apr 25, 2006

i need to know, how much people belongs to the number in Colum A - if in
colum C is written "ISM".

A B C
1 1 Meier ISM
2 3 Huber ISM
3 2 Schmitz UPA
4 2 Mayer ISM
5 1 Mueller UPA
6 1 Hase ISM

View 12 Replies View Related

Filters And Criterias

Feb 20, 2009

I'm trying to modify this code in order to do the following.

I want to choose first column and then the criteria for filter, then i want to choose a second column and criteria to filter the remain values from the first filter.

Here is my
Sub Filter()
Dim Myrange As Range
Dim CriteriaVal As Variant
Dim CriteriaVal2 As Variant

Dim KillColumn As Integer
Dim KillColumn2 As Integer
Dim ActiveColumn As String
Dim AC
Dim LastRow As Long
Dim rng As Range

View 9 Replies View Related

Match 2 Criterias In An Array?

Jan 7, 2009

If I have to match MAX from Row 1 and MIN from Row 2 and get the EXACT MATCH as well as more than 1 Match?

I have 3 Rows and 4 Column Matrix

from A1 till D1 contains values as 10,20,50,40
from A2 till D2 contains values as 30,20,10,40
from A3 till D3 contains values as TOM,****,HARRY,JONES

THen my answer would be HARRY, however if there are more such combinations present then I would require a list of all of them.

Ex2 For Multiple Lookup
from A1 till D1 contains values as 50,20,50,10
from A2 till D2 contains values as 10,20,10,40
from A3 till D3 contains values as TOM,****,HARRY,JONES

View 4 Replies View Related

If Two Criterias Match, Value Should Show As 1

Aug 27, 2009

If column A is greater than B and column C is blank then Column D should show value as 1.

ABCD
15121


Similarly if column B is greater than A and column C is blank then Column D should show value as 2.


ABCD
12152

Can I put both the formulas in one column so that I get the value as 1 or 2?

View 4 Replies View Related

Count Between Two Dates With Two Criterias

Jan 26, 2013

How do I count between two dates with two criterias?

Col B = dates
Col D = Results (in this case "Car accidents") dropdown cell = A100
Col G = RRV or aircraft (Type of transport) dropdown cell = B100

The data is found on Sheet("Orders")

View 1 Replies View Related

Find Data With Two Criterias

Nov 18, 2009

I have for example database in three columns - A, B, C:
__A_______B_______C
Order1___Item___99-99-99
Order1__________10-15-78
Order1___Item___88-88-88
Order2___Item___10-18-25
Order2___Item___10-15-25
Order3___Item___10-15-25

I have two criterias: Order1 and Item
How to find in DB sheet these data and copy in to other sheet?

I hope get this result:
99-99-99
88-88-88

View 9 Replies View Related

Multiple Criterias In Autofilter

Mar 4, 2010

I need to use a not equal to operator to apply filter on a column. Something like:

Fc = Array("BA-DIRECT", "DAFC", "WSS", "LISS-East", "LISS-West", "GSO DRO")
ActiveSheet.Range("D:D").AutoFilter Field:=4, Criteria1:="" & Fc, Operator:=xlAnd

I need to filter all values which are not equal to "BA-DIRECT", "DAFC", "WSS", "LISS-East", "LISS-West", "GSO DRO" in column 'D'

The above code is not working.

View 9 Replies View Related

SUMIF With Two Criterias - One Of Which Is Date Range

Jan 8, 2014

I am working on a report for work with the following formula:

=SUMIF(A5:G8,AND(between 1-1-13 and 1-31-13,"soft cost"),G5:G8)

my formula is "IF A5:G8 IS BETWEEN JANUARY 1st 2013 AND JANUARY 31st 2013 AND ALSO IF IT IS A SOFT COST THEN GIVE ME THE SUM OF THAT ROW"

Pretty much column A contains different dates and column C indicates whether a cost is a "hard cost" or a "soft cost". If the date is within the month of January AND if it is a soft cost, I need the dollar amount in column G summed-up (must meet both criteria - January and soft cost).

View 4 Replies View Related

Finding Cells Based On Two Criterias ....

Feb 6, 2009

I want to create a conditional formatting formula, which marks products currently in stock.
Lets say that I have the following setup:

View 11 Replies View Related

Multiple Criterias In SUMIF Formula

Jul 28, 2009

is there any way for a sumif formula to have multiple criterias? for my case, after the formula checks for a condition, it has to check for another condition before summing up the figures.

this is my current formula:

View 8 Replies View Related

How To Use Lookup Function With Multiple Criterias

Aug 26, 2009

I've forgotten all the formulas that I learned during college. And that was for Excel 2003; now I'm using Excel 2007. So, hopefully you experts can help me out.

So here's the problem. I need to set up an invoice in Excel but I don't know what formula to use. I'll first describe my invoice and then I'll tell you the problem.

It's a basic invoice where you have customer's info and product's details and prices. So, when I choose a customer's name from a list in a cell, the address and phone of that customer will automatically appear. I don't have a problem at all with this part. And then there's the product details section where you enter a product type, size, color, price per unit, and total price. The price per unit also depends on the customer entered. Each customer has different pricing and I used a discount system for each customer. We basically have a universal price list for our products, but we have different discount percentage for each customer.

My invoice will look something like this: ...

View 10 Replies View Related

Sum An Offset Colums From A 2 Criterias Hlookup

Aug 27, 2009

Say i have database (A2:X4), the first 2 row is ID (text), and the last row is the value.

If given, 2 ID that can be looked up to the database, and say the data given is equal to the ID in cell I2&I3. The question asked is how to get the sum value of (cell E4:G4)?

For more clear explanation i have attached the example.

View 3 Replies View Related

Index And Match With 2 Criterias/conditions

Jan 12, 2007

Need to kown if Index and Match can be used if you have to criterias. Below is a glimse of my sheet. The data does come from 2 sheets, I don't know if that is another reason, doubt it, but needed to tell you just incase. I have sales information and I just want a quick view of the sales by associate by date. My 2 condition are the associates id# and the date.

=INDEX(($J$2:$Q$4),MATCH(AND($A2,C$1),AND(N2:N4,M2:M4),0),9)

my range is from J2:q4, my criteria is column a2 Sales asso Id and C1 the date, and match is N2:N4 & M2:M4, witch is the 9th column.

View 9 Replies View Related

Finding Minimum Value Using Multiple Criterias

Feb 28, 2008

I have managed to make a work queue and lots of other stuff for the model, but I can't get it to take orders in the way I want it. Each order has a order number (from 1 to 100) and the orders come in almost randomly e.g. 3, 5, 11, 2, 7, etc. What I want to do is to take the smallest available order that has not been processed in.

The available orders column and processed orders look something like this:

A B C D
Time, Available, Processed, Start processing
5 2 0 2
10 0 0 0
15 0 0 0
20 0 0 0
25 5 0 0
30 0 0 0
35 0 0 0
40 0 0 0
45 4 2 4
50 0 4 0
55 7 0 5
60 6 5 6

Zero means no new orders or no processed orders. Now the Start processing column should select the smallest not processed order if previous order has been processed.

A have, for now at least, all other problems solved, but can't figure out how to get start processing column check for the smallest not processed order line. I have tried combination of Min and Max functions with If, but it soon requires too many Ifs to make any sense out of it. I also tried the Dmin function, but it wasn't up to the task becouse the model requires ~1000 lines and as Dmin only takes criterias vertically I ran out of columns . So how could find minimum from row one until current row excluding values processed so far and only checking orders available so far?

View 9 Replies View Related

Delete Unwanted Characters Within Certain Criterias

Jun 20, 2007

I have a spreadsheet that i manually edit each and everyday e.g.

A B C
EABGL/UD NDT254892
MRMR/RUS/ELQNS259762
LSL/UW/B LQNS267259
WWEX/UQ bbr263666
LWL/KL/B 270407
MYTCJ/UB NDT271774
LNL/SB/UB HLC - 271955
SMMQD/WT HLC - 269516
EACO/TN/UGBBR257827
NILVA/UC EUi273645

For everything that doesnt equal EM, LN, LW and TH in column A, everything should be deleted in column B.

For the remaining EM, LN, LW and TH, i would then like it to delete / (forward slash and all characters after this) so that this would make my life easier.

View 4 Replies View Related

Values Which Are Equal Not Treated Equal

Jul 5, 2007

I'm having trouble with a small vba macro. At the end of the macro I test to see if two variables are equal and then print out true or false. However, for some reason even though the variables are equal vba is not treating them that way. I have put the values that represent the variables on a spreadsheet and used the if(x1=x2) formula and it says it is true, also, when I debug the macro and watch the values when it comes to test the logical expression the numbers are the same. I don't understand why vba does not say that the two variables are equal. I have attached a screenshot of the breakpoint where I double check the values are equal.

View 2 Replies View Related

Sumif Formula Needs To Split 2 Criterias Of Addition

Aug 6, 2009

you guys very kindly helped me with a spreadsheet a couple of months ago, but i now need to adapt it for another dept. I have completed as much as I can.

I need column C and E in the 'totals tab' to only calculate contract and upgrade sales respectively (found in 'service orders' tab). I also need Scott's and ash's individual sales to be calculated in corrisponding tabs. Most of the formulas are in place so just need them tweaked slightley.

View 4 Replies View Related

Counting Cells If Special Criterias Are True

Apr 25, 2006

I have a worksheet containing appr. 9000 part numbers that have four different columns as: annual volume, dispatch quantity and turn over speed.

I want to count all rows where these conditions are true:

(annual volume / dispatch quantity)>turn over speed

I thought I could do something like this but it turns out wrong:

=COUNTIF((annual volume/dispatch quantity); >turn over speed)

This does not work.
Is there a way that I can do this easily?

note: the " ;" is correct. In my excel I use ; instead of " , ".

View 12 Replies View Related

Using Vlookup To Get A Sales Volume But Need To Meet 2 Criterias

Nov 6, 2008

I need to get the sales volume from another worksheet but need to meet 2 criterias in both col A and B. How can I do it? Can I use Vlookup for this?

I'm attaching a file here. The cell highlighter in yellow is where I need the sales volume. First I have to find the region, then the brand of battery to get the sales volume.

View 14 Replies View Related

Summing A Pivot Table Based On For Criterias

Jan 16, 2009

I do not know if this is possible, I have a pivot table, however I would like to be able to sum a particular range based on start and end date. then by make and model as the second set of criteria, The sum would be displayed into a form on a different worksheet. attached is a file so I would like to know the total

View 3 Replies View Related

Counting Cells If They Meet Multiple Criterias

Aug 16, 2008

I have a list of brands in one worksheet that are abbreviated (Brand 1 = "AB-"). In another Worksheet I have a list of products that start with various brand abbreviations (ex: AB-12345, BP-12345), and in another column on the worksheet I have codes that represent certain characteristics of that style ("1"=flat shot), "2"=shot on model, etc). Now what I want to do is count how many instances I have of products that begin with "AB-" and have a "1" in the other column so I can get a count of how many flat shots i have to do for that brand.

So far I am using the below code to get a TOTAL count of products that start with "AB-", but i cant figure out how to write it so that it checks additional criteria in another column.

=SUMPRODUCT(--(ISNUMBER(SEARCH(A3,(OFFSET('photo list'!G2:G5001,,,,))))))

A3 contains the text "AB-" and 'photo list'!G2:G5001 contains the list of products that may or may not contain the text string "AB-". Column S (not shown in this code) contains the codes for how to shoot. Hope someone can make sense of this and give me a hand.

View 9 Replies View Related

Function To Accept 2 Criterias And Return Result

Sep 8, 2008

Name

Day

Total
AlanMon400AlanThur600TomMon200TomWed300JamesMon1000Output4002001000

What function/formula can I use if I want that function to accept 2 criterias and returns me a result. Based on the above example, if the criterias are Alan & MOn, it will return 400 and if the criterias are Tom and Mon, it will return 200 and if the criterias are James and MOn, it will return 1000. Bascially, I want a function that can accepts 2 criterias. I do not want to sum up the total of the rows that are associated with one of the criteria i.e Alan. For Alan, I want 400 instead of 1000

View 9 Replies View Related

Creating A Formula To Calculate A Result That Depends On 3/4 Criterias Been Met

Apr 28, 2008

Firstly its probably best you download the file from below and open it up.

http://www.mediafire.com/?un3smhmdyzt

I need a formula to put in cells in column S on sheet March.

It needs to match up the correct price from sheet 'Container Price List'.

The price that should be displayed depends firstly on the account number, container and waste stream all matching to decipher which line the price is on. Then the price displayed should be the price in the column called 'exchange charge' however if this is 0 it should be the price in the column called 'rental charge'.

So for example, if you look at row 27 in sheet 'March' the price displayed would be £80.39. The formula will look at the account number 'c028' on sheet 'March' and match it to the any of 'c028' on sheet 'Container Price List'. It will then look at the container in sheet 'March' which is RL14 and match it with the ones that are RL14 in sheet 'Container Price List'. Then it will look at the Waste Stream in sheet 'March' which is GEN.C and match it with the GEN.C in sheet 'Container Price List'. The result given should be the exchange charge in sheet 'Container Price List' which in this case is £80.39.

If for example the exchange charge displayed £0.00 then the result given should be the rental charge in sheet 'Container Price List' instead.

View 12 Replies View Related

Count If A Cell Is Equal To Cell In Different Spreadsheet And Is Less Or Equal Zero

Apr 17, 2014

How to correct my formula because it does not work?

Here the formula: =COUNTIFS(Data!C2:C24005,A17,Data!M2:M11149,"<=0")

I need to insert the formula in column C (Findings tab) which counts the rows in Column M (tab named "Data") that equal A17 (Column A in "Findings" tab) and which are less or equal zero. In addition if I drag the formula down I want to only the values be entered in highlighted in blue cells in column C (SKUs With Zero Sales in "Findings" tab) and empty cells in not highlighted cells. I attached images of these two tabs: Data and Findings.

View 5 Replies View Related

Countif- Where Column A Is Equal To A Specific Number And Column B Is Equal To Another Number

Jun 29, 2008

I'm trying to count if there are situations where column a is equal to a specific number and column b is equal to another number.

both columns are numbers. Does anybody have a good idea how to do this, maybe countif, but i am not sure how to do more than one item with that.

View 9 Replies View Related

Equal To Or Less Than

Mar 18, 2008

I have a quite complex list of variables to put into a formula to come up with the correct number and I need some help to figure this one out. I don't know if it would be easier to set up a chart and then index the chart to do a lookup or how I need to go about doing this to achieve the most efficent solution.

I need the following to go into one formula:

1. =< 24 needs to show the number 24
2. =< 30 but > 24 needs to show 30
3. =< 32 but > 30 needs to show 32
4. =< 36 but > 32 needs to show 36
5. =< 40 but > 36 needs to show 40
6. =< 48 but > 40 needs to show 48
7. =< 50 but > 48 needs to show 50
8. =< 60 but > 50 needs to show 60
9. => 60 needs to show 60

I am trying to set up a spread sheet to figure square footage of plastic laminate countertops, but the plastic laminate only comes in several stock sizes : 3x8, 4x8, 5x8
3x10, 4x10, 5x10
3x12, 4x12, 5x12

So if I have a countertop that is 25 sq ft., I cant use a 3x8 (24 sq. ft), so I need to go up to the next size which would be a 4 x 8.

I might need to use a ROUNDUP function with this formula too? I already have cells set up to figure the depth, backsplash height, and front edge height and then give me the squage footage of that.

View 14 Replies View Related

If Value Does Not Equal Then?

Jan 4, 2014

Example If Selection.Value *Does not equal* ActiveCell.Offset (0,1) Then .....

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved