Non Volatile Replacement For INDIRECT In SUMIFS Formula

Dec 4, 2013

Okay so I'm trying to us the SUMIFS formula to add values on a different sheet based on criteria on that sheet but I want to use a text string on the sheet with the SUMIFS formula to reference the sheet with the data on it.

My original formula was:

=SUMIFS(INDIRECT("'"&A2&"'!K4:K500"),INDIRECT("'"&$A$2&"'!B4:B500"),"=LEFT(AA1,2)",LEFT(INDIRECT("'"&$A$2&"'!C4:C500"),3),"=MID(AA1,7,3)")

However I kept getting an error and excel said that INDIRECT("'"&A2&"'!K4:K500") was volatile, so I tried to replace it with INDEX and my formula was:

=SUMIFS(INDEX(A2:A2,1)&"'!K4:K500",INDEX(A2:A2,1)&"'!B4:B500",LEFT(AA1,2),LEFT(INDEX(A2:A2,1)&"'!C5:C500",3),MID(AA1,7,3))

However INDEX(A2:A2,1)&"'!K4:K500" returns the value "1251062 EP.EL+CB-A10'!K4:K500" which wont calculate properly because of the quotation marks but excel is auto inserting them.

View 3 Replies


ADVERTISEMENT

Replacement For Using Indirect (Volatile)

Oct 30, 2009

What can I use to replace the portion in red because it is volatile?

=IF(A3="N","",RANK(B3,$B$2:INDIRECT("$B$17"),0)+COUNTIF($B$2:B3,B3)-1)

View 6 Replies View Related

Indirect To Non-volatile

Feb 26, 2010

why Microsoft have not made the indirect function non-volatile. In 1997, they changed the index function to non-volatile.

View 9 Replies View Related

Non Volatile Custom Function Acting Volatile

Oct 15, 2007

I am using a linear interpolation UDF that recalculates every time a change is made to any cell in the workbook, whether that cell is pertinent to the calculation or not. Is this correct behavior?

View 9 Replies View Related

Volatile References In Formula

Mar 4, 2010

I have a worksheet that will be referencing data from a 2nd open workbook.
Although the 2nd workbook will contain common worksheet names (eg stats), the file name will be volatile.

Consider the formula in wb ws1 cell A1 used to copy a value from wb2 (39961(28-May-09).xls) ws1 (stats) cell B1 if workbook 2's name was static.

='[39961(28-May-09).xls]Stats'!$B$1

In my case, the reference file will always be different and must be substituted in the forumla above (red portion). How can I adapt my formulae recognize the available workbook. I have the file name available now in cell C4 of WB1.

View 9 Replies View Related

Volatile Formula Returning Date To Be Static

Sep 21, 2008

I've posted this question in another site http://en.allexperts.com/q/Excel-105...xExp_72253.htm , but i haven't gotten any answer. I'm attaching an example of the file to show an idea of what i'd like to do, please check first. As you can see, i have a Due Date and Priority columns. The Due Date column has a formula, that when i choose the Priority level, it calculates the Due Date from the current date and auto populates in the cell. The problem is, when i open the file the next day, and insert another Priority level, it updates everything even the previous Due Date entry. This is the formula i have in place for the Due Date:

=(TODAY())+SMALL(IF(WEEKDAY((TODAY())-1+ROW(INDIRECT("1:"&(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))+ ROUND(30*((VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))/30),0))))<6,ROW(INDIRECT("1:"&(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))+ROUND(30*((VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))/30),0)))),(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0)))

Is there a way to make up a vb macro to be able to make the Due Date output static, without affecting the formula?

View 5 Replies View Related

Formula Involving Replacement.

Feb 20, 2009

I have several walking routes that are labeled A through H. Each walking route has a different distance. .18 miles, 1.45 miles, etc. What i would like to do is be able to enter what routes are walked in a day and have it display the milage in a total cell. Example......

View 4 Replies View Related

WORKDAY Formula Replacement W/o Use Of Analysis Add-In

Jun 17, 2008

My boss has made it clear I can't require our people to use the add-in.

I have searched under WORKDAY as well as under Barry Houdini, since someone said he has come up with plenty of replacements for the Analysis add-in... but I am not finding it.. can someone help?

Using the add-in, this is what I have:

myCell.FormulaR1C1 = "=WORKDAY(RC[1],-RC[2])"

View 9 Replies View Related

Add Total $ Value Formula Into Existing SUMIFS Formula?

Apr 3, 2014

In my attached spreadsheet example below, in the Rollup Data sheet, I have a formula (Cell: C17) that will give me the actual cost totals that has been invoiced on WBS Element (DWRRI-BW096-231) for a total of $35,004.81.

What I need the formula do is run a redundant check procedure on my new WBS Element (DWRRI-BW096-231) actual cost total formula in the Rollup Data sheet (Cell: C17) against/vs. what the SAP system provides in the raw SAP Excel export contained in the PTD-Actual Cost sheet for the WBS Element (DWRRI-BW096-231) actual cost total of $35,004.81 (Cell: F9) that's highlighted in the yellow row.

In the Rollup Data sheet (Cell: C18), I need this formula to perform a similar function the as the previous formula above it (Cell: C17), but it this formulas function (Cell: C18) would have a separate operation that would only look for and return the single/sole WBS Element (DWRRI-BW096-231) actual cost total contained in the PTD-Actual Cost sheet (Cell: F9).

In the Rollup Data sheet I could then compare my two WBS Element (DWRRI-BW096-231) actual costs totals, the first from the automated addition of all the individual matching WBS Element actual costs into an actual cost total (Cell: C17), the second (new formula I'm requesting) from the matching and return function of the single/sole WBS Element actual cost total (Cell: C18).

So, if the two separate WBS Element actual cost totals (Cells: C17 vs. C18) both equal $35,004.81, the formula in C17 is a success and has correctly identified and added all the actual costs contained in the PTD-Actual Cost sheet, but if the two separate WBS Element actual cost totals (Cells: C17 vs. C18) both do not equal $35,004.81, the formula in C17 failed for some reason and has not correctly identified and added all the required actual costs contained in the PTD-Actual Cost sheet.

How to Add Total $ Value Formula into an Existing SUMIFS Formula.xlsx

View 2 Replies View Related

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?

View 6 Replies View Related

Adding Formula To SUMIFS

Jul 18, 2014

I have a number of cells, with a rule as follows, but within each cell the company name and month vary, over 836 cells, so I cannot do a find and replace so wanted to know if there was a way of adding the following part to the rest of the formula within the 836 cells in one go, rather than tediously going through each cell and copying/pasting.

The part I would like to add to the various cells - 'Master Quote Sheet'!$H$18:$H$6021, "Won"

Existing formula (the varying parts within the different cells are the "02.2014" and "Company Name"
=SUMIFS('Master Quote Sheet'!$J$18:$J$6021, 'Master Quote Sheet'!$E$18:$E$6021, "Direct", 'Master Quote Sheet'!$A$18:$A$6021, ".02.2014", 'Master Quote Sheet'!$D$18:$D$6021, "Company Name")

View 14 Replies View Related

Sumifs Formula For Checking Dates

Jun 1, 2014

I have a SUMIFS formula that I am having trouble with. I am trying to check the following:

=SUMIFS(NFCU!$G$5:$G$298,NFCU!$E$5:$E$298,NFCU!$H$2,NFCU!$A$5:$A$298,"<e1")

The range NFCU!$A$5:$A$300 is a column of dates. The evaluation of "<e1" does not seem to evaluate. However, when I change "<e1" to a date value of 41760 it works. How can I reference a field rather than the raw value?

View 4 Replies View Related

Convert SUMIFS Formula In 2003

Oct 9, 2009

How would I convert the following formula into Excel 2003?

=SUMIFS($Q$7:$Q$5000,$O$7:$O$5000,"Cat",$P$7:$P$5000,"Dog")

View 2 Replies View Related

Formula Or VBA To Do Sumifs On Multiple Columns

Oct 31, 2013

On the attached work book I have 2 sheets on the total minutes sheets I need to run either a VBA or sumif formula that looks at all the Init columns and if it finds that name then it sums the duration cells corresponding with that name.

Picking log-Rev6.xlsm

View 4 Replies View Related

SUMIFS Formula With LEFT Function

Jan 16, 2009

have this formula..

=SUMIFS(Sheet2!$D$2847:$D$3065;Sheet2!$I$2847:$I$3065;$B$6;Sheet2!$G$2847:$G$3065;$C$6;Sheet2!$B$2847:$B$3065;A14)

D2847 - D3065

includes Numbers..

lets say.. i want every cell counted which has in these range the first two numbers..

every cell has..

5052
5053
5054
4060
4050

so i count the entries which has 50.. so in total it gives a result of 3. Not summing them, just count the entries that has the first two numbers "50" in every row..

The Red Markings can be replaced, the rest should stay.. it is needed due different criteria.....

View 6 Replies View Related

Convert SUMIFS Formula For 2003

Dec 30, 2009

Need convert the following formula so that it would work in Excel 2003: =SUMIFS($BB$4:$BB$500,$E$4:$E$500,"Abbeywood First",$L$4:$L$500,"Autumn"). I could play around with it for hours and get nowhere, so I'll just turn to the experts.

View 5 Replies View Related

Sumifs Formula With External Links?

Sep 12, 2012

I have some sumifs() functions in cells in a workbook that contain external links in them:

Code:
=SUMIFS('C: empForAuditor[Budget Employees 2013 (0055521).xlsm]Labor Summary'!$E$54:$E$88,'C: empForAuditor[Budget Employees 2013 (0055521).xlsm]Labor Summary'!$C$54:$C$88,$G50,'C: empForAuditor[Budget Employees 2013 (0055521).xlsm]Labor Summary'!$A$54:$A$88,H$49)

The formulas work fine if the external file is open. However if the linked file is not loaded and I load the file with the external links, as soon as the file with links is calculated, some of externally linked cells turn to "#VALUE" errors. I have a need to have only certain people see the employee file that is referenced above but the others need to update the file with the external links and not have errors in cells.

The really weird thing is that some cells yield the #VALUE error and some don't, even when both have SUMIFS() functions in them. Even cells with the Exact same formula in them will sometimes yield #VALUE and other times yield a real value.

View 3 Replies View Related

Sumifs Formula With Multiple Row And Column?

Mar 29, 2014

Want to apply sumifs formula with multiple rows and columns.

SOURCE DATA

Jan-13
Feb-13
Mar-13
Apr-13
Apr-13
Apr-13

[Code].....

View 4 Replies View Related

VBA - SUMIFS Formula Not Updating Cell When Filling Down?

Jul 27, 2012

I have the below formula working correctly. However the issue is when it pastes and fills down values its using H1 reference in the formula even when it goes to the next column. So basically when it goes to cell I2 it should update the sumifs formula to lookup I1 instead of H1 and so on and so forth for all columns till the loop stops.

VB:
Sheet1.Activate
Dim frmla As String
frmla = "=SUMIFS($C:$C,$A:$A,H$1,$B:$B,$G2)"
Range("H2").Activate
Do Until ActiveCell.Offset(-1, 0) = ""
ActiveCell = frmla
ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1).Activate
Range(ActiveCell, ActiveCell.End(xlUp)).Select
Selection.FillDown
ActiveCell.Offset(0, 1).Activate

View 1 Replies View Related

SUMIF OR SUMIFS Functions With Criteria As Formula?

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

Sumifs Formula To Add Up Weights Between A Defined Period?

Dec 11, 2013

I'm exporting data from an access database into an Excel document and want to use the Sumifs formula to add up weights between a defined period (we have to show tonnages on a weekly basis).

The data and calculations are on a separate worksheets. The data looks like this:

[URL]

I'm trying to sum the data in column E based on the date range in column A and my formula is

=SUMIFS('Site CR'!E$5:E$5000,'Site CR'!A$5:A$5000,">01/11/2013",'Site CR'!A$5:A$5000,A5)

For some reason, my formula always returns 0 and I can't see why.

[URL]

View 4 Replies View Related

Convert SUMPRODUCT Formula To Use SUMIFS Or COUNTIFS

May 1, 2012

I have a large metrics spreadsheet that has thousands of formulas using SUMPRODUCT. We are using Excel 2007/2010 now and I wondered if there was a way to convert these type of formulas to use the SUMIFS or COUNTIFS. I can do simple ones with 1 or 2 criteria where its AND, but then there is a combined OR and AND like this one has I cannot get them to work right, am I stuck with using SUMPRODUCT? My hope was to speed up the overall calculation speed. Note: The data spreadsheet 'Sheet2' contains 64555 rows, I write these formulas using VBA so that the range is finite to the actual data range is why you see 64555.

=SUMPRODUCT(--('Sheet2'!$J$3:$J$64555=$A6),--(('Sheet2'!$I$3:$I$64555="HOC")+('Sheet2'!$I$3:$I$64555="MGN")))

View 2 Replies View Related

Excel 2010 :: Sumifs Formula Not Working?

Jun 10, 2013

I have a workbook with multiple tabs that I update every other month with current data. I have one tab for data that I clear each time and copy the updated info into. (That info is copied from another excel spreadsheet, by the way.) On the second tab I have sumifs formulas set up to pull in certain data from the data tab. This setup has worked perfectly until today. I copied my data into the one tab but could not get the formulas to calculate. It looks like the formulas aren't picking up the data. I've checked to make sure the formulas are calculating automatically. I've retyped the formula. I've replaced the =. I tried naming the ranges I wanted the data to be picked up from. I've tried changing the format of the data - general, text, numbers, etc. I'm working in excel 2010

View 2 Replies View Related

Use SUMIFS Formula For Ranges Greater / Less Than Some Number?

Jul 5, 2013

How do I use the SUMIFS formula for ranges greater/less than some number?

In my spreadsheet I need to sum the values that are less or equal to January 1st, for example. How do I do it? Because this formula allow to find values equals to something.

View 5 Replies View Related

Sumifs - Formula To Return Sum Based On 2 Variable

Sep 17, 2013

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).

View 8 Replies View Related

Sumifs Formula Does Not Contain Multiple Criteria For Same Range

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

SUMIFs Using Multiple Criteria Or Array Formula

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

Using Cell As Array Reference In SUMIFS Formula?

Feb 11, 2014

I have a report for which I would like to set up a Quarterly summary section. There are 6 working teams listed in my table and I have created two cells with data validation to allow me to specify which team and for which quarter I would like to pull data. My intent is to have a formula in one cell which can extrapolate the quarterly team information based on the values in the data-validated cells.

My worksheet is set up so that:

The data table (tblD) contains metrics for all teams for the full yearTeam names appear in Column A of the table and the reporting month appears in Column BCell M10 has data validation so that the only choices are: Q1, Q2, Q3, or Q4Cells X1:X4 contain the quarterly values mentioned aboveCells Y1:Y4 contain array references, so that Cell X1 = Q1 and Cell Y1 = {"January", "February", "March"}Cell N10 contains a VLOOKUP which returns the appropriate array from Column Y based on value in Cell M10Cell L11 has data validation so that the only choices are the 6 team names

Now I was able to get quarterly information by manually typing the 3-month array into my formula like so:

Code:
=SUM(SUMIFS(tblD[TU5],tblD[Team],$L$11,tblD[Month],{"January","February","March"}))

BUT! When I try to use a formula which points to the VLOOKUP cell (N10)--thus allowing me to make the display dynamic--the result is always 0.

Code:
=SUM(SUMIFS(tblD[TU5],tblD[Team],$L$11,tblD[Month],$N$10))

While inspecting the formula in the formula bar, I noticed that the values listed in the array appeared with double quotes (i.e. "{""January"",""February"",""March""}"). I tried every permutation of typing the array in the lookup cells I could think of, but none would allow my formula to properly calculate the total I needed.

{"January", "February", "March"}
{January, February, March}
January, February, March

Although I don't often utilize arrays in my formulas, I do employ them from time to time. I'm hoping there's some factor I might have overlooked which keeps my formula from working. is there an issue preventing array references stored in cells from being used in formula?

View 6 Replies View Related

SUMIFS Formula To Work Out If A Rep Is Below Target On Selling Brands

Aug 10, 2009

i am trying to us the SUMIFS formula to work out if a rep is below target on selling brands.

I have two worksheets summary and data, Summary worksheet is where i am trying to do the sumif formula against a sales average taking the information from the data worksheet
I have account manager name, brand, sales, DSI
I tryed the below sumifs formula but its coming back as 0

Please could someone have a look and point out the error in my ways
=COUNTIFS(data!$A$2:$A$11,B17,data!F2:F11,"<B2)")
The first count if should count how many times the account manager appears in the brand and the second count is if his DSI on the data worksheet is higher that the target dsi on the summary sheet

View 3 Replies View Related

Excel 2007 :: SUMIFS - How To Specify Criteria That Looks At Type Of Formula

Jul 31, 2012

I'm trying to sum over a range (column A) based on values in another range (column B). Column B's cell values are calculated with a simple numeric formula that is either a subtraction or an addition of two cell references. I want to sum all the column A cells for which the corresponding column B cells are determined by a subtraction. Then separately, sum the cells for which the corresponding column B cells hold an addition. It seems that SUMIFS is the function to use, but how do I specify the sum criteria as "a subtraction" or "an addition"?

Windows 7, Excel 2007

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved