i have created a file for tracking my employee trandiness however, when i want to count the M, P, X ,L & AP keys which i already created it gives me OK for one month but when i tryied to calculate more than sheet "month" gives me error !! i used the below farmula :
=COUNTIFS(tblJanuary[@[1]:[31]],KeyPersonal)
how to calculated the above fx in every month. !!
i have 10 sheets in my workbook and i wanted to do countifs on every sheet for column a so i have created 2 more sheet one called start the other call end and moved them start before sheet1 and end after sheet 10
this is the formula i was trying to use =COUNTIFS(Start!:End!A:A,finished!A3)
I'm trying to collate data from two sheets (sheet1 & 3) , paste it on sheet5 and do some calc. But the macro keeps copying the same row after 2 iterations. Also the find function stops working after 1st iteration. Not sure what to do
Sub Macro17()
'
' Macro17 Macro
'
'
Sheets("Sheet1").Select
Range("B3:L3").Select
Do Until Selection.Row = 10000
Selection.Copy
[Code] .........
I have attached an example. on the "master" sheet in column L I need a vlookup which will look up the information in column I & J and return the appropriate amount for that from column C of the "sheet3" tab. If there is nothing it should just return 0.
Sample Macro jscalem.xlsx
Is it possible to have a forumla that gives a True or False (perferably if it's possible for a conditional formatting to change colors) for multiples of a number. I would input a number in the cell and it would change the cell color if it not a multiple of say for example 13.
View 5 Replies View RelatedI need to be able to round a given number (stock required) up to the next multiple of another number (pack size), as in the attached example.
Also, is there a way I can round to the closest multiple, ie either up or down, depending on which multiple is closer? I haven't decided which way I want to go yet!
How do I round percentages, both up and down, to the nearest 5%?
Ex. 82% to 80% as well as 84% to 85%
I need the output in B1 to be in multiples of the value in A1, rounded up.
example:
Value in A1 is 6
random formula in B1: =IF(AND(N2>$AA$1,S2>0),ROUND((30/$AA$1)*AA2-Z2,0),IF(AND(N2>$AA$1,S2=""),ROUND((30/$AA$1)*AA2-Z2,0),IF(AND(N2<$AD$1,S2=""),ROUND((30/$AD$1)*AD2-Z2,0),IF(AND(N2<$AC$1,S2=""),ROUND((30/$AC$1)*AC2-Z2,0),IF(AND(N2<$AB$1,S2=""),ROUND((30/$AB$1)*AB2-Z2,0),IF(AND(N2=M2,N2<$AA$1,S2>0),ROUND(30*(AA2/N2)-Z2,0),IF(AND(N2<>M2,N2<$AA$1,S2>0),ROUND(AA2*(30/$AA$1)-Z2,0))))))))
So if the resulting value of my B1 formula is 2.33, I'd like to have the output be 6. If the resulting value is 14.55, I'd like the output to be 18.
Is ther a way to search a column from M9 down and delete all numbers that are not multiples of 25. Also delete shiftup column N with it? i.e.
From:
1200.......399
1225.......700
1250.......325
1260.......320
1274.......500
1275.......251
1300.......102
To:
1200.......399
1225.......700
1250.......325
1275.......251
1300.......102
I'm trying to make a sales chart defining the top 6 most popular items I'm having a problem.
Attachment 316136
I need to create a vlookup to find a cell and returns all cells that are underneath, in the example:
jicRD.jpg
I need to find the color and return all cells that are down
I've got a head-scratcher here that no one in the office can figure out (at my level anyway). I'm trying to count multiples of the same value within the same cell. For instance, if one cell's value is:
2, 2, 4, 5
I need my '2' count column to return two '2's. I'm using a wildcard because the cells will contain the numbers 1-7, some multiple times, and usually in different orders. The wildcard formula I'm using only picks up one if multiples of the same value are entered in one cell. Here's the formula:
=COUNTIF($C$4:$AG$60, "*"&"2"&"*")+COUNTIF($C$4:$AG$60, "2")
This formula only returns a value of one in the '2' count column. It can't find the second '2' for some reason. I haven't been able to make COUNTA work either.
I know Excel quite well but this problem has me beaten and I have searched high and low to know avail.
I have an input column of departments and names which is ever-changing and currently I have hard-coded them into a spreadsheet to run a couple of drop down columns. However, I really want this to be automated and no longer hard-coded so that names are in the correct departments automatically.
to create the following formula which I'm now repeating in more elaborate forms. At the moment I am using the formula below but I need to get the bit in red in the second half of the formula to search on more than the one condition.
Currently it is searching a postcode abbreviation field and is picking up all those with 'BS' however I don't just want to search on BS. I would like to search on BS, BA, SN, TA, GL. I have tried putting different combination using commas and plus signs etc in but excel doens't seem to like the way I'm doing it.
=SUMPRODUCT(--(Sheet1!$T$3:$T$10000=A49),--(Sheet1!$S$3:$S$10000="BS"))
My question is presented, in details, in the attached file. Only Range 1 (B6:B15) should be generated by "Excel". (the numbers in Range 1 were typed by me as trials only...) (All other Ranges should not be touched). The values in Range 1 can be Multiples of 10th. only. (10, 20, 30, 40, etc....)
View 3 Replies View RelatedI am setting up a time and attendance system.
What I want to do is calculate the overtime that someone has worked but in multiples of 15 minutes.
Example, if someone worked 20 minutes over they would be paid for 15 minutes overtime.
If someone worked 31 minutes over they would be paid 30 minutes overtime.
The possible overtime someone could work in one day is 6 hours.
I want it to return the overtime in decimal numbers (e.g 0.25 for 15 minutes overtime).
I have attached a sample spreadsheet.
I would prefer this to be done in VBA if possible?
I would like to be able to add a standard set of new worksheets in varying multiples to my workbook from time to time.
How would I:
1. COPY the entire contents of a worksheet I've named 'Last' (content may be added to from time to time - so 'copy all' rather than a set range, so that:
2. Each worksheet created after 'Last' is named in turn 'Vendor1', 'Vendor2' etc
Sometimes I will need to create, say 10 further w/s 'Vendor1' to 'Vendor10' other times maybe 50 w/s. or more.
After these new w/s are created they will be manually renamed to the name of the actual vendor - so this naming convention of Vendor1 etc, will not cause problems with existing named worksheets.
However, the position in the sequence of 'Last' will obviously change as new w/s are added so the VBA would need to take this into account.
How do I create a code that record on row 1012, starting with column N, will count the number of rows between each "0" record in the range F7:F106. Perform the same function for number "0" in the range G7:G106 and recording in N1013.
Perform the same function for number "0" in the range H7:H106 and recording in N1014. Perform the same function for number "0" in the range F7:H106 and recording in N1015.
I am trying to create a spreadsheet containing a table of dimensions that are multiples of up to six loadable variables. I work for a stainless steel tank manufacturer. The body of tanks are formed from coil stock of varying widths. Typically these might be 1200mm, 1500mm, 2000mm and possibly 1800mm as well as the imperial equivalents. This could mean up to eight variables but in reality all are unlikely at any one given time.
We are told the volume that the tank is required to contain and a target preferred diameter. The theoretical height is therefore derived from this information. But now comes the tricky bit. It is desirable, to minimise cost of welding by minimising the number of strakes or wrappers. Can anyone give me a formula or whatever that can be used to do this and hopefully generate a table of close heights up to say 40 meters. Thus allowing an operator to select from say five presented options.
I have a table in a spreadsheet that the three principal widths of 1200mm 1500mm and 2000mm generate. It was created for me a while ago by a gentleman working at the Amsterdam Museum that I’m happy to send for perusal. The trouble is he never told me how he did it and no matter how hard I try, I cannot discover the method or formula. The first column gives the total combined height, the second the number of 1200mm wrappers, the third the number of 1500mm and the last the number of 2000mm. It is that simple
I need a basic formula that will take my start time from my end time and round it up to the next hour with a two hour minimum. I formated my cell to not have decimals and I tried to use round and roundup along with and if/then formula but no luck. If it makes any difference, all times are in 24hr format.
View 6 Replies View RelatedIs it possible to use design mode to create a button for multiples macros?
What i want to do is create like a list, and every selection run a different macro. I have a report that i want to use macro to make different filter selections, it would be about 20 different filters combination, so 20 buttons would be a mess... Is that possible or can i do it in another way?
And is possible to link the filter value to a different cell?
I need cells to be able to only allow multiples of fifty and a message to pop up of invalid entry for everything else.
View 9 Replies View RelatedI would like to add a piece of code into the user form that will check and verify if a part has already been added so as to avoid multiples in a user-driven/created database.
here is a repost of the current code i am using for the user form (I have posted it before in another thread .. Blane245 helped me out with a different question I had)
I am using the formula to count if c- is in the cells: =COUNTIF($B2:$U2,"C-")
However, I need to change the formula so rather than just count if C- is in the cells, I need it to count if A*, A+,A, A-, or B+ are in the cell instead.
I'm currently working with the formula
=COUNTIFS(Data[Quarter],"Q1",Data[Country],A3,Data[Sales],">2000")
However I need the formula to count only one for the n possible duplicates in the Data[Name] column.
Say I have a list of part numbers, and each part number has an X or a 0 next to it, depending on my own set parameter.
How do I then report that data on another tab so that it counts how many there are in a set area AND if its an X.
At the moment I have this:
I am looking for a replacement to the function COUNTIFS that will work with Excel 97-03. I have attached a sample of the document which will hopefully demonstrate what I am trying to achieve. On the sheet labelled 'Data' there is four headings (MerRefs, JCPRefs, Self Refs and Other) for the results beneath these headings I have used the Countifs function. I have attempted to use the SUMIF as an array formula but couldn't get this to work.
Also in the hope of killing two birds with one stone, I created this in Excel 2007 and in the past when I have saved documents as 97-03 documents it used to warn me of any incompatible functions included within the doc and now it doesent. Where can I switch that setting on?
I'm trying to do a COUNTIFS for a set of data. What I want it to do is pull everything that is assigned to a certain person that has no date in one column, and information in another. For example:
Name
Date
Comment
Michelle
4/30/14
Closed
Michelle
Issue
Michelle
4/30/14
Closed
Michelle
4/30/14
Closed
Michelle
Issue
So based on this dataset, I would get the number 2 for Michelle (row 2 and 5 have no date in column B and a comment in column C). I've tried a few variations and can't seem to make it work right.
I am using the COUNTIFS function. I can us it with simple criterias but unsure to go about this criteria.
I would only like to count the cells if the range in question is equal or greater than S3 but is equal or less than T3.
I don't know how to use the => signs.
I have a list of information regarding jobs that need to be carried out by a number of companies by a number of dates. What I want to count is the number of jobs that company A need to caary out by a specific date and then the number that company B need to carry out by the same date. The formula basically needs to say countif column a equals company A and column C equals 11/11/09. I have tried numerous combinations of IF, Countif, SUMif, And etc but can't get the formula to work,
View 9 Replies View Related