I am producing a spreadsheet that will track returned defects. Column B is where I input the department variable. In a new table on the same sheet I need a formula that will count department names and give me a total for the returns for each department.
View 6 RepliesSee attached.PMS 2.xlsx. I have a table with data on PMS sheet and would like to have something like the example on the Example sheet using data from the PMS sheet.
View 2 Replies View RelatedResouce Capacity Management .xlsx
How do I make my Pivot Table count/Sum the Threshold of each resource within department is within our 80% to 120% threshold?
From the example data of my previous post i would like to continue further to sum all the amounts based on department.My requirement is to sum the amounts in the next column down the department column. i had splitted the actual data into two categories based on departments "CNN" & "CNN-IN".
I had many departments like this in my data which i want to display them in different categories based on departments and the sum of the amounts in the next below cell of department. i got an idea on this that solution can be achieved if i apply Filters & Auto filter.But i m not perfect with the code and syntax.
I have a file that's almost 3000 rows of data I have attached a test file for the purpose of this request. I am trying to figure out the average days overdue per department whose due dates have not already passed.test (1).xlsx
View 2 Replies View Relatedto calculate how many days of each different leave type have been taken for each Cost Code within an Excel 2003 spreadsheet. What adds to the problem is that the data extracted from our system comes to +/- than 50 000 lines. So doing this manually is out of the question.
I have attached a spreadsheet containing an extract of the information (DATA Tab) to better understand. Under the Summary Tab I have the format in which the information must be presented. For each Cost Code/Department I need to calculate how many e.g. Annual Leave/Sick Leave/Other Leave etc. was processed for each month from January to end of October.
Has anyone dealt with something similar? I would really appreciate any assistance as this problem is way above my Excel skills (not that it’s actually above average in any case... ;-)
I have multiple departments and on each department we report the costs in four columns this month, budget, last month and last year. I have summed across the row to a hidden column and if the value is 0 I have a basic macro that "hides" the zero value rows. I understand I could further automate the process by having an "event" based in the spreadsheet which when the calculated values change the macro will run. This would mean when looking at the next department the zeros would automatically be hidden. I suspect I need a worksheet_calculate event but not been able to make it work.
View 2 Replies View RelatedI am trying to track metrics for a QC dept. Right now my sheet has 35 lines of data and a header row, for 36 lines total. This is my funtion that isn't quite right:
View 2 Replies View RelatedI have a workbook that has a list of different depts. I need to design a way to enter a login of sorts to the excel and allow the user of that department to edit their entries only and lock the rest of the entries for the rest of the departments and do not show them in the result list after the user logs in. Once all department manager fills in his/her entries, the administrator is notified via email.
View 1 Replies View RelatedI'm trying to get the number of items against a specific department which are contained in a sheet called "data", the sheet is contained in the same workbook.
There are named ranges for
Department (text)
Date_From & Date_To (in the format dd/mm/yyyy)
Band (Numeric 1 to 3)
Items (Numeric)
Using the following formula I get an #NUM error (using Excel2003)
=SUMPRODUCT(--(Department=A7),--(Date_From>=$B$2),--(Date_To
I send out a mail template everyweek to all the dept. employees using outlook. I attach a excel sheet to the mail so the employees can type in their hours and then sent it back. Then i go in manually each sheet and copy the info in THE timesheet excel sheet and sent it to the HR. Any easy way automate this process? What id like is to send a mai lcontaining the excel sheet, the emlpoyees fill it and the data tranfers to a excel sheet...
View 2 Replies View RelatedI work at a cardboard manufacturing plant in the Q.A Department. Occasionally paper claims need to be raised due to the paper being faulty (out of spec.). I have attached a spreadsheet which at present works out the paper cost according to which paper grade is typed into a particular columb. But, paper price varies according to the width (i.e. Deckle) of the paper as well. And this is the variable which I have not been able to implement thus far.
The relevant columbs are 'D', 'E' & 'I' for the purposes of this formula... It should be noted that below this I have pasted in the paper pricing list... and that in general each paper grade has 3 different prices depending on the Deckle (i.e. paper width), so there are 3 possible prices which can be displayed depending on the Deckle inputted into columb 'E'.
I'm trying to analyse data and summarise it by date (greater than 60 days, and between 30 days and 60 days), by department using the first two digits of department codes, and where there are multi-line order numbers.
View 4 Replies View RelatedI have a petty cash sheet set up:
A1 - Date
B1 - Receipt No.
C1 - DEPT CODE
D1 - NOMINAL CODE
E1 - Reason
F1 - Amount
G1 - Running total (I've put a little formula in there to keep the balance - clever eh!)
So - this is fine - except I have to add the total amount for each department/nominal code each month.
What I'm doing at the moment is copying and pasting, sorting the sheet by:
Department
Nominal Code
Date
Then doing a manual add up of each and adding it to my month end report.
I'm really not an expert at all - but I thought there must be an easier way for me to do it. After inputting the petty cash there MUST be a way that it can automatically calculate it?
Or is this going to be a difficult thing to do requiring expertise in pivot tables etc?
I have the following formula which returns the number of asset breaks for each head of dept in sheet3 based on their teams in Sheet1 and then does a countif in >30_DAYS_ASSET in sheet2. In sheet 1 is a mapping table with range names "Teams2", "Teams" & "Managers". I now need the formula to also do a count on the unique numbers in sheet2 Column B, if there are duplicate numbers then that will be 1 asset break, so for example for for Joe plummer we can see there are duplicate case numbers so the formula in total should return 2.
=SUMPRODUCT(--ISNUMBER(MATCH(Teams2,INDEX(Teams,MATCH(C11,Managers,0),0),0)),
COUNTIF('>30_DAYS_ASSET'!$B:$B,Teams2))
Sheet1
AB2ManagersTeams3JOE BLOGGSAMP14JOE PLUMMEREQADMIN567Teams28SECADMIN9AMP1
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Sheet3
AB4Head of DeptASSETS5JOE BLOGGS286JOE PLUMMER3
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Sheet2
AB6SourceCase No.7SECADMIN4498618SECADMIN4498619SECADMIN44991310AMP144986211AMP144918012AMP1550935
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
which formula achieves the following:
i have a simple table design with the dates running across the row, below each row i write the name of the person on shift. in the 3rd row i mark the time they are late for work (if any).
below that in a seperate table i have a list of my staff next to that i would like there to be a running total of their lateness. I need a formular that will look for their names and then total any lateness that i have inserted. I have include a basic spreedsheet of what i want to achieve, the column in RED is where i wan the totals.
Sorry for the question. Normally I find answers to my excel questions by going through the help tab or by searching on Google. However, I don't even know what question to ask on this one!?!
Basically I have created a spreadsheet with several columns, but I have one column that lists the shirt size (YS, YM, YL, AS, AM, AL, XL, 2X, 3X) of each person. Is there a formula that I can create that will tabulate the number of sizes (i.e. AS=2, AM=7, etc.)?
In previous years I made a column for each size, and simply placed a "1" in the correct column, and had excel just add the 1's from each column. However, that takes more time and space. I was hoping to streamline it this time around.
I have a table with 5 columns and approx. 85-90 rows.
Column A has the Branch name in it e.g. Beavers or Bedfont (11 Branches in total)
Column B has User Type - Adult, Child, Guest (Adult), Guest (Child), Catalogue
Column C has Session Type - Booking, Drop-In
Column D has Total Session Time (mins) - which gives a number in minutes of the total session time used
Column E is not needed
I currently get a calculator and add up e.g all of the adult Bookings for Beavers and enter them onto a Report Sheet, then all of the Adult Drop-Ins for Beavers etc. I want an Excel Spreadsheet that will give me a total number for each so I can do away with the calculator.
I am thinking of creating a new sheet with a number of cells that have a formula similar to this
=IF(AND(A2="Beavers",B2="Adult",C2="Booking"),E2,0)
But I want it to see Adult, Guest (Adult) and Catalogue as the same thing / and I want it to pick up Child and Guest (Child) as the same thing.
I Have 20:00-04:00 as text in a cell, what i need is to take the 04:00 and show only if its after 00:00 and before 07:00 and show it as time value. This is where I'm up to so far but i don't know how to ask it for > 00:00 and < 07:00.
View 4 Replies View RelatedI need to add some HH:MM values to give a total general value, its for a staff rota, i need total values for the hours worked per day/week.
eg. three 8 hour days in 08:00 format to give a total of 24
I have set up a spreadsheet to calculate time as followed:
A1 - Start Time
B1 - Finish Time
A2 - Hours Worked:
=IF(B1<A1,B1+1,B1)-A1
C1 - Start Time
B1 - Finish Time
C2 - Hours Worked:
=IF(D1<C1,D1+1,D1)-C1
and so on.........
I want to count a range of cells and if the value of those cells is 5 and over, i want it to give a 1 and if its under 5, i want it to give a 0. I've attached a file of what i'm after, row 13 is where the formula would go, i have just put in the values.
View 1 Replies View RelatedI am having trouble developing a formula which will return a field (specific cells back to me) based on some conditions.
Basically I want a formula which will return the date (G3-T3) to me on each client. On the conditions of there usage.
So I want it to extract the value of the first number to comes across (the value of the number does not matter).
E.g. Gissle (D5) "The formula should go along and provide me with the date (value) "1/7/2013" [Cell I3) in cell [E5).
It just needs to hit the first value it comes across horizontally and provide me with the date (field) which it is located in Column E (E5-E11).
save at work because I had to manually scope out the first value and insert and copy the date (took an hour).
Sheet1 *DEFGHIJKLMNOPQRST2***Usage*************3*
Date*1/5/20131/6/20131/7/20131/8/20131/9/20131/10/20131/11/2013
1/12/20131/13/20131/14/20131/15/20131/16/20131/17/20131/18/20134
Client*************8**5Giselle*********5******6Elaine******4*********7Lidi**4*************
8Eliza****************9Ji***************410Erika****1***********11Maria********5*******12*****************
i have a problem regarding calculated field.. i have data of sales of a distributor with all the accounts that he handles.. i want to get the total number of buying accounts for that distributor..
buying account is an account that bought a specific product for at least one time... even the account buy 2 or 3 times more for that specific product the account is considered as 1 buying account..
i want to use a pivot table so i can manipulate the data.. first i tried to add an additional field to my data and put the formula
"if(norbo_val>0,1,0)" where norbo_val is the total sales less the rejects. it means if ever an account have positive sales the account is considered as a buying account for that specific product. my problem is that when that account bought the same product for 2 or more times it sums all the times an account bought for that specific product..
so i decide to remove the field in my data and create a calculated field in the pivot table. i input the same formula to the calculated field and it gives me the correct result where if ever an account bought a specific product for at least one time regardless on how many times the account bought that same product, the account is considered as 1 buying account. the only problem i am encountering now is that the grand total is not summing up the total numbers of buying accounts if i list all the accounts. it seems the formula is affecting the grand total where it should add the total of buying accounts but instead using the formula of the calculated field so the result is only 1.
I need a formula to look in column B and find all "jun" (or anything else that I put in there) THEN look in column J and find only all "d" (d is one of 3 choices there) that correspond to the "jun" then give me the count of the "d".
This is an example. With the formula I can do a variety of things to get info that I'd like to have.
I am using Excel 2010 and am looking for a formula that can solve this matix in column below. The yellow is where I would like the answers.
A
B
C
D
E
1
DEP
SPS
SUB
2
123456
[Code] ........
This is what it would look like when the formula were applied
A
B
C
D
E
1
DEP
SPS
SUB
2
123456
[Code] .........
Requesting a SUMPRODUCT (or other) command that will give a count of rows where all of the following three things are true:
column A = P
(column B = Q or column B = R)
column C = S
I have been using the wrong formula to count total entries in columns and only just found this error. The MAX formula in cell B4 is: =MAX($B$12:$B$36). If the all the rows are full within range F12:F36, then the MAX formula is fine to count the total within range B12:B36 (25) so I thought. But sometimes there are omissions between F12:F36. If there are 2 blank cells anywhere within F12:F36 for example, then B4 needs to show 23 respectively. In the sample WkBk B4 needs to show 8
View 2 Replies View RelatedIn Cell A1 I have a name (John Doe). In cell A2 I want the formula which will be a number total. In Cells A4:A20 I have a list of names with John being in several of them. In Cells J4:J20 I have numbers. There is data in between the name and numbers that I don't need.
So, I want A2 to search A4:A20 and everytime it sees John Doe to add the numbers that are 9 cells over (J4:J20). This is basically to show how many mistakes John has made (total). I tried =Sumproduct((A4:A20=A1), (J4:J20,1,0)). I know I am missing something or not on the right track.
What function should I use to find the total length of the 3 extracted names (last + first & middle title?) I need one single expression for the entire column.
A
B
C
D
E
Full Name (Last, First & Middle plusTitle - the latter may be separated by either an ! or $ symbol).
Title (extracted)
First and the middle name if the latter exists. (extracted)
Last Name (extracted)
The total length of the 3 extracted names (last + first & middle + title) e.g. 25
6
Brouillet,Amy Anne!Miss
Miss
Amy Anne
Brouillet
[Code] .......