Sumif Multiple Criteria: Sum Spend In Certain Categories
I have a column which lists total spend by vendor. I am using the sumif formulae to sum spend in certain categories, for example spend greater than £500,000. I would like to use two criteria, for example greater than £300,00 but less than £499,999. I am also using countif to get the number of vendors in each range of spend. How do I adapt my current code of "=SUMIF(D2:D1463,">300000",D2:D1463)" to look at greater than £300,000 but less than £499,999. I'm sure this is simple but like most things only simple if you know how.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Sum Of The Spend Column
I'm trying to use the sumifs function from '07 on the following table Plant No.Work Order No.Spend150010236514$7,035.67150020356981$3,474.331500#$26,963.701500#$169,851.50132010236515$972.00132012456589$5,110.041500#$27,067.20150020056523$26,948.96150010056269$8,439.64150014859856$7,045.50132016849578$34,950.00150020036152$9,853.751500#$13,180.38150015693265$10,822.38 What i want is get the sum of the spend column, if, plant is 1500, and work order no. starts with 1. The formula i am trying is: =SUMIFS([Spend Column],[Plant Column],"=1500",[Work Order No. Column],"=1*"), but that just returns a 0 value.
View Replies!
View Related
SumIf The Sum Of Unique Values Is Greater Than Criteria
I have a problem which is beyond my capabilities. I need excel to sum amount column (See example table below) by unique values in company column then conditionally sum those values based on the year of investment, stage of company and finally, the kicker, the summed Amount by unique companies value has to be less than a certain threshold. For example, I want companies in Stage Column of Exit only and in year 2004 only BUT only if the sum of the Amounts by unique company values is less than 100. So in the example table this would return 0. I have code and criteria written to sum unique values based on similar criteria such as sum if company Stage is Seed and Year is 2004 but for the life of me I can't seem to make the jump further. For Unique values I used this function: =SUMPRODUCT(($B$2:$B2=Year04)*($D$2:$D2=StageS)*($A$2:$A2=$A2))=1 Then: =DCOUNTA($A$1:$D9999,2, Criteria) or =DSUM($A$1:$D9999,2, Criteria) This along with criteria explained before returns the number of unique companies that are Stage=Seed and Year=2004 or sum of amounts with that criteria. I am trying to adapt this technique to get what I want but to this point have been unsuccessful. My best guess is that I will need to create an array of the unique companies (New Sheet column A) with the Summed Amounts (New sheet column B) and then run functions again with the easier equations and criteria? I am trying to avoid combining company records as each investment needs to remain individual and there are 16000 records. Any help would be greatly appreciated. Company Year Amount Stage XYZ 2004 10 Seed XYZ 2004 20 Seed ABC 2004 5 Early ABC 2004 25 Early DEV 2004 14 Later DEV 2004 19 Later TRU 2004 100 Exit TRU 2004 120 Exit TRU 2004 100 Exit
View Replies!
View Related
Lookup Multiple Criteria & Sum Result On Multiple Criteria
I am trying to solve a problem. I am currently using this formula = SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300) This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long. So the info looks like this in excel MARKETING 200612 -10 MARKETING 200701 -25 MARKETING 200708 -50 ECONOMICS 200709 -30 The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
View Replies!
View Related
Sum Categories At Each Change In Value
Could somebody please suggest a formula that will sum values at each change in category? I've played around with a pivot table, but for this it really doesn't fit. Column A is the category and there are totals in column B for each entry. The only place I need a formula is at the change in First and Second...so for any second all of the third's plus that second and for the first everything below. Attached is an example with outcome in column C xxxx First xxxx Second xxxx Third
View Replies!
View Related
SUMIF Multiple Criteria In 2003
I am using Excel 2003 and need help with sumif with 2 criteria. Please see attached worksheet. I am trying to find total costs for repective projects if they happend in 2009 only, else leave blank. In attached worksheet, see column c, row 17...thats what the result should be. Find All Instances of: Project A that happend in 2009 and from range c2..c7 Project B that happend in 2009 and from range c2..c7 etc. and show their sum in c17,c18, etc.
View Replies!
View Related
SUMIF - Multiple Criteria Using Concatenate
Im tryng to do a SUMIF formula that has several criteria as answer but using the concatenate formula, but it doesnt seem to come back with the answer - im using AND/OR formula aswell, but have a feeling im doing it wrong. It does work when i use the single concatenate, but not when i try a do more than one
View Replies!
View Related
Sum Across Multiple Tabs, Multiple Criteria
Excel 2007 My workbook contains 13 tabs - 1,2,3,...12, and Summary My data starts on line 4 of every sheet but varies in length - so far the longest goes to line 30. Rows used on all 13 sheet are as follows: A - contains facility names B - contains a two or three letter code C - contains hours D - contains dollars E - contains adjusted rate On the Summary tab I have listed all the facilites and two or three letter codes. I need to sum column "C" on tabs 1-12 when they match columns A & B on the summary tab. I have tried the following but can't get them to work: =IF($A5=""," ",SUMPRODUCT(--('1:[12]12'!A$4:$A$50=$A5),--('1:[12]12'!B$4:$B$50=$B5),'1:12'!D$4:$D$50)) I did not put the [12] excel added that automatically I had 1:12 =SUMPRODUCT(--(THREED('1:12'!$A$4:$A$50)=A10)*(THREED('1:12'!$B$4:$B$50)=B10),(THREED('1:12'!C4:C50))) I just seen the THREED for the first time today and am not sure if this was the correct place to try but it didn't work anyway
View Replies!
View Related
Formula- To Pull Cell Values Similar To A SUMIF Function (SUMIF(range,criteria,sum_range))
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 Replies!
View Related
Sum Numbers With Multiple Criteria
How to sum dollar amounts in one column as they relate to two other columns. The two green columns (Account & Dept.) need to correspond to the appropriate dollar amount (Total Transaction Amount) and then the dollar amount needs to be summed under each Dept # as it relates to the Account #. As you can see you can have different Depts. for the same Account.
View Replies!
View Related
Multiple Condition SUM With Or Criteria
I am trying to use an OR condition within a sumproduct formula. Why am I not getting correct results? (see attached) I want to sum all costs for each of my PO #s: If the Rebate Type is IPT, Wireless, or Security, then the result goes in the "Fndtn Ext Cost" column If the Rebate Type is IPT Advanced, Wireless Advanced, or Security Advanced, then the result goes in the "Adv Ext Cost" column. The formula I am using is as follows: =SUMPRODUCT(($A2=PO)*(OR(Rebate_Type=$F$3,Rebate_Type=$F$5,Rebate_Type=$F$7))*(Ext_Cost)) But for some reason, even though the OR statement is evaluating to FALSE (I tested it by itself), it's still summing ALL the extended costs for that PO #.
View Replies!
View Related
Sum With Multiple Criteria Without Having To Concatenate
I have data in sheet 1 and sheet 2. Sheet 1 contains a product code and line number and sheet 2 has the corresponding cost. I want to be able to bring back the cost to sheet 1 without having to concatenate the product and line number. I'm sure this is possible with a formula, but I am currently stumped. I pasted the sheet 1 and sheet 2 data below. Note that the sheet 2 data extends the full row and does not wrap.....
View Replies!
View Related
Sum With 2 Criteria Across Multiple Sheets
My question is similar to the thread Count/ sum with two criteria from multiple sheets. For example, I would like to use SUM Formula the number of widgets sold by store (Column A) by month (Row 5) for each salesperson (sheets Homer, Marge, and Lisa). I'm using the following Count Or Sum Two Criteria From Multiple Sheets equation to achieve the desired results: =SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$3&"'!A:A"),$A6,INDIRECT("'"&$A$1:$A$3&"'!b:b"))) where my worksheet names are in range A1:A3, the store names are in range A6:A10, and the months are in B5:D5. However, for each month, I have to manually change the column ie. from "'!b:b" to "'!c:c", "'!d:d", etc. In my real worksheet, the columns extend down to ED so to manually change the range would be extremely time consuming.
View Replies!
View Related
Sum By Criteria Across Multiple WorkBooks
Is there a shorter way of doing this formula using sumif on Excel 2002? This formula works but only up to worksheet 13, need it to go through 31. =SUMIF('1'!A4:A41,"12C",'1'!E4:E41)+SUMIF('2'!A4:A41,"12C",'2'!E4:E41))+SUMIF('3'!A4:A41,"12C",'3'!E4:E41).............
View Replies!
View Related
Sum Cells Multiple Criteria Different Sheet
I have tried many options but can't seem to figure this out. I have two tabs: Trial balance and Analysis On the Analysis sheet, I have a cell where I want it to look on the Trial balance sheet, find all codes these codes (7813.1, 7813.2, 7813.89) which are in column A:A, then give them the totals of what is in G:G.
View Replies!
View Related
Count And Sum For Criteria In Multiple Columns
I am trying to figure out how to create a formula using multiple criteria in different columns. Ideally, I need to use the whole column (i.e. E:E rather than E2:E400) because I don't want to have to update the formula every time I input data. I will simplify my spreadsheet for example purpose. Basically, column A has a unique identifier that either begins with an "M" or an "R." Column B either contains a person's name or a "-". Column C contains a dollar amount. 1. I need to be able to count all the cells in Column A that begin with an "M" AND have a "-" in Column B. 2. I need to be able to SUM the $ amounts in Column C ONLY for the items that begin with an "M" in Column A and have a "-" in Column B. Is there any sort of formula that might do this? I have tried SUM arrays but as I said before, I would rather be able to use the whole column.
View Replies!
View Related
Sum Multiple Non Continuous Ranges By 1 Criteria
I am trying to write an array using =sumproduct. I cannot get the second array to work. Whenever I split up the arrays they work fine separately, but not together. This is what I am putting. Any help is greatly appreciated. I am not sure if I am even using the correct formula. =SUMPRODUCT((B37:B51="Math")+(F37:F51)+(I37:I51="Math")+(M37:M51)),[(B18:B31="Math")+(F18:F31)+(I18:I31="Math")+(M18:M31)] So basically, I am creating a transcipt. So in column B if the subject is Math, I want column F to be added and so on and soforth. See there are more arrays, because it is broken down to grades 9, 10th, 11th, 12th. And what is being added is their credit (.5 or 1) for the class for each year.
View Replies!
View Related
Sum With Multiple When Two Ranges Or Criteria Are Met
What I'm looking for is that it ADDS the values, when 2 or more ranges and criteria are met. I don't think it's possible through the SUMIF because it allows "range,criteria,sum_range." But I know there's another way around, if so, please demostrate in a basic formula. Like.. I have something on A:A (name) and B:B (points), and the formula must meet the 2 ranges & criterias (name & points) to add the values in C:C (money).
View Replies!
View Related
Multiple Criteria Sum With Dynamic Ranges
I have a sum part of which is $I$4:$I$500. The 500 part of the sum is constantly being manually changed using edit replace because of a growing amount of data. In cell A1 ona another worksheet within the workbook I use the Count function to count the number of rows containing data. This cell is named DataCols Is it possible to combine $I$ with the named Range DataCols? I have got this far - ="$I$"&INDIRECT("ColNo") which returns 500 if I remove "&I$"& in front of it.
View Replies!
View Related
Sum Only If Multiple Criteria Is 1st Met
Have tried SumIf but still not good enough. The formula I'm looking at is to sum up 2 or more column if the below criteria matched: 1) Cell A1 = Cell A2 and / or Cell A3 2) Cell B1 = Cell B2 and / or Cell B3 3) Cell C1 = Cell C2 and / or C3 Which formula should I use?
View Replies!
View Related
Sum Index Results Matching Multiple Criteria
I have a spreadsheet schedule that pulls quantities from another spreadsheet's cells. if the part number of the reference row matches the schedule cell's part number (row), and the date of the reference cell row is 2 workdays after that of the schedule cell's column, the quantity is put in the right place in the schedule. that part works. the problem I have is that sometimes there is more than one quantity with the same part number and date (due to the parts belonging to different jobs). when this is the case, only one of the quantities is picked up by the schedule. i want the schedule cells to sum the quantities of the reference file with matching criteria. i also don't want to have to change the reference file at all in order to do this. below is the function that works (besides summing the duplicates). the part after the " " is what i would need to alter. HXS is the reference file where D is the part number column, I is the date column, and 5 is the index column referencing the quantity. C is the part number column in the schedule K is the date row in the schedule ....
View Replies!
View Related
Sum Values If Multiple Variable Criteria Are True
I'm trying to find the sum of a range of values based on multiple criteria, and the criteria is that the fields all have to be identical, then sum them. I've attached a brief example spreadsheet that has the fields A=City B=State C=Values What I want the formula to do is first find the range of all the matching states, then find the range of all the matching Cities within the states, and then sum the values based on them having matching city values. I've been able to do that with one criteria using SumIf, I'm not sure if this will help paint an image of what I want to do: =ROUND(SUMIF($B$3:$B$11,$B$3:$B$11,$C$3:$C$11),0) But I can't figure out the way to do multiple criteria against itself. Most of the results I get from Google using multiple criteria are using a set few values, and I can't seem to figure out how to alter those methods to work with my situation.
View Replies!
View Related
Sum Of Multiple Ranges Sharing A Single Criteria
IM trying to add the result of multiple ranges sharing a single criteria and have had no luck. I am trying to get the range from every other colum and have the total qty of the selected criteria display in a single cell. The best way to describe is countif with multiple ranges and a single criteria. perhaps countif isnt the answer, it is the only way i know to describe the issue though.
View Replies!
View Related
SUM (count) - Multiple Criteria Including Date Ranges
My setup is - excel 2003 sp3 / windows xp On one sheet (Data) I have a list of action items, each with owner; target date; classification and in some cases revised target date. I'm trying to report on these fields and provide a status, by owner and classification, of how many are overdue; due this month; due next month; due beyond 2mths. Using a SUM array formula on another sheet I can count the number that are overdue based on date; owner; classification; and target date, but can't find a solution if there is a revised target date. Any guidance you can give would be greatly received. =SUM((Data!$L$3:$L$27=D$18)*(Data!$P$3:$P$27=$C23)*(Data!$N$3:$N$27
View Replies!
View Related
Sum Range: Multiple Criteria In Rows & Columns
I posted this on Mr Excel, but did not get an answer. I hope someone here can help. I have a rather large financial worksheet that I am trying to extract some specific data from. The sheet contains budget and expense #'s for the fiscal year for several projects. The project names are listed in column G and the same project can be on multiple lines, depending on what the expense is. Column J contains either "Cap" for Capital, or "Exp" for Expense. The monthly data starts at Row L. Each month has 6 columns, the one that matters for this scenereo is the 5th one for each month, which is the Forecast Column. Our Fiscal Year is April 1 - March 31, but this sheet ruins from March - March so that we can carry over anything from the previous year. So the forecast column for March is column "P", April is column "V" and so on, adding 6 columns for each month....... With me so far? What I am trying to do is get the total forecasted expenses amount for a specific project for the remainder of the fiscal year. In other words I need to look for a specific Project Code in Row G, look for "Exp" in Row J, Then get only the numbers that are in the Forecasted Column for months in the future and add them all up. Just in case that is not totaly clear---- criteria to meet Column G = "Project Code" Column J = "Exp" Row 2 > this month Row 3 = Forecast.......................
View Replies!
View Related
Sum By Multiple Criteria & Use Cell Values As References
I want a total of column F based on criteria in column D and column K from the worksheet named in cell C9. I have put in the indirect function in the first part but am messing up the syntax in the second and third part of the formula. "Indirect(C9&" should be where 'Oct2009' is shouldn't it. Where am I going wrong with the following formula? SUMIFS(INDIRECT(C9&"!$F$2:$F$11000"),'Oct2009'!$D$2:$D$11000,$C$1,'Oct2009'!$K$2:$K$11000,"YOKO")
View Replies!
View Related
Multi-Criteria Sum Based On Multiple Column Cells
I have a large worksheet that lists employees and the hours they work each day in a pay period. For Example: Column A holds a date and Column E holds the number of hours worked for a paytype. So i want to create some function that sums the numbers in column E, but only those where column A holds a date. Then i want to return that number to a cell, say C20. I think I've managed to tell the macro which rows i want to return values from, but I can't figure out how to say "add these numbers together and put the total in C20." I'll check this frequently if clarification is needed.
View Replies!
View Related
Sum With Multiple Criteria A Number Of Rows And Columns From Original Data
I have 6 columns of data laid out as follows, columnA columnB columnC columnD columnE columnF XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX I need to find data in columns B and C and add the figures from column F, which drop down one line. I have written the following formula which is giving me far higher figures than I should be getting = SUMIF($B$2614:$B$2640,"bhree*",IF($C$2614:$C$2640,">=01/07/2006",( OFFSET($C$2614:$C$2640,1,3,1,1))))
View Replies!
View Related
If Match Then SUM? - Wanting To Add Multiple Values If Criteria Matches
Column A would be Recipe Numbers, Column B the Planned Total. I want to search column A for all matching recipe numbers. Then, any recipes that match, look in column B for the planned totals, adding all incidents where they match. Then the most difficult part, which just occured to me now. I only need the Total Planned Total and Recipe name reported once. Recipe#..........Planned Total 450................100 600................75 620................125 450................50 620................180 450................100 600................200 450................110 I will not be able to sort these lists. If-Match-Sum? to display: Recipe#............Total Planned Total 450....................360 600....................275 620....................305 I've researched for the first part... and it seems like Match will only find the first match and not look further? Am I wrong on this? How to display the results hadn't even occured to me yet, since I was still trying to figure out how to GET the result.
View Replies!
View Related
Nested SUMIF Statement Or Multiple SUMIF
I 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.
View Replies!
View Related
Sumif With Two Criteria
I don't know why I can't figure this out, but it has been too long, but I need a formula that looks for yesterday's date and adds up any time that matches that date and that person from the list. So like in this example, for yesterday, my answer would be 1 hour for Ashley. A B C 1 1/7/09 Ashley 0:10 2 1/8/09 Ashley 0:10 3 1/9/09 Bob 0:10 4 1/7/09 Ashley 0:50
View Replies!
View Related
SUMIF With Criteria
I am trying to take a census by date and name for every half-hour using the attached data. So for example, I would like to know how many people Roger had that walked-in and walked-out for each date on the spreadsheet and during what hours. The census would be every half-hour and not double count times. I could actually deal with just the min and max time for the day (by date and name) and having some indicator (say a one or an X) in the cells when Roger had at least someone anytime between walk-in and walk-out. It's kind of like getting a "time worked" based on the data that's provided to me. So I know how many hours Roger worked by day.
View Replies!
View Related
SUMIF 2 Or More Criteria
I have a workbook with several different worksheets. I am attempting to pull data from a worksheet, I will name it sheet1. Sheet1 has 6 columns. The data I am interested in is in B and F. Column B has account numbers ranging from 1 - 1,000,000 and column F has the total account balances. What I am attempting to do is to sum the data in F that falls between a criteria in B. For example I want the sum of F that correspond to accounts 4999 thru 7555. I have tried several variations of the same formula from my online search and am still unable to get it to work properly. I have tried using ">=4999" "<=7555" and I cannot get it to work properly. It seems fairly straight forward, but I am perplexed as to what I am doing wrong.
View Replies!
View Related
Adding Multiple Cells From Multiple Sheets With Sumif Function
I'm trying to put together a spreadsheet that tracks disc capacity increases, affected by any incoming projects. I've managed to do so for one project, but would like to for up to 10. The way i've designed the solution (i'm sure there are far more elegant ways, but hey) is thus: A forecast worksheet keeps track of a grand total, taking information from sheets P1 -> P10 (being projects 1 to 10). I am unable to figure a way to add up all the increases from all 10 project worksheets with one succinct formula. What I use so far is: ='P1'!C83+SUMIF('P1'!E82,"=2009 - Q1",'P1'!D82) ..................
View Replies!
View Related
{SUM(IF(...} To SUMIF(...)
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.
View Replies!
View Related
|
TRACKER
