I am trying to get excel to return the MAX number of a range of numbers that have multiple if criteria.
Here is some sample data with the final product:
COLUMN A
COLUMN B
COLUMN C
FINAL PRODUCT
x
a
3
7
x
b
5
7
x
a
4
7
y
a
10
15
y
b
15
15
y
a
2
15
I'm trying to set up my formula so that if there is no criteria match, rather than returning 0 it leaves the cell blank.
(I recommend skipping the below text and just opening the attached spreadsheet, instructions/what I'm looking for and trying to achieve and well laid out requirements and examples are in the spreadsheet and much more thoroughly explained then I can do below)
I'm setting up a table that grabs data from columns in a raw data spreadsheet.
For the purpose of the table I'm making, the formula needs to match up both the House, and the year/month from the raw data sheet, then if the two match and are side by side, return the value to the right either in the Plan Field or the actuals field depending on which field in the table the formula is in. If there is no entry or matching criteria for both the house and Month then it should leave the cell blank, not show a 0
So I have a SUMIFS formula that I'm trying to set up so that if there is no criteria match it leaves the cell blank.
So =SUMIFS(sum range, criteria& range, criteria&range), if no matching criteria can be found, leave cell blank, else if matching criteria found then return value from the sumrange.
This is the exact formula I am using:
=SUMIFS(INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B9, 'Raw Data'!$1:$1, 0)), INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$A$8, 'Raw Data'!$1:$1, 0)), Table!$A$9, INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B$8, 'Raw Data'!$1:$1, 0)), TEXT(Table!C$8, "yyyymm"))
Yeah kind of hard to follow, so attached is a very detailed and well laid out spreadsheet showing the example of what the formula is, how it's working/what it's returning and an example of what i want it to return/look like, so please check out the spreadsheet, it explains it a million times better.
Essentially though i want the formula set up so that if there is no criteria match, rather than returning 0 it leaves the cell blank.
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 RelatedFollowing 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.
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).
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?
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'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.
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 RelatedI 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.
=SUMIFS($F$2:$F$204238,MONTH(E2:E204238),"=month(referenced_range)")
*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.
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,"
I am trying to devise a formula for the data set below (into a different tab). The table below is something like what I have:
Day 1
Day 2
Day 3
Day 4
[Code]....
Though my table is a bit more complicated (it has empty rows in between and a much larger data range).
I need to reflect this in another tab in the following format:
Day 1
Day 2
Day 3
Day 4
Spoke 1
Spoke 2
Spoke 3
As I fill in data in the first tab, I would like this tab to automatically pick it all up. Is there any way I can do this? I may add rows etc. so I do not want a simple addition.
I'm trying to create a sumifs that has 'before date X' and 'on or after date Y' as two of the criteria.
In the past, I have simple done a DATE(x,y,z) function inside the sumifs, but I'm trying to change this for reasons that would take a little while to get into. Let's just say it would make my coworkers lives much easier.
My hope is to get these date range criteria by referring to ribbons which have the dates in mind in them. Here is the formula I have now, and an example. See the red part of the formula.
Say I'm trying to have before Aug 1, 14 and on/after July 1, 14 as two criteria. FBP column A is where I would have the dates that this criteria would search through:
B1: 7/1/14
C1: 8/1/14
Formula: =SUMIFS(FBP!$G:$G,FBP!$B:$B,"=E",FBP!$A:$A,">="&B1,FBP!$A:$A,"
Shortened for example;
I have created a drop down list with gas, groceries and travel as the categories.
column a is labeled expenses and formatted as currency.
column b is labeled category with the drop down list in each cell.
column d contains each item, gas, groceries and travel on separate rows.
column e will contain the total for each item in column d using "sumifs formula".
If there are no travel expenses, instead of returning $0.00 I want the cell to be blank.
I know how to do this in a simple sum function but sumifs are sumthin else.
I have tried using <> for not equal to and everything else I can think of and I usually get a VALUE error.
how do I get it to be blank instead of $0.00???
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
I would like Excel to return me a value based on many criterias in the same worksheet.
E.g
A
B
C
[Code].....
QN: For any of the value from Col B to Col N, i want the result of Column A appearing in my cell.
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] ....
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
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
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"?
=SUM(SUMIFS('Supplies Only'!N:N,'Supplies Only'!E:E,{"2","26","27","59","86"},'Supplies Only'!P:P,{"275","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 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.
I have a couple Sumifs formulas that I am trying to return monthly values for but there are small discrepancies on the values that they return. When I manually select the cells that I'm targeting with the formulas, I get a different value. I have checked the formulas 100x & can't figure out what is wrong with them.
Example:..............
I am trying to work out a formula to return a sum based on 2 variable, but one of the variables in in a column and one in a row, anyway I can do this?
I don't want a normal sumif returning the relevant column as the relevant column will change based on a cell that can change.
I basically have a list of products sold (products listed down the page) by month (month listed across the page), I want to sum all the products in a particular month on a separate tab (both product and month can be changed).
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.
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 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
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.
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...
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