Sumifs / Multiple Criteria / One Of Them Being A List?
Jan 16, 2014
I will take out the different sheet references and instead show it as if it all on the same sheet.
=SUMIFS(F7:F1000,D7:D1000,C37,I7:I1000,P2:P5))
Im trying to get this too work.
So F7:F1000, is the data to be added together
D7:D1000 = the year
C37 = 2014
I7:I1000 = category, so this has stuff like (food shopping, fuel, car, bills, utilities etc)
P2:P5 = three criterias ([Balance], [Cashback], [Transfer])
Im trying to sum together the values which is 2014, and has the text [Balance] or [Cashback] or [Transfer] in the category column.
View 2 Replies
ADVERTISEMENT
Apr 11, 2014
Here is a sample of the formula I am currently using.
Formula:
[Code]....
This works perfectly for what I was needing but I am adding more criteria and running into a problem. I am rolling together usage for parts with different part numbers but the same function in order to reduce SKUs (as well as capture replaced SKUs) needed. These are located in Columns A:G. what i would like to add is more conditions to where the parts are going. Currently I have one location located in cell B2. To analize my usage further, I want to add another location in C2. So i want my formula to look like this.
Formula:
[Code] .....
when I use this it still only adds up with what matches in B2 regardless of the value in C2. Conversely, I can get the formula to work if I take out the extra part numbers.
Formula:
[Code] ....
View 6 Replies
View Related
Jul 12, 2012
I created the following formula and cannot get it the correct result. The return result is only adding column "P' with "275". How can I get it to recognize "276,"278"?
=SUM(SUMIFS('Supplies Only'!N:N,'Supplies Only'!E:E,{"2","26","27","59","86"},'Supplies Only'!P:P,{"275","276","278"}))
View 6 Replies
View Related
Apr 29, 2014
I have a below table as below
A B C
a aa 1
a aa 1
a dd 1
a aa 1
b aa 1
b bb 1
b aa 1
b bb 1
c cc 1
c bb 1
c bb 1
c cc 1
d cc 1
d aa 1
d bb 1
d cc 1
When i put the formula
=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"}))
it returns 12 However when i put
=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,{"aa","bb"}))
it returns only 5
I don't want to use multi formula like
=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,"aa"})) + SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,"bb"}))
I want to achieve this using SUMIFS only
View 5 Replies
View Related
Mar 21, 2014
I'm trying to sum data based on a the criteria of a date being between a certain range and sorted by a particular name.
I have included a sample sheet with two tabs:
Totals-I would like to get the sum of the hours a person worked in the range of dates being 1/1/2014-1/24/2014 (which is D1 and E1 in the current charges sheet) Current Charges-this is the page that I would like to gather the information from.
Below is a sample of the formula I was trying to use, but I get an error....my interpretation of what I was trying to input is as follows: Sum C2:C34 in the current charge sheet if B2:B34 is greater than or equal to D1 on the current charges tab (which is 1/1/2014) and B2:B34 is less than or equal to E1 (which is 1/24/2014) with A4:A34 in the current charges sheet equal to A2 on Totals sheet.
SUMIFS('Current Charges'!C2:C34,'Current Charges'!$B$2:$B$34,">="&'Current Charges'!$D$1,Current Charges'!$B$2:$B$34,"<="&'Current Charges'!$E$1,'Current Charges'!$A$4:$A$34,A2)
Test Data:
SUMIFS Test Data.xlsx
View 14 Replies
View Related
Nov 10, 2013
I'm working on a formula for calculating shipment weight per city it goes to per weight scale of the shipment pricelist. Although we have 4 customers with customer codes which we ship with our own trucks and some customers pick up there goods.
Therefore, my sumifs formula currently looks like this:
=SUM(SUMIFS('Freight SH 13'!$R$7:$R$14176,'Freight SH 13'!$R$7:$R$14176,"
View 1 Replies
View Related
Jan 7, 2014
I am trying to do a sum of data that contains three requirements - The country has to match either the USA or Canada and then it needs to pull for only a certain month (i.e. Jan, Feb, Mar) and then for that month only pull either Airfare, Hotel, Per Diem, or Car Rental. Basically looking for a way to Sum only the specific category for a specific time frame.
View 9 Replies
View Related
Mar 4, 2014
I am trying to improve how I write my formula. In this case I have replaced this horribly long formula:
=SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"CRT",TransactionSource,
"MC",ClassCode,"PR",TransactionCategory,1,TransactionCode,3006,ChargeOff,"OK")
+SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"CRT",TransactionSource,
"MC",ClassCode,"PR",TransactionCategory,1,TransactionCode,3031,ChargeOff,"OK")
[Code] ....
with this nice shortened version:
{=SUM(SUMIFS(Amount,TransactionAccountFunction,{"CRT","SCR"},TransactionSource,{"MC"},
TransactionCode,{3006;3035;3031},ChargeOffCredits,{"OK"},CAAPartner,A10,ChargeOffCredits,"OK"))}
I have pieced this together using information on various forums and it appears to work, but I have a question:
What is the difference between using a comma and semi-colon in this new formula?
View 3 Replies
View Related
Mar 13, 2013
I am trying to create a SUMIFS function to sum estimated hours for specific systems that do not have a Completed date. I do not want to sum remaining hours if it has been completed. i.e.
=SUMIFS(Hours, System#,"110-000-00, Complete (Date), ISBLANK)
System #
HOURS
Complete (Date)
110-000-00
0.5
210-000-00
0.5
[Code] .........
View 7 Replies
View Related
Mar 27, 2014
I have a large amount of data, 40,000+ rows about how students do in remedial classes including from whom they took the classes. I have pulled from it a subset of student success as relates to the separate instructors. Now I would like to extract a consolidated list of those instructors for whom the student gain on criterion based pre and post course assessments is above a specific level. I have no trouble writing if statements that will look at two types of data, the course taken and the average student increase to determine which instructors meet or surpass the criterion, but I can only do that within a list that includes all the instructors. Because there are more than an hundred such instructors, it would be easier for the dean if I could provide a short list of those that meet specific criteria. I believe that it would be an array that I should use, but I have been fooling around with this for 2 days now, and am finding myself at a series of deadends!
View 5 Replies
View Related
Aug 11, 2012
I have the following formula in Col C
=SUMIFS(C$275:C$379,$A275:$A$379,{"Idle Time","Oil stock *"})
The criteria "Idle Time" & "Oil stock losses" are in Col A.
I would like to add the values in Col C based on "Idle Time" & "Oil stock losses" which are in Col A
The formula =SUMIFS(C$275:C$379,$A275:$A$379,{"Idle Time","Oil stock *"}) adds the idle time value correctly, but ignore the values of Oil Stock losses
View 4 Replies
View Related
Oct 31, 2013
Having issues trying to do a sumifs function using multiple criteria to get a final total. I want to sum a list of values that have different transaction types from a transaction table. The only working way I have found is to do a sumif and create a CSE formula for it and drag it down and sum to the total. I am looking for a way to sum the the entire list of transaction types I need from the transaction tab and get to the total value rather than have to use a CSE and then sum. Basically I want to take out the sum of the CSE results....Is there anyway to do this?
View 5 Replies
View Related
May 24, 2014
Attached is a spreadsheet. The first worksheet is a summary of the data inputted in the second. I am trying to total the "total spend field depending on whether it is "Extra Works", "Spares & Materials" or "Other" which works fine. I would now like to sum depending also on the month that it was spent.
Order Tracker 2.xlsx
View 13 Replies
View Related
Dec 23, 2013
Following the syntax of the SUMIFS formula --> Can "criteria_range1" be the same as "criteria_range2" ?
It didn't work for me and I got the result as 0.
View 5 Replies
View Related
Dec 19, 2012
I have the following formula that I did:
=SUMIFS(Sheet2!L:L, Sheet2!B:B, 1, Sheet3!A:A, Sheet3!A5, Sheet2!X:X, "LOL", Sheet2!R:R, "delivered")+SUMIFS(Sheet2!L:L, Sheet2!B:B, 1, Sheet3!A:A, Sheet3!A5, Sheet2!X:X, "LOL", Sheet2!R:R, "shipped")
As you can see I have exactly same thing in the first SUMIFS function, and the second one except for the "delivered" and "shipped" criteria. Now, what I would like to do is to create a range that would be used instead of a single criteria.
Actually, I have more than 20 criteria but I don't want to do 20 separate SUMIFS and then adding them. Best would be if I could select a range of criterias (I would like to be updating that list easily in the future).
View 6 Replies
View Related
Feb 28, 2014
The below formula is working fine:
=SUM(SUMIFS(YTD_GBP,Region,"Central Budgets",Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"}))/1000
but if I try to add in a second criteria for Region, as below:
=SUM(SUMIFS(YTD_GBP,Region,{"Central Budgets","Corporate Services"},Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"}))/1000
I get zeros.
But if I do two separate formulas and add them:
=(SUM(SUMIFS(YTD_GBP,Region,"Central Budgets",Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"}))+SUM(SUMIFS(YTD_GBP,Region,"Corporate Services",Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"})))/1000
View 5 Replies
View Related
Nov 22, 2008
I'm trying to sort out an accounts receivable ageing into various age categories For example, for a customer with 3 months credit term, i've used the formula =SUMIFS($C3:$AA3,$C$1:$AA$1,">=4",$C$1:$AA$1,"<=9") to sum up amounts that are 1 to 6 months past due. For the next age category 7 to 12 months past due i've used =SUMIFS($C3:$AA3,$C$1:$AA$1,">=10",$C$1:$AA$1,"<=15").
As my customers have different credit terms (30 days, 60 days etc), i've to adjust the above formula accordingly e.g. instead of ">=4", I'll have to change the criterion to ">=7". This is time consuming as i've got a long customer list.
View 2 Replies
View Related
Jul 7, 2014
I am trying to create a SUMIFS statement with an alphanumeric criterion. I have found formulas that delete specific alpha-strings from the alphanumeric criterion before summing, but I have multiple different alpha-strings for the different departments.
Right now I have:
Code:
=SUMIFS(E4:E100,G4:G100,”>1.67”,)
But I need to add in that the alphanumeric values in C4:C100 are >= 3000. There are always 3 letters at the beginning of the course codes and then a space before the 4-digit number.
I tried
Code:
=SUMIFS(E4:E100,G4:G100, ">1.67",C4:C100,">=*3000")
But that, alas, did not work and it does not appear RIGHT works with SUMIFS.
View 3 Replies
View Related
Aug 21, 2013
Excel.xlsx
Attached is the example of the data, I am trying to sum the amount applying 2 criteria, within a particular month and other category. The formula tried is
=SUMIFS(D5:D10,C5:C10,"*June",E5:E10,H1)
However it is giving a value of zero only. I have tried other combinations as well.
View 4 Replies
View Related
Apr 20, 2014
I need to do a SUMIFS with 2 sets of Criteria, but I need the second Criteria to be an OR Statement. The numbers I am adding up is in Column E, with any where from about 20,000 to about 60,000 records (will fluctuate all the time). The first Criteria is easy - match up the name in AG12 with any of the names in Column C. The second criteria is to match up the numbers in Column A with any of the numbers in AD1 through AD40.
I tried this but it didn't work: =SUMIFS(E:E,C:C,AG12,A:A,AD1:AD40). I've alos tried a million other variations of that, imbedded SUM(IF( statements, SUMPRODUCT statements, and DSUM statements and no luck. I can't find anything that allows me to make that second second criteria look at the numbers in Column AD as being AD1 or AD2 or AD3 so on and so forth.
View 3 Replies
View Related
Mar 13, 2014
How can u use sumifs to meet 2 critera and then sum the one column..
=SUMIFS('[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$M$8:$M$100000,'[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$C$8:$C$100000,B5,'[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$B8:$B100000,$A$2)
That is the formula i used and it worked, yet when i try to use it for other names (where b5 would be) the numbers skew and do whatever they want..
I just want to sum all of the values in M:M which have B5 or "fiona" in columns C:C and also match the date from B:B with A2.
I believe ive done it right and as i say it does work with the first name in b5 but when i pull the formula down it doesnt work anymore...
View 5 Replies
View Related
Jul 8, 2014
So I have a formula as follows:
=SUMIFS(BD:BD,J:J,J15,AD:AD,AD15,ABS(BD:BD),ABS(BD15))
Although it is not working because I cannot use ABS() on a criteria range. is there any other way that I can have this formula sum the values if the absolute value of the number in that row is equal to the absolute value of 1 or more values in the same column but a different row?
View 7 Replies
View Related
May 28, 2009
Could ANYONE help me solve this formula based on critera sheet name.
On my main sheet workbook i have all the sheet names in column A with their description in column B. In column C i would like to have sort of this formula with result as total.
eg....
View 8 Replies
View Related
Oct 3, 2013
sumifs I was using but eventually got it to work by switching the order of the criteria. how the order of the criteria affects the result?
Working formula:
=SUMIFS('QB data 2011-2013'!$F:$F,'QB data 2011-2013'!$L:$L,'Daily Report Summary'!$M$4-1,'QB data 2011-2013'!$M:$M,'Daily Report Summary'!$N$4,'QB data 2011-2013'!$C:$C,'Daily Report Summary'!$J10,'QB data 2011-2013'!$H:$H,4)
Failing formula:
=SUMIFS('QB data 2011-2013'!$F:$F,'QB data 2011-2013'!$H:$H,4,'QB data 2011-2013'!$L:$L,'Daily Report Summary'!$M$4-1,'QB data 2011-2013'!$M:$M,'Daily Report Summary'!$N$4,'QB data 2011-2013'!$C:$C,'Daily Report Summary'!$J10)
The only difference between the two is that the criteria referencing "4" is at the end of the working formula VS the beginning of the failing formula.
I have never experienced the order having any affect on the returned value, but in this case the top formula works but the bottom returns 0.
View 9 Replies
View Related
Dec 9, 2013
I have time-worked spreadsheet use to calculate the hours worked over a year, now I need to sum the hours worked during a given period if they happen to be on a public holiday. I have a column (say A) displaying dates from 1/1/13 till 31/12/13, another column giving the result of the worked hours (say B). I also have a defined list of public holidays which I need to compare with (named Holidays). The working year is "divided" in 13 periods of 28 days each (the last one having 29 days). I was trying to use the SUMIFS function (since i have other constrains that need to be met)
e.g. =SUMIFS(B1:B28,A1:A28,Holidays)
View 9 Replies
View Related
Mar 19, 2014
I'm trying to put together a SumIfs formula to find the total count of procedure codes during a certain time period, for a given market.
I have the Market column, Procedure Code column, Date column, and count of Procedure Codes column.
The range of Procedure Codes I want to total on are in cells C4:F4 and they are:
99307993089930999310
It's fairly straightforward with the dates in the SumIfs as well as markets, but I'm not able to get the range of Procedure codes to work like I want. These 4 codes are just the criteria that will use to pull the totals from, which comes from a much larger dataset.
View 1 Replies
View Related
May 3, 2014
Let's say I have a simple table like below:
Region
Date
Qty
N. America
23/4/2014
10
EMEA
24/4/2014
50
EMEA
25/5/2014
20
I want to sum up all EMEA quantities that are in the month of April.
I've tried this:
SUMIFS(C:C,A:A,"=EMEA",MONTH(B:B),"=4")
I've also tried:
{=SUMPRODUCT(IF(A:A="EMEA",1,0)*IF(MONTH(B:B)=4,1,0)*C:C)}
Both do not work.
Also as the data grows each month, I would want to refer to the entire column in my formula, e.g. 'Region' as A:A (rather than A2:A4).
View 4 Replies
View Related
Apr 28, 2014
SUMIF and SUMIFS formula, where in I want to set criteria in the formula as greater than or less than or equal to value derived from another formula. I am inserting the following formula but excel is not allowing me to enter the formula.
=SUMIFS($C$22:$C$30,$E$22:$E$30,>=LEFT(L21,3),$E$22:$E$30,<=RIGHT(L21,3),$A$22:$A$30,G22)
View 3 Replies
View Related
Jul 10, 2014
I am trying to do a sumifs with several criteria, and I want one of those criteria to be when the date in Column C (Date Contains Day Month Year) is in 2014. So that it only summs the lines from 2014. Not sure how to specify my Criteria Range to do this.
View 3 Replies
View Related
Apr 12, 2012
I would like to use an (I think) easy SUMIFS formula. In this case I'm using only 3 criteria, and 2 of them work perfectly. With the third I want to SUM only IF the series begins with 112 in the example.
Criteria 1 Criteria 2 Criteria 3 SUM
SYS 1 111234 105
SYS 1 111345 111
ORA 2 112456 33
SYS 2 112998 70
SYS 2 112856 56
[Code] ..........
From what I've found out so far I could use "112*" ore something like it, but I cant get it to work.
View 2 Replies
View Related
