Counting Aged Tickets Based On 2 Critera
Mar 5, 2007
There are 2 sheets, the 'Open Cases by Resolver' sheet is where I am trying to gather all the relevant information from the 'Open Cases - STATS' sheet. I need to be able to group the tickets in ages, ie tickets open Inc<30, Inc 31 to 60, Inc 61 to 90, Inc >90. The spreadsheet will help explain it in better detail. I thought a ' CountIf(And' formula might work (am trying to keep it formula based) but am having trouble recognising the team name then counting the ageing tickets.
View 3 Replies
ADVERTISEMENT
Oct 7, 2013
I have created a spreadsheet as follows:
Date revenue total tickets sold total tickets sold per day
10-1 166,453 15374
10-2 166,915 15414
How do I formulate so that the numbers automatically update per day
View 2 Replies
View Related
Mar 6, 2009
I am trying to write an array formula that will work out how many minutes of tickets are open within a particular month. I have a formula shown on the first table which calculates how many minutes a ticket is open for within a particular month, but this only works for an individual ticket, Basically I neet to populate the 2nd table with an array formula that will calculate the number of minutes within the month that numerous tickets are open, effectively a SUMPRODUCT but I can't get it to work.
The right hand side of Table one doesn't exist in my main file, I am trying to populate the 2nd table using only the first four columns within Table 1.
******** ******************** ************************************************************************>Microsoft Excel - My God.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF3G3H3F4G4H4F5G5H5F6G6H6F7G7H7F8G8H8F9G9H9F10G10H10F11G11H11F12G12H12F13G13H13F14G14H14F15G15H15F16G16H16=ABCDEFGH1 01/02/2008 Jan 08Feb 08Mar 082 3129313W00000220/01/2008 17:33:2629/02/2008 02:36:5456037 16,035.8540,001.15-4W00000127/01/2008 01:05:4620/02/2008 03:17:52200 41.13158.87-5W00000122/03/2008 09:06:1824/03/2008 08:33:57789 --..............................
View 9 Replies
View Related
Nov 2, 2009
See the attached spreadsheet and the comment boxes inserted containing my questions.
View 10 Replies
View Related
Oct 1, 2008
I need to find out over aged students by date of birth - now()-age started to school: DOB 8/16/2000 , Now() Age started school is 8/30/2005. Student is in grade 1. should be age 6, however, he is 8. Answer should be a little over 2 yrs.
View 2 Replies
View Related
Jun 30, 2009
I need a formula that will return number of aged breaks under each criteria in sheet 1 row 1 and each team based on the unique case number, so for example in sheet 2 I have my raw data starting in A1, i should get the below results in sheet1 ...
View 9 Replies
View Related
Nov 20, 2007
I've attached a very small spreadsheet where a few cells are highlighted.
These cells are where I can't figure out how to perform the appropriate calculations.
None of the cells can ever be moved. They are linked to AutoCad and MS Access.
I've calculated the few I know how to do, but when there are two types of criteria, I don't know how to set up the formulas.
View 12 Replies
View Related
Jul 31, 2006
I extract data from a data base and return the query in details, i need to merge 2 rows of data that has the same project number. example i have project ABC with billable time of 10hrs @rate of £200 and unbill time of 6hrs @rate of £0. The query will split the data as there is a difference in the rate.
I need a formula that recognise that it is the same project and merge the row together!
View 9 Replies
View Related
Jan 28, 2010
I have a large list of data and need to filter unique values of 1 column with a criteria.
For example:
A B
Car 1
Bike 1
Car 2
Car 2
Bike 3
Bike 3
Bike 4
Car 4
I want to filter out only the lines whereby the column A & B is the same comment/value.
Other words: both column A & B have to be unique values.
Based on the list above this means that the list should look like this:
A B
Car 1
Bike 1
Car 2
Bike 3
Bike 4
Car 4
View 10 Replies
View Related
Nov 16, 2006
I have a spreedsheet that has 5 worksheets in it named game1 thru game5 i want to count games won and games lost from each worksheet. how do i do that? the games won and games lost in the same cell on each worksheet.
View 9 Replies
View Related
Apr 30, 2009
i am having some difficulty modifing some existing code which is this: It will count all entries from G14 to G217 if *Ost* is in E14:E217
View 3 Replies
View Related
Feb 4, 2010
I have attached a spreadsheet I am working on. In it I need to count the number of cells populated in F5:F34. The issue I am running into it that there are formulas in those cells that determine what to display. Is there a way to count what cells are blank and not blank based on the result from a formula? In the attached for I should get an answer of 3.
View 2 Replies
View Related
Jun 19, 2013
Here's a link : [URL] .....
What I would like to do is create a column that will count the number of cells in each row whose background color is not red or yellow. The yellow background color was input manually, and the red background color was input through conditional formatting.
I have a spreadsheet full of GPS locations of different pumps on different dates. Each column represents a different pump, and each row represents a different date. The cells on this sheet contain one of two things - either a set GPS coordinates or the word "Repair" and a description of the repairs done. A few days ago I went through and checked the GPS coordinates to see which pumps were where every day during the past three years, and changed the background color of each cell containing GPS coordinates determined to be off-site to yellow. I then used conditional formatting to change the background color of each cell containing the word "Repair" to red. Now I need to go through and find out how many pumps were on location, not being repaired on each day.
Here's a link to a sample workbook. Any cell containing the word "GPS" just represents the fact that there are GPS coordinates in the cell. Their format is basically "(#####, #####)".
pump location example spreadsheet.xlsx
View 2 Replies
View Related
Jan 29, 2014
So an example xls is attached.
What I want to do is to get excel to calculate the numbers I have highlighted in yellow automatically, the data on the left is dynamically updated
View 3 Replies
View Related
May 30, 2007
i have a spreadsheet which is used to keep the training records for the company it counts no of staff and their skill levels for different parts of the job, when a person moves to a different branch the skill required changes and they have a set period of time to aquire the new skill.
when the time is up the colour of the old skill is changed to a red font by a conditional format based on the date.this is then counted by a calculation so the team know how many people are to be targeted for extra training.
the problem i have is the calculation will not count conditionally formated cells, so whenever i update the sheet i have to go to each cell with red text and change the font colour manually to red, this sounds really silly and is impossible to explain to anyone who does not understand conditional formatting. i e the people who will end up using the database
what im looking for is a way to count conditionally formatted cells based on the condition being met i.e. they are showing as red font.
View 13 Replies
View Related
Oct 28, 2008
I've got two worksheets. One contains data from a query, the other worksheet should count rows in the queryresults based on two criteria. See example.
I tried doing it with sumproduct in the normal variant and the array-variant...
View 8 Replies
View Related
Oct 21, 2009
I have a column of dates and want to count how many times there is an entry for a particular month. example:
(column D)
7/4/2009
7/13/2009
7/22/2009
8/1/2009
8/6/2009
9/15/2009
I want to be able to coun (countif?) how many times there is a July listing, August listing, etc.
=COUNTIF(D3:D20,">=7/1/2009")+COUNTIF(D3:D20,"<=7/31/2009")
=SUMPRODUCT(--(D3:D20>="7/1/2009")*(D3:D20<="7/31/2009"))
I have made them arrays.
View 4 Replies
View Related
Oct 25, 2011
i have big sheet with lot of data and in column B there is date dd-mm-yy.
How can i count specific product (column A) based on date (column B) for each month) on sheet B.
View 7 Replies
View Related
Feb 14, 2012
Any easy formula to compare two columens of fixed dates and count how many of each fall within a certain range of each other.
So, for example:
Col 1 Col 2
20/1/11 21/1/11
20/1/11 31/1/11
18/1/11 24/1/11
Lets say col one is a start date and col 2 is an end date. Now say I want to count the number of times the end date falls within8 days of the start
I would have thought that something as simple as
Code:
sumproduct(--(a1:a3<(b1:b3+8))
would work, but clearly I am making a fundamental error, and no matter what I try I'm getting stuck.
View 3 Replies
View Related
Apr 18, 2013
I am trying to count multiple occurences.
Instrument Name
Times Traded
AEON CO LTD
6
ASICS CORP
3
ELECTRIC POWER DEVELOPMENT C
10
In column C (Times Traded) I have used a simple countif formula: =COUNTIF($B$2:$B$8068, C2)
Now I have to check for how many times this instrument was bought or sold. This is in column A .... How will I count the Buy values ... cause this formula I devised doesnt work =COUNTIF($B$2:$B$8068, and(C2, A2:A8068="Buy")
View 4 Replies
View Related
Jul 25, 2013
I have a column of data (say in cells J1 to J20) such as
AB*
CD+
EF&
etc etc - not every cell has an entry.
In another column elsewhere, each of these items has a number associated with it, e.g.
AB 3
CD 3
EF 4
Note that the symbol (*,+,&) is not in the table.
What I want to do is this: For each value in column J, I want to lookup it's corresponding value in the table and add 1 to a count if the value is say 4 (so in the example the cell containing my result would be 1 because only EF has a value of 4). If I wanted a value of 3 my count would be 2 (because AB and CD both have a value of 2).
I think I need a combination of countifs, vloopkup and left (as I need to strip off the number in row J somehow) but I can't get the syntax right (left for example won't work on a range).
View 1 Replies
View Related
Aug 29, 2013
I have data in my worksheet as follows
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
[Code]....
I want to create a sum of all values in column B (Data Size) that correspond to the same Dept-div code in column A
Ex: 0667 the total should equal 43268.8 for the two cells.
I need to count all cells in these columns versus a specific range?
View 2 Replies
View Related
Aug 15, 2007
Need to writing a formula that will calculate the number of times a contract is used each month. Problem is that RMA numbers sometimes repeat. So I need to count the contract number for every unique RMA in a month......
View 5 Replies
View Related
Aug 11, 2008
I have a spreadsheet that has Leads in column H for eg Advertisements and Presentation dates in column K
I need to set up a formula that will count the number of dates (Items) in column K that is applicable to the item in column H for eg Advertisements, Referrals etc . There can also be blank items in column K which can be ignored
View 9 Replies
View Related
Jul 23, 2009
I'm using a sumproduct forumla to count rows based on specific data in multiple columns. So if column A equals 1 and column G does not equal 6 and column M equals 4 then count that row. I know how to do this. The problem I'm having is that I want to count column A if equals 1 or 2. I tried adding an OR comand in with my sumproduct but it doesn't seem to work that way.
View 4 Replies
View Related
Mar 2, 2006
I have one column with names and another with date ranges (JAN06) etc. I
want to count the number of times a name occurs within a given date range.
View 12 Replies
View Related
Aug 9, 2007
I want to count the number of times a certain value is found in columnB, only for unique values in columnA.
I have made a simple example file, attached. Column B shows Status which can be Active, Current, Dormant. Column A holds Item names. I want to know how many Active Items I have, but an Item can appear more than once in ColA, so I want it only to include in the count the unique ColA values.
View 9 Replies
View Related
Feb 3, 2012
I'm putting together a calendar that will have show when people are off on holiday, training, out of the office or ill. But as it will identify people by name I have changed to cell style to reflect just what activity they are doing. As such I want to be able to count over a year just who has done what.
Is there a piece of code that can count based on the text inside a cell and it's style e.g. "20% - Accent1"
View 2 Replies
View Related
Mar 19, 2007
From a base table, I need to create a new table listing only the unique combination of 2 columns and their individual sum. Example:
Base Table:
ITEM-----Loc-----QTY
Pen------School----5
Pencil----Office-----9
Binder---Office-----9
Pen------Office-----6
Pen------School---15
Binder---School---12
Pencil----Office----10
Binder---School----6
Pen------Office----11
I need to have a table resulting to the following:
Item-----Loc------Qty
Pen------School----20
Pencil----Office-----19
Binder---Office------9
Pen------Office-----17
Binder---School----18
View 9 Replies
View Related
Aug 11, 2007
Is it possible to count unique values in one column based on that column and another column? I have a report that has a column of dates (B:B), column of month and year (C:C), and a column of RMA numbers(A:A). The RMA numbers repeat if there is more than one part on the RMA. I would like to count the number of RMA created each month. Below should be Jan = 2; Feb = 2; Mar = 1............
I have a CSE formula that allows me to count based on two criteria but it does not count unique values and you have to specify the two criteria.
View 4 Replies
View Related
