I have date in column A, vendor in column B, and amount in column C. How can I summarize data into table that add the amount by vendor by month and year? I was thinking of doing sumif but I don't know how to specify a range of date (for example, 1/1/2005 thru 1/31/2005) to sum the data together. I am attaching an small example worksheet to help explain better.
View 3 RepliesIf I have ten numbers which I would like to split into 5 buckets (1,2,3,4,5) how can I best achieve this?
A trivial example:-
The series 12, 12, 18, 18, 25, 25, 34, 34, 40, 40 would neatly spilt into 1, 1, 2, 2, 3, 3, 4, 4, 5, 5.
Similarly
The series 12, 13, 18, 19, 25, 27, 33, 34, 40, 41 would neatly spilt into 1, 1, 2, 2, 3, 3, 4, 4, 5, 5.
A non-trivial example
The series 12, 13, 40, 41, 42, 50, 70, 71, 89, 90, 91 I would like to be split into 1, 1, 2, 2, 2, 3, 4, 4, 5, 5, 5.
So groupings are based on how close consecutive numbers are to each other and there must be at least one value in each bucket (1,2,3,4,5). The number of data items can vary form 5 to, say 20 or so.
I need a formula that will help determine what "bucket" (based on today's date & due date) a date would fall under. I have 5 buckets:
RRC
0-14 Days
15-22 Days
23-30 Days
31+ Days
I am using the following formula:
=IF(D2="","N/A",IF(TODAY()-D2-26,TODAY()-D2-12,TODAY()-D2-5,TODAY()-D20,"31 + days"))))))
This formula works fine, however the next day I run the report the formula has to be slightly modified to place everything in the correct aging "bucket". Is there a formula that would alleviate the need to update the formula everytime I would like to check the inventory?
The dates are actually based on a weekly (Sunday through Saturday) schedule. So if the Due date is Prior to today's date it would fall into the 31+ category. If due up to 14 days (including today) it would fall under the 0-14 Day "bucket". And so on...
I want to categorize the # of seats into buckets (1-5, 6-10, 11-20, 21-50, 51-100, 101-250, 251-500 and 500+).
So if the number of seats is 19, than the bucket would be 11-20.
Is there an easier equation than IF(OR?
I need to ID various items by maturity bucket. each item has a maturity date and I need to put them in various buckets (1-9) based on date ranges.
the dates on these buckets would change depending on the initial date used (in this case I start at 3/31/2014), but i would update that date as needed, thusly the begin/end dates for these buckets would change.
I tried this formula which gives me an error (d2 is the maturity date of the first item, it would change as the list goes down):
=IF(AND(d12>=F1,d12=G1,D12=H1,d12=I1,d12=j1,d12=k1,d12=l1,d12=M1,d12N1),"Bucket 9",0)))))))))
F
G
[Code].....
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
View 9 Replies View RelatedI need to perform 2 SUMIF's on 2 columns of data to return a result and I'm not quite sure the best way of doing this. I'll give an example below.
I have 2 columns of data, both numeric and the SUMIF needs to say if H1:H100="10" and also if J1:J100="907". I can perform one or the other but not both.
I have the following sumif formula in a spreadsheet. Unfortunately, the columns where I search the data and the columns needing to be summed will vary depending on how the user downloads the data.
Using VBA I've designed a sub-routine that dynamically identifies and returns to the spreadsheet in cell AC57 the letter of the column that contains the criteria I need to search on (in this case, 'S') and the letter of the column to add up (in this case, 'H') which is contained in cell AD57. How do I get the sumif formula to accept these columns as string arguments?
The cell that contains column 'S' (the search criteria column) is cell AC57.
The cell that contains column 'H' (the sum column) is cell AD57.
I need to sum all values in column I from worksheet CostTrack if the cell in same row in column D is the text I am wanting.
For example, this formula will be in worksheet Recap C3:C25
In Recap Column B3:B25 would be the exact same text as in column D from CostTrack.
So I think it would something like this:
=SUMIF(CostTrack !I3:I25, IF(CostTrack !D3:D25="B3",
And then I don't know how to finish this out or if this is close to being correct.
Suppose I have some data with a code for each data point:
1 100
1 200
1 300
2 400
2 500
3 600
The first column is the code and the second is the data. I can use a SUMIF statement to sum all the data that have a certain code (like 1). What if I wanted to sum all data that meets one of a number of codes? Suppose I wanted to sum all data that meets 1 or 2. I know I can do this with 2 separate SUMIFs, but I was wondering if there was a way to do it with one.
I am trying to summarize data for my boss,
She has a spreadsheet with all of the employees of the company listed. Each employee is associated with a cost ceter (there are several hundred employees, and about 90 cost centers). Each month we count how many people are in which cost centers. I am using a sumif equation, and that works well, however, we keep the historical data, as well as the budget data in the same spreadsheet, so each month the "sum_range" column needs to change (ie column 4 for feb, 5 for March). I can use a find and replace to address a different column in my equation, but my boss would prefer something simpler.
I need assistance to create a formula that combines SUMIF and SUBTOTAL. I have created a SUMIF function for a long list of data for approximately 45 staff based on a type of errors.What I would like to do is use the filter by staff id. For example, when I use the filter to choose John, the SUMIF function does not calculate only for John but it still shows for the entire staff. Is there any way I could combine SUMIF and SUBTOTAL so that when I choose a certain staff from that long list, it will calculate accordingly.I have attached a simplified list of the spreadheet. What I need is when I filter by staff ID, the summary for error type and summary for errors by step to change automotically.
View 3 Replies View RelatedI have 3 worksheets in the same workbbook named Payment Distribution, Invoice Detail, and Corporate Phones respectively. Payment distribution has 3 columns (Corp, Dept, Cost). This worksheet is protected against changes. Invoice Detail has 2 columns (Deptartment, Charges). This worksheet is a workable worksheet.
Corporate PHones has 4 columns, (Phone #, Dept, Name, charges) with mulitple listings per row.
SUMif is used in the Invoice Detail worksheet for the department numbers and the subtotal of those charges for department numbers. The Payment Distribution worksheet contains a reference to a cell in Invoice detail for that department.
If I add a new department listing to Invoice detail and then sort the department numbers numerically, how can I get the forumla for SUMif to maintain the same no matter how I sort the data? It is causing issues if I sort the departments numerically if I add the new department to the bottom of the list.
Sheet1 contains a large set of data, including a date and a corresponding value.
Sheet2 (Summary) has a column called "Begin Date" and a column called "End Date". How can I use a formula to sum every piece of data that fits within the two dates?
I was creating a sales report: See below
Sales for Shook, Emily(131520) Total Sales $160.18
Sales for Clayton, Casandra(131008) Total Sales NO SALES FOR THE WEEK
Sales for Jofery, Rebecca(126310) Total SalesNO SALES FOR THE WEEK
Sales for Brea, Olga(124257) Total Sales$140.21
Sales for Mastro, Rachel(131521) Total SalesNO SALES FOR THE WEEK
Sales for Rodriguez, Blanca(111550) Total Sales $154.33
Sales for Katz, Mary Isabel(126840) Total Sales$269.87
Sales for Kitson, Mackenzie(130466) Total SalesNO SALES FOR THE WEEK
Sales for Blagniceanu, Adriana(126518) Total Sales$161.25
Sales for Best, Jessica(128350) Total SalesNO SALES FOR THE WEEK
Sales for Sanchez, Vanessa(126437) Total SalesNO SALES FOR THE WEEK
The Sales For (Last Name, First Name) was a concatenate I created to give everyone a unique identifier. Than I used a vlookup based on the the sales report to get there total sales
=IFERROR(VLOOKUP(D7,sales2,5,FALSE),"NO SALES FOR THE WEEK")
My problem is say for example: Sales for Sanchez, Vanessa shows up twice on the report stating she has total sales for $40 and $60 how can I get excel to calculate that within my VLOOKUP Function. If there a formula I can use to combine both values. I was think SUMIF is the most likely answer but I'm having problems.
The formula that works is =SUM(IF('Pipeline Input'!$X$13:$X$39=1,IF('Pipeline Input'!$H$13:$H$39="Lead",'Pipeline Input'!$K$13:$K$39,0),0))
I am trying to modify this formula so that the ranges are dynamic to allow me to add new data with an insert at row 13. What would the syntax of the formula look like if I use the INDEX function to allow the ranges to grow with new data? I have tried naming the defined ranges and entering the formula as =SUM(IF((CloseMo)="1",IF((SaleP)="Lead",(LoanAmt),0),0)) but I get a #VALUE! error
I need a solution for the equivalent of a SUMIF combining both vertical and horizontal data. The vertical cells align to the horizontal ones, but they're in a different table.
My attempted formula is: =SUMIF($H$22:$H$30,"TRUE",D7:L7)
*note that this is just an example set of data...my real data set is much larger (both rows and columns)
I need to be able to do this without transposing any of my data.
Things I've tried:
- Another option I tried was making D7:L7 a named range and using the transpose function (as an array) within the SUMIF formula above. I received an error.
- I tried using a bunch of IF statements added together (i.e. =IF(H22=TRUE,D7,0)+(H23=TRUE,E7,0)...); this actually works properly, but I get the "formula too long for cell" error when I put them all in (too many characters)
I'm using excel 2003 and windows XP professional.
To comply with the rules, I have already posted this request in the Excel forums http://www.excelforum.com/forumdisplay.php?f=8. I have 6 spreadsheets that the team that I work for edits on a regular basis. I also have one summary spreadsheet that our director reads and uses for her reports. Most of the forumulae pulling information through are simple = ones, but there is one section which is a bit more complicated. The team have to list the number of client related activities they have done by month - eg:
A------- B--------C--------D---------- E-------F
Month--- Client--ITT-----Quotation-- Demo---Visit
1--------A--------1--------2-----------1
1--------B--------1----------------------------1
2--------A--------1--------1
2--------B----------------- 1-------------------2
3--------C------------------------------1
I am using a SUMIF to total the number of ITTs etc done per month: =SUMIF('S:InternalSales Figures2006-2007[ES 06-07 Spreadsheet.xls]Activity'!$A$3:$A$1001,1,'S:InternalSales Figures2006-2007[ES 06-07 Spreadsheet.xls]Activity'!$C$3:$C$1127)
This works fine if both the Summary Spreadsheet and ES 06-07 Spreadsheet are open at the same time, however, if ES 06-07 Spreadsheet is closed, then all I get in the Summary is #VALUE!. At the moment, all I can think of to rectify this is to do the SUMIF in each separate spreadsheet and then copy the information over to the summary one, which is duplicating information!
I am using the following formuale to pick up a numeric value (from column G in Feb09 sheet: =INDEX(Feb09!G:G,MATCH($A10,Feb09!$B:$B,0))
Trouble is, the match it's doing in column b in Feb09 is listed twice in the sheet, but i'm only picking up the data for the value in the first line ... i think i need a sumif function.
I have a formula =SUM(IF($I$4:$I$302="A",$K$4:$K$302)) works fine.
I am using the same formula referencing a different column =SUM(IF($W$4:$W$302="A",$Y$4:$Y$302))
And I get the error : "A Value used in the formula is of the wrong data type"
I have many kitchens using the same recipes. I need to distill information down until I've got a summary of how much is being made. Uploaded is a condensed version of the point in the process I'm having difficulty with. This workbook will pull information from 8 other workbooks and give me excatly what everyone made on any weekday.
And from there, with the kind help of this forum, I figured out how to do a SUMIF based on the recipe number. And it summed up all instances of 'Recipe X' being used. However, it continues to SUMIF itself all the way down the page... which is good, because of how recipes are chosen for each kitchen. However, I only need to report one instance of each recipe.
In the uploaded example (and I apologize for the colorful sheet, but it helped me double check what I was working on.) ... I only need to report the PURPLE results elsewhere... the first instance of each SUMIF.
I am wanting to use these two formulas in one cell. Is there anyway to do this? If "AD3" is 0 I want this =SUM(X3:AC3) and then if cell "AD3" is greater than 0 I want to basically use this formula
=SUM(AH3,X3:AC3)-AD3.
Is there anyway to merge these two formula's?
I have a time entry spreadsheet where I total hours worked based on the time code entered in the cell next to the hours. I use a formula such as the following: =SUMIF(F11:AS19,"=AN",E11:AR19) which checks cells F11 thru AS19 for the timecode AN and when found, takes the hours in the cell before the code and adds them to the totals way off to the right.
The problem is that when someone does a cut and paste to the time data within the range of F11:AS19 and pastes it somewhere else, like to the next employees data (no formulas are being cut and pasted, just the detail data), the formula for that new line automatically changes to exclude the cells that were pasted over. It is as if Excel thinks that you pasted over that data so it is no longer of interest to you.
It is odd because you can cut and paste smaller ranges within that range with no effect to the formula but if you include the first column or the last column of the range in your cut, then the formula gets altered. If you cut and paste the entire range, the formulas all change to #REF!
Most excel/vba books say not to use the sumif function.
I have a very large workbook (9meg) and cant use VBA code as it slows down the interation calculations required.
I have used the sumif function to find and collate unqiue data on different worksheets.
Is there another function that i should be using?
I have a report I am attempting to populate with data from a pivot table in another worksheet. Column A holds all the reference numbers (primary key), column B contains various start dates, and I want column C to contain all the payments made since the start date for each reference number.
The source data is a pivot table with Row = Reference number, column = transaction date, values = transaction amounts. This is an extremely large table, as I'm processing data from almost 1,200 cases, which each have around 20 payments spread over the last year, on completely random days. What I would like to do is build a formula in my report which looks up the records for the reference number from column A, and then adds up all the payments which have been made after the date in column B (and ignore any payments in the table which are before that date).
And to make things more complicated:
if an error is generated, it needs to return as 0, not #N/AThe report has the dates in UK format dd/mm/yyyy, but the pivot table has the dates in SQL format: yyyy-mm-ddThe pivot table is connected to a SQL database via ODBC and has to refresh every time it is opened.
=IF(ISERROR(VLOOKUP(A2,'Transactions'!$A$2:$B$1194,2,FALSE)),0,VLOOKUP(A2,'Transactions'!$A$2:$B$1194,2,FALSE))
On the "CurrentCustomer" worksheet tab I am trying to accomplish the following:
When the merged cells in the F column (F3, F25, F47...F443) equal the word "Contract", I need the corresponding total cells in the E column (E23, E45, E67...E463) to add up, if and only if the corresponding F column merged cell equals "Contract".
For example, using the first three ranges, if F3, F25, & F47 all equal "Contract", I need the corresponding E23, E45, & E67 to add up. If F3 & F47 equal "Contract", but F25 equals anything other than contract I need E23 & E67 to add up only.
I'm working on a sheet where by I need to reference multiple columns of information to calculate a total.
Such as (simplified example of my real issue):
__A__B__C
1 20 AP1 C30
2 25 AP1 C30
3 35 AP2 E10
4 50 AP3 E10
My requrement is to only add values in column A wich have corresponding column B="AP1" AND column C="C30"
I've tried the following and keep getting errors:
=SUMIF(AND(C1:C4,"C30"),(B1:B4,"AP1")),A1:A4
I'm trying to do this:
=SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
It returns zero each time.
(BTW,
=SUMIF(B5:B12,"=D*",D5:D12)
and
=SUMIF(B5:B12,"=?D*",D5:D12)
both work just fine and return non zero sums.)
So my question is,
Where do I put the OR, or is there another way to do this?
I have a workbook which I am trying to clean up and make more efficient (in terms of formula calculating time).
In it are examples of array formula such as this one (which work correctly):
{=SUM(IF(Expected!$A7:$A894=5,Expected!$K7:$O894))/3}
From past readings here, I have heard that the array {SUM(IF(...} is slower in calculating than the SUMIF(...) equivalent.
As such i tried to convert is to:
=SUMIF(Expected!$A7:$A894,5,Expected!$K7:$O894)/3
This gives a different answer though. I thought for a single criteria they would be equivalent.
Q1: What am I doing incorrectly above?
Q2: Is there a more efficient version of the array formula I could use (SUMIF and otherwise), so I can have some options availible.
I got a column A containing a alpha-numeric data and want to sum up the numeric value of each cell which begins with "t" and have the sum divided by 486 (i.e. (120+230+25)/486). I tried the following formula but to no avail.
A
t120
t230
m45
m30
t25
My formular: sumif(mid(a1:a5,2,3,">0"))