SUMIFS Function With OR Statement In One Criteria?
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.
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.
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.
I want to sum the NUMBERS in golumn G if the DATE in column L matches the DATE in cell F37 AND if the TEXT value in column B is equal to the TEXT value in cell G36.
Is this is just an example, but the logic should hold true. Data for example is in A1:C5. I want to use a SUMIFS statement that will look at type of Vehicle and Dates. So for the example in cell D1 (I wanted to add only Cars sold between May 1-2), but my current equation = 0, where I am expecting 20
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
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?
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.
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.
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).
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.
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"?
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'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.
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?
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....
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.
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)
I am trying to use the sumifs function to add a column that is in pounds but with multiple criteria.I am trying to add a price field but between two dates (between the beginning and end of each month) another criteria from a range of cells I have copied the formula I have written below
try to explain a little column K is the column I need to sum column B is the date field that I need to extract i.e. 1st to 31st January (I have put these dates in a cell that correspond to I17 and J17)
then the last bit column W is another criteria that I need met, this works for just one cell however if I try to add another criteria in another cell it doesn't work after M17 I add to the formula ,Sheet1!W:W,"="&M18)
Is there an easier way to construct the formula in the sales revenue col of the monthly analysis table on the summary tab so that one does not have to enter the specific dates into the formula?!
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.
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.
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.
I am trying to create an equations that sums up a range based on matched dates. I have considered using an array, but that turned out to be much more work than necessary.
I have never used the SUMIFS before and having trouble, here is the equation I'm using.
*F2:F204238 is the range that needs to be summed up *the month(referenced range) is in date form *I was able to pull and evaluate all the data on it's own, but when using it in the sumif equation it gave me zero result so it must be a syntax error.
