SUMIFS - Life Ruining #VALUE! Message...
Oct 10, 2008I have no idea why I am getting the #VALUE! message...
Any light shed on this would be much appricated, or an alernative formula.
ADVERTISEMENT
Change The Name Of A Folder With Ruining All Links
Dec 18, 2013The CFO wants to change the name of the top folder on our network drive at work.
I have literally thousands of files in different folders within this top folder, and changing the name of the top folder is going to cause hundreds of thousands of reference errors.
Is there a best way to protect these links, other than to find+replace for hours and hours?
Formula To Calculate Remaining Life Of Asset?
May 15, 2014I am trying to figure out a formula to calculate remaining useful life. I have the following information:
In Service Date (Cell W2)
Estimated Life (Cell X2)
Aquired Value (Cell Z2)
Is there a formula that will just calculate how much life is left based on the information i have?
IF Formula Used To Price Life Insurance Policies
Jun 20, 2009I price life insurance policies and need a formalu for the following new Fund:
If the sum of the first two years of premium added to 10% of that total is equal to or less than 25% of the face amount, the case fits into the general parameter.
Here is what I have, but it isn't working:
=IF((U2+V2)*10%)+(U2+V2))
Formula To Work Out Maintenance In Life Cycle Model
Jul 4, 2014I'm try to do a what IF Formula, so I can automatically add the cost into the right year for a lifecycle cost
My criteria is in column K I have either New Build or Existing and column AT I have year 12 of the lifecycle and column AZ I have year 18
And what I want the result to say is IF Column K Say New build I want the value of the first maintenance year to start in year 18 and column k say Existing I want the value of fist year Maintenance to start in Year 12
I already have this formula in the Column AT&AZ working out he value
IF(ISERROR(MROUND((AQ$1-$Q2),$P2)),"0",
IF((MROUND((AQ$1-$Q2),$P2)+$Q2) = AQ$1,$AF2,"0"))
Component Life Cycle Management - How To Extract Correct Data
Apr 30, 2013I am trying to create a life-cycle management sheet that is simple for the user but gives specific data.
The user would input "Date", "Machine type", "Component Set No.", and "Component". This would happen over a period of time and build up a picture of how often each component was lasting and it's average life in days.
I am fine from the user input side but am struggling to figure out how to extract ONLY the data I need to display in a graph.
For example: If I want to see the average no. of days "component X" is lasting before being replaced I've not been able to extract just that data. I have tried using a pivot chart but this won't do exactly what I'm after.
See the attached for a clear explanation : Component LifeCycle Example.xlsx
Placing Icons With Message In Message Box
Jul 11, 2009How do you add icons into a message box. ie a warning triangle etc.
View 3 Replies View RelatedMAX Over A Set Of SUMIFS
Feb 25, 2013I'm trying to find the maximum over a set of conditional sums, preferably using an array formula or some other single cell solution. My experience with array formulas so far has been to copy things off the internet and modify slightly to my needs.
Here is how my data is set upA
B
C
D
Trait1
Trait2
Value
Trait 1 to Max over
Cat
LongHaired
3
Cat
[code]....
The "Trait1" I am summing over will remain constant for each refresh of the spreadsheet. I want to return the max value of the sum over each "Trait2" that falls into the "Trait1" category.
In this example for Trait1 of "cat" I would return 8 (5+3) that corresponds to LongHaired cat.If I were writing straight SumIf and then Maxing over I would write
Code:
=SUMIF(C:C,A:A,$D$2,B:B,"LongHaired")
for each unique Trait2 and then Max over the set of outputs.
SumIfs Between Two Dates?
Jan 1, 2014I am using a SumIfs formula and I want to add a criteria that only sums the Sum Range when it is between two given dates (01/01/2013 and 31/12/2013).
View 2 Replies View RelatedUsing TODAY() In SUMIFS
Jan 20, 2009Using TODAY() in SUMIFS
so, this formula works as expected:
SUMIFS Alternative
Jun 17, 2009I've been looking for an alternative to SUMIFS as I have a sheet I need backwardly compatible with Excel 2003. I've looked at SUMPRODUCT which I understand is a good alternative to COUNTIFS but does not allow for summing. I've now moved on to looking at an array formula I picked up from an internet search, but it's not working properly for me. The crux of my Excel 2007 formula is:
View 2 Replies View RelatedSUMIFS With Two Or More Criteria?
Aug 11, 2012I 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
Sumifs With Iserror?
Oct 15, 2009I have the formula below which sums range dv77:dv81, if dy77:dy81 is not an error. Works perfectly.
={SUM(IF(ISERROR(DY77:DY81),"",DV77:DV81))}
However I cannot seem to modify it correctly to work with a sumifs formula. Could someone please show me how it might work?, or a workaround?
For reference the sumifs would sum range(DV77:DV81), if range(E77:E81) is "Neutrogena" and range (A77:A81) is "9".
SUMIFS By Month And Year
Jan 17, 2013I am trying to create a master spreadsheet to sum up other tabs for the number of funds that are going operational by month. The date is formatted at "January 31,2013".
See attachment as an example : Client Schedule Example.xlsx
SUMIFS With Criteria Range
Oct 31, 2013Having 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 RelatedSUMIFS Only UNTIL The Number Repeats
Apr 8, 2014I have the following figures in my spreadsheet and I have been trying to use SUMIFS. However, I don't want to count values in a continuous way, rather than that, I need to count the values ONLY until the number that is being counted repeats.
Example: Spreadsheet from Column Q1 to Column Q6:
Q1 Controlling
Q2 8
Q3 8
Q4 8
Q5 8
Q6 8
On this case, the Excel would count the number 8 five times in this array. However, If there were more number just below the number 8, it would count them as well and that's not what I am looking for.
Q1 Controlling
Q2 8
Q3 8
Q4 8
Q5 8
Q6 8
Q7 5
Q7 8
What do I need is not to count them when the number being counted has stopped to appear eventhough it can be found at Q7.
For my needs the answer should be that the number 8 was counted 5 five times rather than 6.
SUMIFS With Multiple Criteria
Apr 11, 2014Here 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] ....
SUMIFS More Than One Criteria And A Month
May 24, 2014Attached 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
Using SUMIFS Across Multiple Sheets?
Jun 21, 2014Using the SUMIFS function, I could successfully get the expected results from extracting data from 15 base separate excel files (from where the data will be extracted). However, whenever I am re-opening my master sheet(where I want the data to reflect) it gives me #VALUE!
Only after opening all my 15 base sheets, my master sheet fetches data.
Adding Array To Sumifs?
Aug 12, 2014I am trying to do a formula that will add two different sums and so far I have this:
={SUM(IF('FAC Open POs'!$F:$F='Cash Waterfall'!$B6,'FAC Open POs'!$V:$X))}+SUMIFS('Cash Report'!$O:$O,'Cash Report'!$S:$S,'Cash Waterfall'!$B6,'Cash Report'!$T:$T,'Cash Waterfall'!G$4))
But I keep getting an error. I know that the formulas apart work, but I keep getting an error with trying to use an Array formula like this.
Using Sumifs (Or Other Total Functions)
Dec 2, 2013I am trying to solve the problem I am having for my weekly report. Currently our Reporting has to be done Via Excel, with no reporting consule to have dynamic ability. I am trying to put a formula table together so all my manager has to do is dump raw data and the calculations take care of the totals for the table. I am running into the issue of when trying to return certain data at a certain date. So here is what I have and what I need (If it can be done without changing the formula each week)
For the MTD calculation I have:
=SUMIFS('Raw2'!$L$2:$L$902317,'Raw2'!$O$2:$O$902317,"=2013",'Raw2'!$M$2:$M$902317,"=11",'Raw2'!$N$2:$N$902317,">
=1",'Raw2'!$N$2:$N$902317,"<=31",'Raw2'!$B$2:$B$902317,"=0000013")
The raw data wont have information past the current day of the month so the <=31 allows for the current month to calculate correctly.
Where I am having trouble is the Previous Month Formula:
=SUMIFS('Raw2'!$L$2:$L$902317,'Raw2'!$O$2:$O$902317,"=2013",'Raw2'!$M$2:$M$902317,"=10",'Raw2'!$N$2:$N$902317,">
=1",'Raw2'!$N$2:$N$902317,"<=24",'Raw2'!$B$2:$B$902317,"=0000013")
This returns the data, but I have to change the "<=24" every week to the date that I want up to.
I have tried =eomonth(today(),-2+1) but that is not allowed as the Criteria.
SUMIFS - Cannot Use The Same Criteria Range?
Dec 23, 2013Following 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.
Use The Sumifs Function To Add A Column?
Jan 7, 2014 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
=SUMIFS(Sheet1!K:K,Sheet1!B:B,">="&I17,Sheet1!B:B,"<="&J17,Sheet1!W:W,"="&M17)
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)
Sumifs With Array Sum Range?
Feb 7, 2014Attached file should be self-explanatory.
I want to be able to set sum range using lookup or match.. For example if I change B2 value to Nov-13, I want it to sum column K.
Using SUMIFS When Text Is Involved
Feb 25, 2014I'm having a complex issue that I've tried solving with =SUMPRODUCT(SUMIFS( and regular old =SUMIFS(, but all to no avail. Here is the issue at hand:
I need to look up three criteria, one of which is a text column.
Column A - Qualifier, is this a qualified sale (yes or no)?
Column B - Salesperson, who had the sale (Jacob, Charles, etc)
Column C - Date, when did this happen (2.2.14, 2.14.14, etc)
I need these three criteria to return a sum of Column A - how many are a yes. The formula that typically works for me for such a big sum, when only numbers are involved for the summing, is:
=SUMIFS(A:A,B:B,"Salesperson name",C:C,"Coordinating date")
I've uploaded how the final product should look across.
Is MEDIAN And Nested SUMIFS Possible?
Aug 1, 2014I have a nifty formula that conditionally tots lots of figures up, and conditionally tots up another set of figures up and divides them to create a percentage
=SUMIFS(INDEX(MATRIX!$S$1:$BC$5933,0,MATCH($D$2&$D$3,MATRIX!$S$1:$BC$1&
MATRIX!$S$2:$BC$2,0)),MATRIX!$N$1:$N$5933,$H12,MATRIX!$L$1:$L$5933,$D$5)
/SUMIFS(INDEX(MATRIX!$S$1:$BC$5933,0,MATCH($D$2&$D$3,MATRIX!$S$1:$BC$1&
MATRIX!$S$2:$BC$2,0)),MATRIX!$L$1:$L$5933,$D$5)
There are 4 conditions in the first part of the formula and 3 conditions in the second one. A division and a neat little percentage.
What I would like to do is add a median to this.....
Essentially I want the formula to 1. Calculate the same as above (as I need the median to be based on the percentages) and then there should be 18 figures that I would like to median
Is a nested sumif and a median possible?
Sumifs Formula Returning Zero
Sep 21, 2009=AVERAGEIFS($D$106:$D$2825,$D$106:$D$2825,">"&$I117,$D$106:$D$2825,"<"&$J117)
Returns = 0
But on a copy of the same worksheet, the same formula returns the correct value. Is there something wrong with my formula cotext?
Sumifs Date Function
Nov 20, 2009Is 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?!
View 5 Replies View RelatedSUMIFS With Criteria Range?
Dec 19, 2012I 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).
SUMIFS Using Arrays As Criteria?
Feb 28, 2014The 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