Excel Formulas

Distribute values evenly in range

=(B$21-$B$1)/(21-1)+B1

How can i make calculations on specific rows?

=(OFFSET($B$2,COLUMN()-COLUMN($D1),0)-$B3)+(OFFSET($C$2,COLUMN()-COLUMN($D1),0)-$C3)

Calculating Total in excel with just % values

=(SUMPRODUCT(B4:B5,1/(1+C4:C5))-B6)/SUMPRODUCT(B4:B5,1/(1+C4:C5))

Find if any of a list of substrings is present in a string

=--(SUMPRODUCT(--ISNUMBER(SEARCH(F$2:INDEX(F:F,COUNTA(F:F)),$A2)))>0)

Formula to retrieve multiple column numbers

=AGGREGATE(15,6,1/($1:$1="ThisHeader")*COLUMN($1:$1),ROW(INDEX($A:$A,1):INDEX($A:$A,COUNTIF($1:$1,"ThisHeader"))))

Highlighting Duplicate number 20 in excel only when they are adjacent

=AND(A2=20,OR(A1=20,A3=20))

Excluding header in conditional formatting function which is using TODAY()

=AND(ISNUMBER(F1);F1>=TODAY())

Find the average but without using range

=AVERAGE(J28,O28,T28,Y28,AD28,AI28,AN28,AS28,AX28)

how to calculate average ages between two dates in excel

=AVERAGEIFS(AGECOL,DATECOL,">="&B5,DATECOL,"<="&C5)

Multi filter in Excel Formula

=COUNT(UNIQUE(FILTER(MATCH($B$2:$B$14,$B$2:$B$14,0),($A$2:$A$14=$E2)*($C$2:$C$14=F$1),"a")))

COUNTIF(S) in two column and get the unique count

=COUNTA(UNIQUE(FILTER(A:A,(B:B="X")+(C:C="X"))))

Error - COUNTIF with Text containing special characters

=COUNTIF(A$1:A$2,SUBSTITUTE(A1,"*","~*"))

How to Sum NA (text NA) values in excel with numbers by treating NA as 1

=COUNTIF(A3:C3,"NA")+SUM(A3:C3)

Excel formula SUMIF problem with column counting

=COUNTIF(O2:O252;"y")*80 + SUM(O2:O252)

How to use automatically update formula without dragging

=COUNTIF(Sheet1!$A$2:$B$15,B1#)

Conditionally flag fields for each group (2 columns) based on top N values

=COUNTIFS($A$2:$A$10;A2;$B$2:$B$10;B2;$C$2:$C$10;">"&C2)+1

Count for every 'x' that falls into an interval within a given time range?

=COUNTIFS(B:B,"<="&E2,C:C,">="&E2)

Searching a value in excel using formula

=COUNTIFS(E$3:E$8,B3,F$3:F$8,C3)>0

VALUE error when attempting to format a date and time cell

=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIMEVALUE(RIGHT(A1,8))

Excel: Find particular (text) value in range

=IF((COUNTIF($E$2:$E$11,A2)),"True","False")

How to use an IF statement to pull cells only with >= XXXX

=IF(--LEFT(A2,SEARCH("x",A2&"x")-1)>1400,A2,"No")

Excel vlookup get the highest value

=IF(A6=1,$C$3,MAX(IF(A6-1=$A$6:$A$14,VLOOKUP(A6-1,$A$6:$D$14,3)+$B$6:$B$14))) CSE Formula

Calculating the week number of a year in Excel

=IF(AND(A1>=DATE(YEAR(A1),1,1),A1<DATE(YEAR(A1),1,8)-WEEKDAY(DATE(YEAR(A1),1,6))),53,WEEKNUM(A1,2))

Excel Formula too many arguments

=IF(COUNTIF(A2:H2,"Accepted")+COUNTIF(A2:H2,"N/A")+COUNTIF(A2:H2,">"&TODAY())=8,"Verified","Pending")

Can I use hlookup or something similar to keep looking for specific text?

=IF(COUNTIFS($E$1:$H$1,$A2,$E$2:$H$2,B$1),"X","")

How do I automatically subtract time data of previous row from current row in Excel?

=IF(ISNUMBER(A1),A2-A1,"")

Summing Text strings and numbers togather in excel

=IF(PRODUCT(D2:E2)=1,(5-COUNTIF(A2:D2,0)),"X")

How to get the exact value using excel function or VBA in Two Columns

=IF(SUM($H$9:H9)>=3000, 0, MIN(3000 - SUM($H$9:H9), G10*$H$6))

Excel formula to return value if two conditions are matched

=IF(SUMIFS($I$2:$I$7,$G$2:$G$7,$A2,$H$2:$H$7,B$1)=0,"",SUMIFS($I$2:$I$7,$G$2:$G$7,$A2,$H$2:$H$7,B$1))

Formula to give accurate Quarter then Year

=IF(YEAR($D$1)=B4,"Q" & INT((C4-1)/3)+1 & " " & B4,"Y"&B4)

if statement not working properly on string data: Excel

=IFERROR(--(E2*1>=0.9),E2)

maxifs returning 00/00/1900 as date for blank date

=IFERROR(1/(1/maxifs('Sheet1'!B:B,'Sheet1'!$A:$A,"="&A2)),"")

Compute the upcoming task date until the system date is crossed using excel or sheets formula

=IFERROR(IF(NOW()<=E7, E7, eomonth(E7,(rounddown(datedif(E7,NOW(),"M")/D7)+1)*D7-1) +C7 ))

How can I pull any cells with a value greater than a number?

=IFERROR(IF(OR(VALUE(LEFT(A2,SEARCH("X",A2)-1))>=1400,VALUE(RIGHT(A2,LEN(A2)-SEARCH("X",A2)))>=1400),A2,"No"),"")

Reference cells with no gaps to fill table with gaps

=IFERROR(INDEX($B$1:$B$4,MATCH(TRUE,MONTH(A10)=MONTH($A$1:$A$4),0)),"")

Assign a category to a text

=IFERROR(INDEX($B$4:$B$6,MATCH(1,(COUNTIF($D4,"*"&$B$4:$B$6&"*")),0)),"")

index match double criteria not working, what is the problem?

=IFERROR(INDEX('Stock Data'!$A$1:$F$63727,MATCH(1,INDEX(($I15='Stock Data'!$B$1:$B$63727)*(J$5='Stock Data'!$D$1:$D$63727&""),0),0),6),"")

Excel: dynamically calculate range next to a searched up cell

=IFERROR(INDEX(INDEX(C:C,MATCH(F4,A:A,0)):C$1000,MATCH(G4,INDEX(B:B,MATCH(F4,A:A,0)):B$1000,0)),"")

Excel: Compare two values to another two values to return the adjacent value

=IFERROR(LOOKUP(2,1/(($A$2:$A$22=$E2)*($B$2:$B$22=F$1)),$C$2:$C$22),"")

Excel Formula calculation based on pervious matching criteria

=IFERROR(LOOKUP(2,1/((A$1:A1="Y")*(A2="Y")),B$1:B1),"")

Is there a way to return value from an array if it is between two numbers?

=IFERROR(LOOKUP(2,1/((A1>=$D$1:$D$5)*(A1<=$E$1:$E$5)),$F$1:$F$5),"")

Conditional Vlookup 3 search terms one table

=IFERROR(VLOOKUP(A2,F:G,2,0),IFERROR(VLOOKUP(B2,F:G,2,0),IFERROR(VLOOKUP(C2,F:G,2,0),"")))

Selecting a cell in a range

=INDEX($G$2:$I$4,MATCH(B2,$F$2:$F$4,1),MATCH(C2,$G$1:$I$1,1))

Excel Formula INDEX MATCH with 2 criteria

=INDEX('[World Population.xlsx]Sheet1'!$B$3:$BX$291,MATCH($B4,'[World Population.xlsx]Sheet1'!$A$3:$A$291,0),MATCH("1999", '[World Population.xlsx]Sheet1'!$B$2:$BX$2,0))

Alternatives to interpolate three dimensional data

=INDEX(A:H;MATCH(6,8;A:A;0)+MATCH(25;B:B;0)-2;MATCH(2;2:2,0))

Lookup Excel Column, Row

=INDEX(B:B,MATCH(A12,INDEX(A1:G8,0,MATCH(A11,1:1,0)),0))

How can I use offset function in Excel by adding just the number of column?

=INDEX(C2:G2,B2)

Showing correct value based on three lookup variables

=INDEX(C4:S32,MATCH(V4,B4:B32,0),IFERROR(MATCH(V5,C2:S2,0)+MATCH(V6,D3:F3,0)-1,1))

Return cell's value based on its date range in Excel

=INDEX(Sheet2!D:D,MATCH(1,(B2>Sheet2!B:B)*(B2<Sheet2!C:C)*(A2=Sheet2!A:A),0))

Count how many matches in the same row across 3 columns

=INDEX({0,2,3},MAX(COUNTIF(B1:D1,B1:D1)))

How to load column dynamically in Excel given the row

=INDIRECT("B"&E11)

Excel formula for recognizing months as a part of a specific year

=INT((G1-1)/12)+1

Extract words before a specific pattern

=LEFT(A2,SEARCH("_x_D?_m*",A2)-1)

Get the first non-matching criteria from a range

=LOOKUP(1,1/(B1:B6<>"O.K."),B1:B6)

How do I calculate the Time Difference in Excel when a filter is active?

=LOOKUP(1,1/(D3:D$100=D2),A3:A$100)-A2

EXCEL: Index Match Max date Multiple Criteria

=LOOKUP(2,1/(A:A=A2),C:C)

Excel Row Number with duplicated values

=MATCH(1,(A2=Sheet2!A:A)*(B2=Sheet2!B:B)*(C2=Sheet2!C:C),0) CSE Formula

Find row number of sum result in range

=MATCH(TRUE,--SUBTOTAL(9,OFFSET(A1:A4,,,ROW(A1:A4)))>=B1,0)

How to extract text between two dots

=MID(A1, SEARCH(".", A1)+1, LEN(A1)-SEARCH(".", A1)-1)

extract text from between two words

=MID(A1,FIND("dimenzija",A1),FIND(";",A1&";",FIND("dimenzija",A1))-FIND("dimenzija",A1))

How to extract the final slug of a url without trailing slash in excel?

=MID(C2,FIND("/cat/",C2)+5,LEN(C2)-4-FIND("/cat/",C2)-(RIGHT(C2)="/"))

Excel MIN() if adjacent cell matches previous row's value

=MIN(IF(C2:C8<>C1:C7,A2:A8,"NULL")) CSE formula

Sum of hours within two times

=MIN(TIME(20,0,0),B2-INT(B2))-MAX(TIME(6,0,0),A2-INT(A2))

How to obtain minimum into the range when values >0?

=MINIFS(D$2:D$1000;B$2:B$1000;J11;D$2:D$1000;">0")

How to find index of cell value in range without using an embedded IF Statement

=OFFSET(INDEX(B5:K5,MATCH(TRUE,B5:K5>B1,0)),-2,0) CSE Formula

Excel: How to find the row dynamically with an Index Match formula

=SMALL(INDEX($B$2:$E$6,0,MATCH(LEFT(M4,FIND("_",M4)-1),$B$1:$E$1,0)),M2)

Return all unique values from column, and sort them alphabetically

=SORT(UNIQUE(A2:A11))

Excel Split Date Range Grand Total into Daily Totals

=SUM((A$2:A$8=I2)*TEXT(IF(C$2:C$8<(H2+31/24),C$2:C$8,H2+31/24)-IF(B$2:B$8>(H2+7/24),B$2:B$8,H2+7/24),"general;\0")*24) CSE formula

Sorting and indexing multiple conditions in Excel

=SUM((INDEX(A2:H2,{1,3,5,7})="Y")*(INDEX(A2:H2,{2,4,6,8})="Y"))

Count number of records by year between two ranges in excel and dealing with null dates

=SUM((YEAR($B$2:$B$10)<=E2)*(YEAR(IF(ISNUMBER($C$2:$C$10),$C$2:$C$10,TODAY()))>E2)) CSE formula

Counting grouped cells excel

=SUM(--(FREQUENCY(IF(YEAR(C$2:C$12)=F1,A$2:A$12),A$2:A$12)>0)) CSE Formula

Excel Sumproduct ignore text issues

=SUM(D7:D19*IFERROR(1/(1+E7:E19),0))

Excel formula: How to refer a cell by its column name

=SUM(INDEX(A2:L2,MATCH(B6,A1:L1,0)):INDEX(A2:L2,MATCH(C6,A1:L1,0)))

Excel - average of range doing vlookup of that range inside the formula

=SUM(VLOOKUP(A2:A8,C2:D6,2,0))/8

IF Logic test - Excel

=SUMIF(C:C,"IN",B:B)

Excel IF statement with SUM in it

=SUMIF(C:C,"Ready for unload",D:D)+SUMIF(C:C,"Unloading in progress",D:D)

How do I sum a table by row and by column in excel?

=SUMIFS(INDEX(B:D,MATCH(C7,A:A,0),0),B1:D1,"<"&C8)

SUMIFS with horizontal range and column header Excel formulas

=SUMPRODUCT(($A:$A=F1)*$B:$D)

Count by column an one condition in one of 3 other columns

=SUMPRODUCT((A1:A100>50)*((B1:B100>20)+(C1:C100>20)+(D1:D100>20)>0))

Excel counting cells based on complex criteria

=SUMPRODUCT((B2:F3>=0)*(B1:F1<=4)*(A2:A3="name1"),B2:F3)

Excel count non null and non empty strings values

=SUMPRODUCT((C2:C6<>"")*(D2:D6<>""))

Sum cells in a row from columns with numbers given in a range

=SUMPRODUCT((COLUMN($B8:$G8)=H$2:H$5+1)*$B8:$G8)

Determining if date is between start/end dates, and averaging days since start

=SUMPRODUCT((F2-B$2:B$10)*(B$2:B$10<=F2)*(C$2:C$10>=F2))

Row number and partition by in MS excel

=SUMPRODUCT(--($A$2:$A2&$B$2:$B2=A2&B2))

COUNTIFS - How to count values that match up criteria of LEFT(Searched_Column,2) > 6

=SUMPRODUCT(--((--LEFT(A1:A7,2))>6))

Excel - Sum/Count of boolean values

=SUMPRODUCT(--(B2:D2=$B$7:$D$7))

Excel - SUMIF - Do not return correct value

=SUMPRODUCT(--(DATA!$A$3:$A$7996=A3),DATA!$B$3:$B$7996)

Array formula to subtract two named ranges, and count the values that are <15 and greater than 0

=SUMPRODUCT(--(r_hire_date-r_birth_year>0),--(r_hire_date-r_birth_year<15))

Excel: How to take a weighted average where blank values cause proportional weighting shifts

=SUMPRODUCT(B2:F2*B3:F3)+SUMPRODUCT(SUMPRODUCT((B3:F3="")*B2:F2)*B3:F3)/COUNT(B3:F3)

Excel - How to sum rows in table A if they meet a condition in table B

=SUMPRODUCT(ISNUMBER(SEARCH(LEFT(B5),O$5:O$9))*I$5:N$9)

How can I aggregate values in one column based on the lookup values in another sheet?

=SUMPRODUCT(SUMIFS($B$3:$B$9,$A$3:$A$9,$D$3:$D$9)*($E$3:$E$9=G2))

Trying to find a way to SUM a field based on criteria looked up in different table in excel

=SUMPRODUCT(SUMIFS(B:B,A:A,D2:D4)*(E2:E4="fruit"))

Excel: How to sum dataset with 3 levels of data

=SUMPRODUCT(SUMIFS(E:E,D:D,B6:B8)*(C6:C8=G3))

Reverse vlookup and concatenate

=TEXTJOIN(",",,IF(B1:B8=A18,A1:A8,"")) CSE Formula

Excel TEXTJOIN of INDEX MATCH unique values

=TEXTJOIN(",",TRUE,UNIQUE(FILTER($F$2:$F$4,ISNUMBER(MATCH($E$2:$E$4,A2:C2,0)),"")))

Finding the MODE in Excel with VLOOKUP?

=VLOOKUP(MAX(B23:B26),CHOOSE({1,2},B23:B26,A23:A26),2,0)

How to use WORKDAY with month and not days

=WORKDAY(A1,NETWORKDAYS(A1,EDATE(A1,1),1))

Excel formula to extract a number and word combination from a string

IFERROR(TRIM(MID(A3,FIND("min",A3,1)-3,10)),TRIM(MID(A3,FIND("sec",A3,1)-3,10)))

Sum with Datedif Formula

VLOOKUP(VALUE(LEFT(C4;1));$U$22:$Z$25;7;FALSE

If And

use: =IF(AND(C14="",G14=""),"",IF(G14<>"","Closed","Open"))