I've included a spreadsheet that show what Im trying end up with. Im looking to have a sheet that has a item indentification number along with a title and value. I would like to come up with a macro or formula that organizes the items by that identification number and totals them.
View 4 RepliesI'm currently working on a summary page for the defects in the building we are working on.
I've done up a summary page which reference's cells in other sheets which contain a TEXT(COUNT formula. When I goto SUM these referenced cells in my summary page I keep getting it returning '0'.
Example of what I'm doing:-
Defects Column in Summary Page
411 --> referenced from =Basements!L1 --> L1 contains =TEXT(COUNT($C$8:$C$100),"0") formula
56 --> referenced from =Level1!L1 --> L1 contains =TEXT(COUNT($C$8:$C$100),"0") formula
33 --> referenced from =Level2!L1 --> L1 contains =TEXT(COUNT($C$8:$C$100),"0") formula
When I go to do a simple SUM of 411,56,33 via =SUM(D3:D5) it returns a zero?
My company works 24/7 and with that some people on nights starting prior to midnight and then finishing after. Of course the "regular" formula works well in calculating between 00:00 onwards but I am getting an error when for instance the person starts at 20:00 and finishes at 04:00. I have tried numerous ways but still the error persists.
View 5 Replies View RelatedThe problem I am having is that I would like to total using the VLOOKUP (or any other command, which i do not know off). Here is the scenario
I have two sheets.
In sheet 1 have:
Column A with the weeks (date).
Column B with numbers.
Column C with numbers.
In sheet 2 I have:
Cell A1 with the date (which I type in)
Cell A2 displays the number (according to the date that I have typed in Cell A1)
In Cell A5 I would like to display the total. If in Cell A1. I type 13/04/09 it would total 30 (06/04/09 & 13/04/09). I have attached a spreadsheet for reference.
On my spreadsheet I'm after a formula for cells E2 and F2 that I can drag down and it automatically populates the cells relevent to the day with the total hours for the Sessions (E2) and the total hours per day (F2). Hope this makes sense, but I'm sure you'll gather want I mean from the spreadsheet.
I am currently building a data sheet using VLOOKUP. When there is no data to lookup it returns #N/A, which is fine. The problem is when I go to total these columns because of the NA it returns NA in the total box. Having tried the if isna formula I am now returning #VALUE!. I may be doing the IF ISNA formula wrong.
View 4 Replies View RelatedI have 3 columns of data and am using an autofilter to sort it. At the bottom I have a formula =subtotal(9,cells requested) which magically shows me the sum of the data showing only (excludes all the figures which are hidden - it's fabulous). However I was wondering if it is possible to have the same formula but to count the number of things shown instead of sum and another to average.
View 3 Replies View RelatedI have two columns for a Home and an Away win-loss records for a team over a few seasons. The win-loss stats in each column are in the form 24-13 (2-two digit numbers with a hyphen in between). How can I get a total at the bottom of the column for all the win-losses?
View 9 Replies View RelatedI want to total column A & total column C but only if there is an amount in both cells on the same row
Can I do this with an array?
Do the columns have to be adjacent? I could make them if necessary.
Data
A C
11 12
21 0
0 17
15 15
11 9
in this example we exclude rows 2 & 3 from the total because one cell contains zero or is blank.
Total Column A = 37 (11+15+11)
Total Column C = 36 (12+15+9)
I work in HR MI and am compiling an equality spreadsheet. Out of everyone who is on the list I want to see how many men and women we have which I have figured out using = SUMIF(GENDER,"MALE",NUMBER) but from that I want to say theres 50 male in total and out of that 30 are White and 20 are Indian. Is there anything I can add to this formula so it looks at more than two defined sets of information??
View 2 Replies View RelatedI have a 1500 row spreadsheet that will change every month. It's set up in three columns Name, Amount, Total. The Total column is supposed to hold the total of all the rows with the same name field. I.e.
4445-8889 12
4445-8889 8 20
5598-7785 10
5598-7785 10
5598-7785 20 40
The problem is that the number of identical named rows is different for each name and changes each month. I want to build a macro that will out the correct total in the last column of the last entry with the same name.
i have been working on 2 sheets receiving and selling item and QTY, or in other words i have a Stock IN sheet and a Stock Out sheet
is there some sort of a formula which links 2 cells, columns, together in a way that, if one cell's number increased the other would decrease automatically ?
say iv set 50 in cell a1, i want to set 10 elsewhere and the 50 in cell a1 will automatically decrease from to 40
here's my excel worksheets i uploaded it,
Text to Number or General.xlsx
The included, small database is formatted as text. It is a text feed from an outside source. I simply want to format the cells into either numbers or general format but not text... seems simple, and it should be, but the only way I can get this done is to go to each cell and access the formula bar and re-enter the number by pressing Enter.
Create some sort of formula combination or macro that will: Recognise a cell with a value of 1, 2 or 3 in. If 3 is in the cell, the cell to its left will be counted and added to a total. If the cell that has 3 in changes the value is removed from the total. Ive tried lots of methods but i cant figure this one out!
View 6 Replies View RelatedI would like to get number of cells since the cell value exceeded a given number in a given range of numbers.
example
cell address cell value
a1 1
a2 1.9
a3 -1.2
a4 4
a5 3
I would like to know number of cells since value was less than 1 : the answer is 3 (a5,a4,a3)
Number of cells since number has exceeded 3: the answer is 2 cells back ie a5,a4
how do you place 100/0 so the /0 stays static and the 100 can be used to operate? (ie: 100/00, 101/00, etc)
View 5 Replies View RelatedI have a column of numbers and want to make sure everything has been entered correctly from our scanning software. Basically, I want to automatically highlight any cell that has any letter in it (e.g. z12o2 instead of 21202 or R705 instead of 5705), ignoring any cells that contain only numbers. I haven't had any luck using conditions based on formulas like =ISTEXT.
View 2 Replies View RelatedI have this formula which is counting the number of cells in a column that fall within each calender month.
However, if there is a formula at the bottom of column B and C that yield a "", the formula breaks.
In my workbook, B/C:133 have a formula =""
I will need the formula in column E to work if there is a formula that yields a "" in column B and C.
How do I count the number of cells that have a value greater than 0 in a range of cells?
View 2 Replies View RelatedI'm trying to develop a new daily timesheet for my production workers, where non-production items are recorded in 15 minute intervals. The user would put in "Clock in" by the corresponding time, and the same for "Clock out" at the end of the day. Any non-production items will be type in next to their appropriate time. Since clock in and clock out times will vary, I need to set up a formula that searches the array of cells for the day, finds the "Clock in" and "Clock out" values, and counts any blank cells in between them. Basically the blank cells will equal production time, and the result of the Count function will be multiplied by 0.25 to get the hours.
I am having a very difficult time finding a way to set the "Clock in" and "Clock out" cells as the range for the Count function, because it won't always be the same cells. What would be the best way to automatically have excel find the cells containing these values and set them as the range criteria for a Count function?
The formula at the bottom was one of my initial attempts, but it didn't work. I took out the '=' for the screenshot, so that wasn't the problem.
I need to know wich formula or VB method, I have to use to count the number of cases in a range of colomns and rows C4:G107, have the same number. For eaxample how many times do I have number 1 and so on...
View 9 Replies View RelatedI have a similar question regarding coloring of empty cells between two cells with specific numbers as posted in below thread : [URL] .....
This is exactly what I want. But here I have some problem with formula.
=COUNTIF(A1:Z1,1)=1
Above formula could pick cell value 1 and color the empty cells in a row between the cells having value=1. But if I want to use the same formatting for other numbers like 2 or 3 it doesn't work.
For example in row(A1:Z1) I have A1=1, D1=1, and F1=2, K1=2, and O1=3, U1=3
I required multiple conditional formatting that could color the cells based on values first from A to D then from F to K and finally from O to U.
I'm using above formatting it works with cells having values 1 but its not working for 2, 3 or any other cells values.
I have already tried replacing 1 in above formula with 2 and 3 but it doesn't work.
In my financial modelling I often have a calculated number of months of inventory. This number varies. I need to use this number to calculate the actual inventory value. For eg. it may be 3.2 months of inventory; in this case I need to sum 3 cells (current month, current month -1, current month -2) plus 0.2 of the fourth cell (current month -3)
But I may need to change the number of months of inventory to, for example, 4.2. In this case I would need the sum of four cells plus 0.2 of the fifth cell etc etc.
I am trying to hyperlink all Worksheet2 A cells to worksheet1 A cells according to their row number. I have tried to create a Hyperlink in A1 and drag it down but what happens is that hyperlink links to same cell adn doesnt change cell number whilst dragging.
View 4 Replies View RelatedGetting a formula or macro that count the number of blank cells between 2 cells with data (numbers) in 1 column. E.g.
1
Blank
Blank
2
Blank
Blank
Blank
3
...
In this case the blanks between 1 and 2, between 2 and 3 to be displayed in an adjacent column.
creating this code or fomatting in my excel file. I want to fill color in cells between the two cells that has value of say "1" or anything in it. There will be only 2 numbers at any time in any row that has the value in it. All other cells will have value of "0" or can be changed to empty cells. So, the formula should check from say range "A3 to Z3" in row and for example it gets the first numbered cell in C3 then it start filling color from C3 cell until it reaches the other cell in that range that has the number in it. Say the row "A3 to Z3" has 1 number in C3 and other in cell H3 then it should fill color between this two cells from C3,D3,E3,F3,G3 and H3.
View 7 Replies View RelatedI have two columns of cells that need to be reformatted: B and C. In the B column excel is reading the cells as time (0:41:55.0), I need it to display those cells as a number like this: 00 41 55.0. In column C the cells look like this: 41 : 09 : 47.3. I need the C column cells to look just like the formatted B column cells (41 09 47.3) Also, even though they have the spaces, the numbers should be read by excel as one whole number (i.e. 41 09 47.3 would be used in a calculation as 410947.3)
View 3 Replies View RelatedI have a column with Cells that will sat True or False, the amount of rows will be different every time, I need to work out what the total percentage of True cells compared to the total number of cells. How would this be achieved.
View 9 Replies View RelatedI am trying to add a number of cells according to a number in an adjacent cell.
If cell A1 has a number in it and column b has a list of numbers, how do i only add the number of cells in the list to match the nmber in cell A1.
example, if a1 contains a 3 only add the first 3 cells in the list, if has a 5 only add the first 5 numbers in the list.
My data is as follows:
Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Number of Starts 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10
Rolling Number of Cases 10 20 30 40 50 60 70 80 90 100 110 120 120 120 120 120 120
In my dataset above the cases finish after 12 months (hence why there is a maximum number of cases).
What I am seeking is a method to count along a certain number of cells, then sum the contents of those. For example, I would like to count the contents of up to 12 cells, then sum those 12 cells.
Where it gets difficult is as follows:
- In month 6, I am just counting the contents of 6 cells (months 1-6)
- In month 12, I am counting the contents of 12 cells (months 1 -12)
- In month 18, I am counting the contents of 12 cells (month 7 - 18)