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
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.
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".
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 View RelatedI'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
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
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?
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")
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
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.
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).
I want to create a conditional formatting formula, which marks products currently in stock.
Lets say that I have the following setup:
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:
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: ...
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.
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.
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?
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.
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.
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 " , ".
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.
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 RelatedI 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.
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
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.
I have a simple database spread sheet and I need to count a column under certain conditions. In one column I have employee names that appear repeatedly, in another I have codes. I want to be able to count how many times the code appears next to the name.
For instance:
If b4:b65000 = Sam Douglas then I want to count how many times different codes appear in the adjacent cell.
Sam Douglas:BI
Sam Douglas:BI
Sam Douglas:SI
Sam Douglas:BI
BI = 3
SI = 1
I have following data (two columns Parent and Child), now I want to apply Countif on Child cell.
But in Countif I want to provide the criteria...let say only count those childs whoes parent is A.
How to do this in Excel.
Parent Child
A e
A f
B g
B h
B i
C j
C k
I'm reasonably new to Excel, and have a fairly basic question to check out:
I have been using the COUNTIF function to count up numbers of items in various categories in a column.
The formulae I have been using are like this:
=COUNTIF(F$3:F$201, "Red")
or where I've wanted to combine various comments
=SUM(COUNTIF(F$3:F$201,"Yellow")+COUNTIF(F$3:F$201,"Cream"))
I'm not sure what formulae to use to count up
1) the total number of entries in that column, so that I can make sure that I haven't missed some (without having to check manually!)
2) how to count up the values that do not match the other categories that I have specified in the COUNTIFs: this would be a value for finding how many 'other' entries there are in that column, without having to specify those values
I am trying to use COUNTIF with two critera. If this isn't possible is there any other way possible of doing this in a range of cells.
What I am trying to do is show the amount of students in a year group who spend x amount of hours on the internet and have a target grade (for example) of Lvl 4
I have been trying use a formula along the lines of =COUNTIF (Q5yr7, "0- 1Hour", Q12yr7, "4")
important columns are: "D", "G", "L".
this is about a project is a granite to be installed in 104 units, these units are divided in 4 types (column "L") K1, K2, K3, K4
I tried IF, SUMIF, COUNTIF, ETC but I think that I need is some combined function that I'm not able to figure out.
as you can see in the attached file, my units 101, 102, etc is not been installed yet (column H is empty), but the units 209, 210, 211, 214 yes was installed on 5/6 for example.
for example, if i use =COUNTIF(H4:H20,">0"), will return me all the H that is not empty (=3), how many kitchen was installed so far, but, I would like to know how many of these returned units (3), is my different types: K1, K2, K3 and K4....