Sumif Multiple Criteria: Sum Spend In Certain Categories
Sep 1, 2009
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 2 Replies
ADVERTISEMENT
Mar 24, 2014
I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.
View 1 Replies
View Related
Nov 27, 2011
I want a sumif where:
=sumif(f2:f500,
if column f has a 7 in it and if column e is greater than 5
View 3 Replies
View Related
Nov 12, 2008
Column A |Column B |Column C
Store_Name| Dates |Revenue_Generated
I want to sum the revenue generated in every row where:
Store_Name = "x"
Dates >= Date1
Dates
View 9 Replies
View Related
Dec 18, 2009
I have included my budget so helpers can see it. I am trying creating a formula on mini-dashboard on A8 (Under the total budgeted amount) that will check the total budgeted amount based on the values I enter on the categories sheet. Of course it will be controlled using the drop down menu on A2 on mini-dashboard. I thought I had this working with a sumif function but it seems to have quit working.
View 2 Replies
View Related
Jan 30, 2014
I'm trying to create an overview which shows a breakdown of the total minutes for each task on each given day.
I need to get a formula to work in the grey area which sums the total minutes (column C) based on matching two criteras of task (column A) and day (column b).
View 5 Replies
View Related
Dec 15, 2009
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 10 Replies
View Related
Jan 27, 2010
Can anyone tell me what is wrong with the formula on page 1 cell F56? it is not adding the 666 gallons at the end of column F for 426 - cc. Any ideas?
View 11 Replies
View Related
Feb 1, 2010
I would like to take a precise value from one table which corresponds to two different criteria. See the example attached with this message.
View 3 Replies
View Related
Feb 15, 2010
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 4 Replies
View Related
Mar 4, 2009
I'm trying to return a value from a data dump based on 2 seperate criteria, 1 being the name of a person and the other being the name of an activity. Is there any way of using an array and the SUMIF function to do this?
View 4 Replies
View Related
Jun 12, 2003
Sumif formula. I need to somehow put a loop on the criteria or something...
The formula is sumif(range, criteria, sum range). The problem I am having is that I have about 20 criteria to select, and sumif is designed for one criteria. I know that I can type the exact same formaula in and have something like this...
=SUMIF(D25:D107,Variables!A6,F25:F108) + SUMIF(D25:D107,Variables!A7,F25:F108) + SUMIF(D25:D107,Variables!A8,F25:F108) + SUMIF(D25:D107,Variables!A9,F25:F108)+ SUMIF(D26:D107,Variables!A10,F26:F108)+ SUMIF(D26:D107,Variables!A11,F26:F108)
This is OK for a few variables but some formulas have multiple criteria in excess of 15-20. the other thing is that the criteria list will grow over time as more variable are added, and instead of changing the formulas throughout the spreadsheet, I would like to have a big range that I can slowly fill up as I go along
The ideal situation would be if I could put a range in ( ie A6:A30) and then when I get a new criteria I just add it to this range. This will make administration eaaseier by giving me one location to update data.
I will show you an example.. I want to calculate the amount of petrol I use based on three criteria - BP, Shell, Caltex ( the criteria being petrol stations that come up on my bill).
Column
A/ B / C/ D
Row / description / Amount/Variables
1 / Shell Petrol / $10/Shell
2 / Cat Food/$13/BP
3 / BP Store/$24 / Caltex
4 / Dog Food/$23
5/Shell Petrol / $98
6/Caltex Petrol/$31
7/BP stuff/$30
8/Shell Store/$70
What I have been doing is this...
=sumif(B1:B8,D1,c1:c8) + sumif(B1:B8,D2,c1:c8) + sumif(B1:B8,D3,c1:c8)
If I could somehow get the formula to choose the whole column D as the criteria range and get the 'sum if' to somehow loop so it goes down the d column and does the same calculation, but just changes the criteria variable each time, then instead of changing the formula, I could just add to the D column each time I had a new criteria I wanted to check.
I looked into DSUM and Pivot Tables. DSUM seems to work well with numbers, not variables and Pivot tables did my head in, especially seeing I know that there is a way to do this in a formula based way..
View 9 Replies
View Related
Oct 10, 2007
I am trying to do a sumif function where it only sums when a different criteria is met in several different columns.
eg
sumif b:b shows portsmouth d:d shows calais f:f shows night then sum range g:g
View 9 Replies
View Related
Nov 12, 2011
I have an array that contains order numbers, tracking numbers and shipment costs. I want to get the total value of the shipment cost per order. the problem is, there are some duplicate shipments (ie same tracking number) and I don't want to include those. I can't delete the duplicate entries from the database for reasons I won't go into here.
so I tired to use a formula like =SUMIFS(C:C,A:A,A1,B:B,B1)
A B C
11462046 CJ225083125US 10.51
11462051 CJ225082247US 17.04
11462046 CJ225083125US 10.51
11462046 CJ225083564US 22.40
the formula doesn't work (won't even let me enter it) but if it did, it should give a result of 32.91. it would add C1 and C4 (but not C3 because even though A3=A1, B# also equals B1 and that is what I don't want to add.
I think maybe a sumproduct formula is what is needed but the negative criteria is throwing me for a loop.
View 9 Replies
View Related
Jan 26, 2004
I'm trying to create a SUMIF statement that that has an embedded OR statement within, and am unable to make it work.
Basically I want to say if "column Q" equals one of 2 criteria ("cat" or "dog") then sum the corresponding number in "column P" I've tried the following statement, but it is just resulting in a zero:
=SUMIF(Q9:Q32792,OR("cat","dog"),P9:P32792)
I've seen support on statements with multiple criteria in DIFFERENT columns, but not if searching for multiple criteria in the SAME column.
View 5 Replies
View Related
Mar 17, 2009
Im trying to have a formula look at two diferent columns and if they meet the criteria add the third column.
I tried using sumproduct but It wont give me anything but error messages or a zero.
Looks like this
=SUMPRODUCT(A:A="Stewart",(G:G="Fiduciary"),C:C)
Need it to say:
if column A = Stewart and Column B = Fiduciary then add up the amount in Column C
View 9 Replies
View Related
Aug 11, 2013
Excel - 2010
I have the following formula:
=SUMIFS(FEB!TRANS_Cost,FEB!TRANS_Cat1,CAT_Main3,FEB!TRANS_Cat2,"Computer Items",FEB!TRANS_Status,"Paid")
TRANS_Cost = B:B on FEB tab
TRANS_Cat1 = C:C on FEB tab
TRANS_Cat2 = D:D on FEB tab
CAT_Main3 = "Everyday_Expenses"
TRANS_Status = E:E
In short the formula adds the total cost of anything 'tagged' as Everyday_expenses, computer items, paid. I want to adapt it so that it takes the value from a defined cell such as Month, so that data can be pulled by month.
View 2 Replies
View Related
Oct 25, 2007
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 Related
Jul 22, 2014
I am trying to create an inventory control system that keeps tracks of what's in bins based on additions and subtractions to the bin. I am hoping to generate a summary output of bin contents based on the transaction list. I know how to do this using pivot tables, but am looking for a VBA or excel function solution since I will have hundreds of bins and thousands of transactions.
Bin number
Product Code
Pounds
1
A
50
1
A
-25
[Code] ......
I want to have the following table auto generated.
Bin
Product Code
Sum Quantity
1
A
25
1
D
75
[Code] .......
I'm using an array formula right now, but it is proving to be extremely slow.
=IF(ISERROR(INDEX('Harvest Generator'!$E:$I,SMALL(IF(LEFT('Harvest Generator'!$E:$E,LEN('Harvest Generator'!$E:$E))=LEFT(B$3,LEN(B$3)),ROW('Harvest Generator'!$E:$E)),ROW(1:1)),B$2)),"",INDEX('Harvest Generator'!$E:$I,SMALL(IF(LEFT('Harvest Generator'!$E:$E,LEN('Harvest Generator'!$E:$E))=LEFT(B$3,LEN(B$3)),ROW('Harvest Generator'!$E:$E)),ROW(1:1)),B$2))
Where Harvest Generator is the sheet where my table is on.
View 2 Replies
View Related
Sep 25, 2013
I am trying to create a a file that will serve as time log and dashboard report for consultants who work a maximum of two hours a day, with a strict(fixed) calendar schedule with multiple sessions. Some consultants may work one hour of a particular schedule on category A(Lead) and the second hour as category 2. How to create a sheet that sums the rates for all the categories worked on a given day without using a macro. Here is my sample data
Pay Categories:
Lead
Assistant
Admin
Pay Rates
Lead-$75/hr
Assistant - $40/hr
Admin - $20/hr
Sessions and Session Dates sample:
Session 1 - 10/8/2013 to 11/8/2013
Session 2 - 11/15/2013 to 12/15/2013
Session 3 - 01/06/2014 to 03/15/2014
*Note the dates will or should be listed in separate columns in order for them to be used for daily logging.
Employees (Sample)
Jane Doe
Jack Doe
John Doe
Desire goals of the report:
1. Daily log as employees work e.g if Jane Doe works on 10/8/2013 as a Lead and Assistant, ideally the data entry person should be able to enter these two values on one cell or pick the relevant value from a set list
2. The report would then match the value of the data in the cell for Jane Doe on 10/8/2013 with a table array with categories in one column and the pay rate in another, and return the appropriate pay value which can either be a sum of the two rates or the total hard coded into the cell.
3.Be able to create a dashboard report that would sum the totals by employee, month,sessions etc in a separate sheet vs other criteria such a budget etc.
View 1 Replies
View Related
Dec 31, 2013
I have data table with sales numbers by product, its wheel base, and over platform for months ranging from Aug 2013 - Nov 2013. This data table will populate with the complete month sales numbers after the month has finished going forward.
I have a table to the right of the listing the sum totals for each product (by wheel base and platform) by each quarter (rolling).
Now I need building a sumproduct average to calculate the quarterly average. This is not that simple because not all products (platform/wheel base) were available the entire quarter.
Also PLATFORM 3 in the tables are grouped - instead of listing platform 3a and platform 3b - using this in the formula:
(LEFT($D$2:$D$100,LEN($J9))=$J9)
Because not all products were available for the entire quarter - and the formula will need to count how many months that product, platform, wheel base was available during that quarter I'm guessing "=MOD(MONTH(A1)-1,3)+1" this may have to be used to count the number of months into the quarter the product was available.
Attached file. [URL] ........
View 9 Replies
View Related
Nov 25, 2013
I am trying to set up a sumif statement with two criteria where if the second criteria is true, the total in the sum range returned is divided by two.
I currently have this:
=SUMIF($G$6:$G$41,"digital",I6:I41)
I want to add in "digital/creative" as a second criteria (from the same criteria range) but I only want 50% of the result of these to be totalled.
is this possible?
View 6 Replies
View Related
Sep 17, 2009
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 6 Replies
View Related
Jan 14, 2009
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 12 Replies
View Related
May 21, 2014
Fabrication and Site Visit in Budget/Spend Analysis - Budget, not pulling through information from drop down boxes - in both cost type columns (they dont seem to be active any more either, not sure what I have done there!
View 1 Replies
View Related
Aug 10, 2009
I have a simple VBS script that puts the username & current time in columns. When the user saves that time is also placed into a column.
I would like to be able to calculate the amount of time a user has spent on the spreadsheet for the current month & if possible the total time all users have spent on the spreadsheet this months.
View 8 Replies
View Related
Feb 6, 2009
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 4 Replies
View Related
Jun 15, 2009
Im using a formula to identify a job description then add up the quantity which works fine. =SUMIF(Master!A1:A10,"Bread",Master!C1:C10)
But my problem is, I have a second description in column 'B' for example called 'White'. I've been trying to get the formula to look at column A & B and if they match criteria in formula, then add up C. But can't seem to get it to work with the second criteria.
View 2 Replies
View Related
Aug 23, 2009
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 2 Replies
View Related
Jan 8, 2009
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 9 Replies
View Related
