I'm working on a staffing spreadsheet, where the hours of the day proceed horizontally with the staff shifts laid out below. I have placed a '0' in each spot where a staff member is on break/lunch, but don't know how to total up these spots.
View 2 RepliesI 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 have multiple sheets cataloging multiple vehicles' mileages in multiple areas (one sheet per month). I want to reorganize this data by Vehicle ID rather than month.
Here is an example spreadsheet to help explain
I have attempted this by writing this formula ...
I am trying to get grades based on raw scores in (I15:AX15) and (i11:ax11) are percentages. If a cell is blank in the (i15:ax15) I don't want it to be used in the calculations.
=(if(sum(i15:ax15),round(sumproduct($i$11:$ax$11,i15:ax15),2),""))
I HAVE AN IF FORMULA IN 7 CELLS BUT I CANT SEEM TO BE ABLE TO GET THE RESAULTS IN THESE CELLS TO SUM IN TO A DIFERENT CELL ITS BEEN A WHILE SINCE I USED FORMULAS AND HAVE FORGOTEN THEM
View 2 Replies View RelatedI'm trying to calculate total occurrences of a given month. I have included the spreadsheet for reference with totals.
I only want to calculate when the Policy column has a "N". If it has a "Y" I do not need to do anything
If the Policy has a "N" I want the formula to look at the V and P columns and give me a total in the Total Occurrence column. I want it to look at the entire month for a grand total.
If the V or P column has a 4 or less it should equal 0.5, if it has a value is between 4 and 10 it would equal 1. So in the Total Occurrence column should calculate the all of the 0.5 and 1's based on each set of columns and give a total. In the example that I have given in the spreadsheet the total in the Total Occurrence column would be 2 because Jan. 1 has a N and a 8 which equals 1 and Jan. 3 has a N and a 4 which equals 0.5 and Jan. 5 has a N and a 4 which equals 0.5 for a total of 2 occurrences.
I would like the TOTAL SOH column to reflect the totals for both warehouse and store count, but I cannot figure out the formula to get it. The way the sheets are loaded onto the system means thay cannot be the same so a simple sum across the rows cant be done. Is there a way of formula to do it ? If there were only a few rows I could do it manually , but the stock is in excess of a thousand lines.
View 7 Replies View RelatedI have a total $$ amount in one cell. Using this $$ amount I need to populate two other cells. One that is divisble by $10,000 and the remainder would populate the other cell. In the attached excel file I made notes to this effect.
If the $ amount in cell A3 is $83,000 I would need $80,000 to populate cell C3 and the remainder $3,000 to populate D3. If the $ amount in cell A3 was less than $10,000 such as $5,000 then only D3 would be populated.
i'm tracking hospital census with days of month in columns and patients 1 - XX in rows. this is basically what i'm working with.
A...........B........C...D...E...F...G...H...I...J...AG...AH
Patient...Payor...1...2...3...4...5...6...7...8...31...Total
1...........MCR.....x...x...x...x...x...x...d...............6
2...........COM.........x...d..................................1
3...........SP............s...s...s....s...d....................4
4...........MCR.............................x...d..............1
5...........MCR.............................x...x..d...........2
Total.................1...3...2..2...2...3....1..0.........14
The "total" cell formula is =COUNTIF(G7:AK7,"X")+COUNTIF(G7:AK7,"s")
S and X just identify where that patient is med/surg vs. skilled.
I'm looking for a solution that will reference column cells B2-BX and add the total from that row (AH) if the payor of that row is MCR, then another cell that would do the same for SP, COM and so on. so in this scenario it would be something like
COM...7.1% (SUM col AH where col B = "COM" ie. 1/14)
SP......28.6% (SUM col AH where col B = "SP" ie.4/14)
MCR...64.3% (SUM col AH where col B = "MCR" ie.1/14)
I'm trying to create a spreadsheet that uses one cell to enter a dollar amount and stores the amount entered in another cell. But I would like the input cell be zero after each entry, and the cell storing the amounts to keep a running total.
View 4 Replies View Related----------A----------B---------C
1-------------------100
2--------11/29----150-------250
3--------11/29----300-------300
4-------------------200
5-------------------300
6-------------------150
7--------11/30----100-------750
Have column A with date, column B with subtotal, column C with Total. The Total (C) is made up of one to five subtotals (B). Subtotals to be included in the Total are those that come after the previous Total up to the row with a date. Only a row with a date has a Total. Is there a formula that can do this?
I need a code that will sum the total of a range of cells which meets the following conditions.
If B2 is not empty then sum ("E2:R2") into cell T2.
if B3 " "" "" "" "" (""E3:R3") into cell T3
ETC, ETC, ETC
Do until "B" is empty
Is there a formula or macro that will identify which cells on a spreadsheet containing various dollar values will add up to a predetermined total? I work in Accounts Receivable and sometimes vendors send in payments for several invoices with no instructions as to the application. I want to be able list the available invoice amounts on a spreadsheet, and be able to tell which of the cells add up to the payment amount.
View 2 Replies View RelatedEX:
1200
1205
1210
1215
Cell with total should contain 15
These are mileage numbers. At the end I need to know the total miles driven as miles are added. I need to be able to see the total miles driven as mileage is added. At the end of the week, I need to see total miles driven.
What i need it for certain colored text to appear in specific total cells.
For example; i have 38 cells in a column within these cells id like it to have black writing which i want to total up and go into a total for that color into a specific cell and say green colored text into a different total cell and red text into another total cell.
If i had for instance 6 numbers all red 6 numbers all black and 6 green id like the numbers to all be added correctly and also into color specific cells?
i have a "budget" worksheet and am trying to total different colored cells and want to know if this can be done. attach a sample.
View 3 Replies View RelatedOn worksheet 1 I have Column A with a list of names, which we shall call John, Paul, George and Ringo, listed randomly within 100 cells (A1 - A100).
I have Cells B1 - J100 with 3 options in each cell; Blank, C and NYC.
I need a list on worksheet 2 for each individual person showing;
(1) total number of cells marked C
(2) total number of cells marked C & NYC combined.
Say I have a listing of cells
1,8,57,77,100, 104
I have a number I would like to find that is made up of a number of these cells. Lets say that number is 205, and as such the only possible values that could make that total are 1,100, and 104.
Is there a formula I can run that will allow me to find this number without manually figuring it out.
I have a Workbook with multiple sheets with Data in them. I would like to Count the number of duplicate values between the sheets.
For example.
Sheet one has a list of case references Person A is dealing with.
Sheet two has a another list of case references Person B is dealing with however the also have a list of case reference's they are monitoring as part of a project.
Sheet 3 has got the total number of case references each Person is dealing with, it also contain the total number of cases Person A&B together are dealing with. however i need to find the total number of cases Person B is monitoring that Person A also has so that the total number of cases is the true number of total cases with no duplicates involved.
(References begin with VB then have a string of numbers afterwards and I would like to know the total number of cases the Person B is monitoring as well so these need ot be calculated).
Using Excel 2002. Here's my problem.
Column A contains the month (as text)
Column C contains an employee name.
Column O contains a reason for absence.
Column K is the number of hours of absence.
The employee's name may appear several times in the worksheet. What I want to do is count the number of hours per type of absence.
E.g. If A=MAY and C=BOB and O=SICK then total hours from all instance of K = X.
This will be used on a seperate worksheet where the name C will be referenced from a validation list.
I have cells C22:C27 with a number that could change each week. How can I get a total of the number of cells that = 0,1,3,4,5,6,7,8,9,10,11,12?
View 3 Replies View RelatedI have 2 TextBoxes (TB1 & TB2) in which a store name and a date are entered. The sheet has many store names and many more dates(shop at the same store on different days). I am trying to get the total of columns L, M & N if TB1 = Column A AND TB2 = Column B. I know where to place the code (in the exit event of TB12(L), TB13(M) & TB14(N)). I have added sample data and updated my sample to make it easier to see what I am trying to do. Can anyone show me how to match up these TextBoxes and cell values or give me a hint as to how to proceed?
View 6 Replies View RelatedIs there a function, or how would I write a vba to figure out the following.
I have 86 items all with a different price, which come to a total of $348,359.33, is there a way to figure out which combination of the 86 entries will give me a total of $57,673.36
I have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell.
Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example.
I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.
This may not be the best way to do this, but I don't know Macros or Pivot Tables.
I am looking for a way with formulas to do the following:
Within a workbook the 1st sheet is the data entry.
In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column:
Example:
Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry.
G2:G2999 in the same sheet is where the data is.
Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?
I need to set up an easy to use spread sheet for my office. It needs to be able to calculate the running total spent of fuel, as well as include any discounts we get and then calculate our total savings.So basically, total spent and total saved.
View 3 Replies View RelatedI'm sure this can be done but I don't think I have the formula correct. What I am trying to do is add each day's total while displaying a positive number if the total exceeds 6000.
View 5 Replies View RelatedI am trying to resize a picture on a worksheet to fit a dynamic measurement(the size of the page is different depending on the contents in cells).
I am doing this all in VBA.
so what I have come up with is that I need to
1) determine the total column width of only the non-hidden(visible) cells.
2) resize my picture to those measurements, so I can get an exact fit, and it doesn't create a new page break, by going over.
I have found some code on forums, and am thinking of something like this as a solution(although I don't know how to complete it):
For summing column widths:
I have a sheet with a list of cost values and I need to be able to total each set. Each set begins with the word "COST" and ends with empty cells. Some sets have no values, just the word "COST"
There are currently about 500 cost sets, from row 1 thru row 2194.
Here is an example of what Id like to accomplish:
BEFORE:
Im trying to make a formula that lets you type in your current level, and then calculates how much XP you have remaning untill you reach the max level.
The way the forumla would need to work would be something like this:
(E10) Enter Current Level:
(F10) 37 (which is my current level)
F11 - would then add cells from C37 to C70 (calculating the total amount of XP required) and then display in (F11)
Being able to say if, then is far to cpmplicated for me in excel. I'm just trying to make this for a bit of fun and am sure one of you talented people can easily do this.