regex,string,google-spreadsheet,match,array-formulas

I think what you want may be array_constrain but for your example I can only at present offer you two formulae (one for each side of the comma): =Array_constrain(arrayformula(left(E2:E,find(",",E2:E)-1)),match("xxx",E:E)-1,1) =Array_constrain(arrayformula(mid(E2:E,find(",",E2:E)+1,len(E2:E))),match("xxx",E:E)-1,1) ...

google-spreadsheet,array-formulas

In array formulas instead OR() use ADD() or the + operator =ArrayFormula(If((E3:E="NULL")+ISBLANK(E3:E),"Unknown",E3:E))...

You nearly had it! However, in an array formula, you cannot replicate an "AND" construction so straightforwardly, in essence since the return from the AND function is always a single value, never an array. Hence, your attempt: =MAX(IF(AND(A1:A8="A",B1:B8<>""),B1:B8)) would initially correctly resolve to (using the values you posted): =MAX(IF(AND({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE},{TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE}),B1:B8)) though...

excel,vba,excel-vba,excel-formula,array-formulas

Your looking for the FormulaArray property that you can set for a cell like so: Range("A1").FormulaArray = "=INDEX(subset!R1C1:R2472C10,MATCH(1,(RC1=subset!C1)(RC2=subset!C2)(RC5=subset!C5)*(RC6=subset!C6),0),10)" See the documentation here: http://msdn.microsoft.com/en-us/library/office/ff837104%28v=office.15%29.aspx...

excel,excel-formula,formulas,array-formulas

Try entering the formula as an array formula (Ctrl+Shift+Enter), that should work.

If your new data is in columns A-C and original data is also columns A-C in Sheet1, then enter this formula in B2: =SUMIFS(Sheet1!C:C,Sheet1!A:A,A2,Sheet1!B:B,MAX(IF((Sheet1!A:A=A2)*(Sheet1!B:B<=B2),Sheet1!B:B,""))) This formula works well if you have only numbers in your 3rd column, but would be more complicated to make it working on text too....

javascript,google-spreadsheet,array-formulas

I think I was just being sort of dumb in regards to needing this. Because this is already being done from a script, I can simply manually add all the CONTINUE() formulas to cells all the way down and to the right of the "root" cell (the one with the...

excel,if-statement,average,array-formulas

Update with Array Function First Attempt: So at first I had thought you could just add a second condition to the Array Function like this: =AVERAGE(IF(AND($C$2:$C$9={"LA","SF","POR","SEA"},$B$2:$B$9={"North"}),$D$2:$D$9)) Where the AND would require each item it be true, but this appears not to have worked... and array functions are tricky beasts to...

excel,if-statement,excel-formula,average,array-formulas

EDIT: Corrected the formula to include "YES" as a criteria: The following should be entered with ctrl-shift-enter =STDEV(IF((A1:A9="YES")*(B1:B9={"john","paul","ringo","george"}),C1:C9)) ...

excel,if-statement,max,excel-2010,array-formulas

Please try: =MAX(IF(A:A=A1,B:B)) entered with Ctrl+Shift+Enter and copied down to suit. I'm afraid this could be quite slow. I did not limit the range because I assumed 107,000 rows was an approximation. However this is slow even for 1,000 rows, so for emphasis I repeat part of @XOR LX's comment:...

excel,excel-formula,array-formulas

Your problem that it is "summing twice" in this formula ={SUMPRODUCT(C,((A<=20)+(C<=20)))} is due to addition turning first TRUE plus the second TRUE into 2. It is not actually summing twice, because for any row, if only one condition is met, it would count that row only once. The solution is...

google-spreadsheet,array-formulas

Maybe somehting like this: =ArrayFormula(if(row(A:A) <= max(if(not(isblank(A:A)), row(A:A))),vlookup(row(A:A),filter({row(A:A),A:A},len(A:A)),2),)) ...

excel,excel-formula,excel-2010,array-formulas,consolidation

Maybe try: =COUNTIFS(Sheet1!A:A,A2,Sheet1!B:B,TRUE)>0 ...

excel,google-spreadsheet,excel-formula,array-formulas

In a google spreadsheet you may want to try: =ArrayFormula(iferror(SORT(ROW(A1:A),SORT(ROW(A1:A),A1:A,1),1)-MATCH(A1:A,SORT(A1:A),0)-ROW()+2)) Example sheet...

indexing,google-spreadsheet,match,lookup,array-formulas

In F6: =ArrayFormula(IF(LEN(D6:D),IFERROR(VLOOKUP(D6:D,{E6:E,D6:D},2,0),"No Referrals"),)) and in G6: =ArrayFormula(IF(LEN(D6:D),IFERROR(VLOOKUP(D6:D,FILTER({E6:E,D6:D},(MATCH(E6:E,E6:E,0)+ROW(E6)-1)<ROW(E6:E)),2,0),"No Referrals"),))...

excel,date,excel-formula,worksheet-function,array-formulas

Following 4 named ranges for simplicity in code: "Dividends": DividendDates (column A); DividendsPaid (column B) "AdjClose": StockDate (column A); StockPrice (column B) try (in column C in "Dividends": {=INDEX(StockPrice;MATCH(MAX(IF((StockDates<=A1);StockDates));StockDates;0))} Assuming that the dividend date for which you want to find the adjusted stock price is in cell A1. And copy...

excel,excel-formula,worksheet-function,countif,array-formulas

Just wanted to check if @Byron answer in the comment works. It works, the result is 7. {=SUMPRODUCT(--(A2:A17>128),--(A2:A17<136), --(NETWORKDAYS(IF(ISBLANK(C2:C17),B2:B17,C2:C17)+0,TODAY()+0,0)<=30))} ...

You can use this array formula: =SUM(IFERROR(([@Code]=Tracker[Code])/COUNTIFS(Tracker[Error Message],Tracker[Error Message],Tracker[Code],[@Code]),0)) The basic approach is to divide 1 buy the number of times a certain Error Message appears and then SUM those... This will give you the unique number of messages. We add in the initial [@Code]=Tracker[Code] so that we get a...

google-spreadsheet,fill,autofill,array-formulas

Let's say you want to do a VLOOKUP on col A (in arrayformula): =arrayformula(if(len(A2:A), vlookup(A2:A,'Sheet 1'!A:L,12,false),)) Make sure there are no other values or formulas in the column where you use this formula. Example sheet...

excel,excel-formula,array-formulas

You can add additional conditions at the start of the FREQUENCY function, e.g. for how many different patients had a fall, use this: =SUM(IF(FREQUENCY(IF('Event Tracker'!I5:I1502="Fall",IF('Event Tracker'!A5:A1502&'Event Tracker'!B5:B1502<>"",MATCH('Event Tracker'!A5:A1502&"@"&'Event Tracker'!B5:B1502,'Event Tracker'!A5:A1502&"@"&'Event Tracker'!B5:B1502,0))),ROW('Event Tracker'!A5:A1502)-ROW(A5)+1)>0,1)) To add extra conditions you can just keep adding IFs in the same place......but don't forget to add...

google-spreadsheet,array-formulas

Does this formula work as you want in cell C1: ={MAX(ArrayFormula(IFERROR(B2:B*D2:D/E2:E)));ArrayFormula(IFERROR(B2:B*D2:D/E2:E))}...

google-spreadsheet,array-formulas

In C2 try: =ArrayFormula(if(len(B2:B); if(regexmatch(A2:A; "(B-|D-)"); A2:A&"-"&B2:B;);)) This will reference the colA and output the concatenation of col A and colB if col A starts with either B- or D. NOTE: you will have to erase all formulas you currently have in col C. Hope that helps ? EDIT: not...

javascript,google-apps-script,google-spreadsheet,unique,array-formulas

Considering mainly your last sentence: =ArrayFormula(sort(unique({B1:B;D1:D;G1:G}))) ...

excel,excel-formula,array-formulas

It would be a lot simpler if the full name were also in two separate cells. You could use a simple SUMIFS. But for the problem you present, if I understand it, given the following: First is the named range containing the first names Last is the named range containing...

excel,excel-formula,array-formulas

Thankfully the function FORMULATEXT does just that in the 2013 version of Excel. It sounds like you want to determine if there is or is not a formula vice return the text so I would recommend adding an IFNA function as well like the example below. =IFNA(FORMULATEXT(A1),"No Formula") You could...

google-spreadsheet,sumifs,array-formulas

The solution that I ended up using was this: Credit due to 2n9 from google forums. Here is the link =arrayformula(sumif(B3:B8&C3:C8,F3:F8&"A",A3:A8)) There were some other very good answers there using queries from Jean-Pierre Verhulst: =query(A2:C8, "select B, sum(A) group by B pivot C") =query(query(A2:C8, "select B, sum(A) group by B...

excel,excel-formula,worksheet-function,median,array-formulas

Maybe: =MEDIAN(IF(A:A="A",B:B)) entered CSE....

If there is no "-" in a given string then the FIND function will return a #VALUE! error for that string. In general, there are two ways to resolve such issues. One is to include an error trap; the other, slightly longer though perhaps nevertheless preferable (IFERROR clauses are generally...

I believe that, in such clauses, Excel gives precedence to the artificial expansion of the reference to match that of the worksheet range selected (which it will always do by filling with #N/As) over first resolving the IF clause over the array. So, whereas "normally" (e.g in a single-cell array...

google-spreadsheet,array-formulas

below code should provide you with a custom function that kinda behaves like an arrayformula. You can reference a whole column with it. Click here for an example sheet (Cell C2 holds the custom formula). The code used is this: function CF(range) { var out = []; range.reduce(function (a, b)...

excel,if-statement,excel-formula,worksheet-function,array-formulas

Please try: =MAX(IF(A:A=2,B:B)) entered with Ctrl+Shift+Enter....

google-spreadsheet,array-formulas

The ArrayFormula plus OR will evaluate to TRUE if any of the cells in the range is in the codition, to overcome this remove the OR, and add an IF for every condition, as such: =ARRAYFORMULA(sum(IF(left(A1:A10;3)="GTR";VALUE(right(A1:A10;1));0);IF(left(A1:A10;3)="DBT";VALUE(right(A1:A10;1));0))) ...