I have a group of users in cell C1 and i wanted to count how many times they have process a payment as long as its value in Cell D1 is more then or equal to 1.
I tried sumif, but its totalling the amount. but i wanted is the number of transaction.
I have an excel spread sheet that is quite large it hold events for a piece of software that we are using what I am trying to do is display the last time was successful and the last time something failed
What I Want Return the MAX Value of the cells in a Column labeled start date for the rows where a Column labeled Agent =X and a Column labeled Mission Type = Y
In plain English with an example In another Cell I want to display the most recent start date for a mission of a particular type for an agent
Using the following table as an example I would like to determine the last time Agent 007 was on a Mission of type a
The Expected result should be 6/23/2008 If the same was asked for agent 99 the result would be 4/20/2008 And again for 66 the results would be 5/2/2008 If we then changed the mission type to b the results would be 007 6/23/2010 66 5/2/2010 99 4/25/2010
Here are the formulas i have tried already the results for these were incorrect and exactly the same 6/23/2012 the formula seems to be ignoring the first value and just calculating the last column to the latest date
Im doing a project and Ive heard that previously, this was done by copying and pasting which took countless man hours to do.
I want to make automated reports of call center data per day per agent. I would like for it to exported into a single excel worksheet with a tab for each Agent, listing the stats per day. What program would be the best to accomplish this feat. I was thinking of importing the information to access to create to muniplulate the information in a one click fashion. but im not sure if i could get the data back to excel.
I am having issues with this Macro since I have to run it on computers only supporting Excel 2003 and computers with Excel 2007. The Macro in use is as follows: --------------------------------------------------------------------------------------------------------------------------------
I have been asked to create a report that rank top sales agent for the month with the following variables:
Total # of sales-35% Total $ of sales-45% Number of calls made-20%
Given that there is more than one variable they have placed percentage weight on each. Thus my task is to cull said percentage and add their total thus getting a figure which would represent their ranking depending on how higj or low the total is.
What I would like to do with the information that I have put together on a different workbook is to reference an Agent (column A), and a time range (Date is in column D) and then copy the row from A to I for each occurrence during that range of dates so we can see how many and what errors have occurred during any range of dates selected. (I will have different people inputting errors at different times so the dates will not be sorted unless they have to be). I want to be able to select different agents/date ranges to be able to get a glance of how that particular agent did during this time frame.
Here is an example of the work sheet where the data is originaly.
[Removed]
Here is an example (in a different workbook) of what the sheet should look like if I was searching errors for Albert Ozier between 1/25/2010 and 1/28/2010.
I want to create a spreadsheet that I can export my transactions from my credit card onto -- is there a way to make it so that the transactions that haven't been covered by my most recent payment(s) are red, while the ones that are paid are green without manually going through & doing it? I know there's the IF, TRUE, FALSE formulas, but I'm confused on how to use them.
Basically, if I spend $1,000 between 5 transactions and make a $400 payment, I want the oldest transactions totalling up to $400 to turn green, while the remaining are stay red until a new payment is posted.
I need to solve for the Trans# I've included it here for clarity, but I need to be able to get that number based on the ACCTNO. Notice the Counter Resets to 1 when the ACCTNO changes from 1001 to 1013.
I need to compare in and out of money in multiple bank accounts.
Imagine in row one i have all the "INs" and in row two i have all the "OUTs"
Now, how do i compare say first transaction in row one to say 5 transactions in row 2 and find the relationship It can be:
1. Transaction IN 1 = Transaction OUT 3 2. Transaction IN 1 = Transaction OUT 2+4 3. Transaction IN 1 = Transaction OUT 3, or Transaction OUT 2+4
So if its a direct relation it just displays where they are equal, if they aren't how it will display which multiple transactions will be equal, and if there are 2 different possibilities it will show both answers.
If its only In = Out its pretty straight forward, but how do i code it to search for combinations of transactions say 1+2+5 efficiently.
1.1 In columns N and O, color the numbers in both the N and O cells green if, and only if, (a)the N cell's number is greater than O's, AND (b) both N and O cells' values are greater than the preceeding N and O cells' (i.e. a great value than one row higher in the column).
1.2 In columns N and O, color the numbers in both the N and O cells red if, and only if, (a)the N cell's number is less than O's, AND (b) both N and O cells' values are lesser than the preceeding N and O cells'(i.e. lesser values than one row higher in the column).
I have approximately 40 seperate sheets in one workbook. Each sheet is a unique part #. Each part has 6 different types of transactions possible. Let's say A-F. A-F each have a date associated when them of when the transaction occured. The transactions are sorted by date. I would like to write a formula that when Transaction A occurs what is the diffence in days until D transaction occurs. Or the time differnce between when B occured and the next F occured.
below is my datedif formula, but it obviously only works in a sequential order from top to bottom.
=IF(DATEDIF(M5,M6,"y")=0,"",DATEDIF(M52,M6,"y")&" years ")&IF(DATEDIF(M5,M6,"ym")=0,"",DATEDIF(M5,M6,"ym")&" months ")&DATEDIF(M5,M6,"md")&" days"
I want to SUM monetary transactions based on two criteria.
1.) if the transaction occurred within a certain month (Jan) 2.) based on the transactions category ("Obligations")
I have two formulas which successfully validate the data individually but I need to combine them so that both criteria must be met before data is summed.
I have a set of sales data which shows the dates of transactions and also the product type that was sold. I want to see the monthly sales for each product type. I can get a total for all product types over the months using the following:
I have downloaded some of my bank statements in excel format but they are just static data - ie, they are just numbers in boxes and the BALANCE column does not react when I take out a transaction.
I have put in a formula for the BALANCE column so it does now take its value from the previous day plus or minus transactions, but now I want to do additional things.
- How would I, for example, categorise several transactions as "HOLIDAY" [URL] ....... and then temporarily make them disappear so that I can see the effect of that on my balance? I can see how to hide/unhide transactions but that doesn't actually seem to have any impact on the balance column.
- Second query: how do I make my current spreadsheet a template so that when I download the next bunch of bank statements I can just apply all the formulae in this one to it?
I'm trying to group a year's worth of bank transactions. The initial data that was cut from pdf files is a date, payee and amount
1) how can I search down col A and give the sum of all like Payees, then total each set of similar payees? Maybe if first 6 characters match, then total until it comes to a different set. Total each set.
2) then, I need to assign a category to each set of payees, so if contains usps, then add category "postage"
3) formula to find all postage totals and combine for a grand total per category.
We've got a bug in our finance system where it can't handle any transactions that have sales but no related commission. The BI team provides a CSV file separately with this information and the sales team has to manually input it. I know how to create a template that can be uploaded into the system but don't know how to pull the data into the template from the CSV file.
I've created the attached example and what i'd like is a drop down box in cell B1 (template tab) listing all the customer codes in column B on the data tab and then based on your selection all the related transaction lines pull into columns A to F (starting on row 4).
How to form sub-totals quarterly in a typical list of transactions. Twist being it is not calendar year quarters but quarters from from 5 April to 5 July, 6July to 5 October etc. Typical table columns looks like:
Date Amount Transcation 5/4/14 200.50 bolts 7/5/14 50.10 screws 6/6/14 10.00 bolts and nuts -------------------------------------------- SubTotal 260.60
12/7/14 10.00 bolts and nuts 10/8/14 40.00 rivets 4/10/15 10.00 screws -------------------------------------------- Sub Total 60.00
I have this spreadsheet that has over 20,000 rows. I was asked to build a search page to will bring back all transactions based on a primary key (account number). Here is a sample:
I have these formulas auto-filled to the bottom of the sheet of each column. The problem I'm having is that with this setup, the return on the G column is giving me
#VALUE!
for all rows that do not have any values entered yet. Is there any way to fix the formula in column G so that it reads the value of the cell instead of the formula in the targeted cell?
I cannot get various formulas (Countif, Match, Frequency, Etc) to work properly.
I am trying to arrive at a total number of matches of numbers in cell range B1:G1 with any numbers entered into the cell range of K1:P11 and have the total of matches display in cell H1. However I do not want to count duplicate numbers from the K1:P11 cells. (if the number 5 in posted in K1:P11 multiple times I only need it reported once in H1)
B1:G1 is the constant and the numbers will not change - K1:P11 cells will be populated by adding numbers until the all the numbers in B1:G1 is completed and match.
I was given this spreadsheet to count attendance by entering the entry date and exit day, however it's counting the first day and the last. I'm needing it to only count the first day and not the exit day.Book2.xls
want to count up from a certain number but rather to a certain number.
Basically I am making a spreadsheet of products which already have product ID's, however there are 1000's of these. So I am doing them by manuafactuer, so some sheets do indeed start from 1, but pretty much every other sheet starts from a number like 1300, or even 2563.
how I would go about making excel automatically fill in the ID field?