How can I write an Excel query that counts unique text data while also excluding data?
If you are sure that you always have at least one admin in your column, then you can count all distinct values in the column and then subtract one. Assuming your data fall in the range A1:A100, then this formula will give the desired answer =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))-1 If you are not...
Sumproduct with multiple criteria
What you could do is add an IF statement to your formula in column Z: IF(AND(W5=0,X5=0),0,SUMPRODUCT(--(ABS($Y$5:$Y$165)>ABS(Y5)))+1) This will make the value in column Z equal to 0 if both W and X are 0. Then you can easily filter these 0s out....
Sum row based on criteria across multiple columns
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...
unable to use MIN and IF function in excel
excel,excel-formula,excel-2010
The formula itself is ok, but you need to enter it as an array formula (press CTRL+SHIFT+ENTER when you entered it instead of simple ENTER)
How to count multiple values in one single cell using Pivot Tables in Excel?
excel,excel-formula,pivot-table
If you're okay with using helper columns, use text to columns to split up your third column into individual columns by using commas as the delimiter. Get everything in one column and then create your pivot table on the resulting data. EDIT: Select C2:C5 and go to the data tab...
Can Not Get My VLookUp In Excel To Return The Requested Data
excel-formula,vlookup,excel-2013
Try using the Index Match method. It's an alternative to Vlookup which doesn't require data to be sorted and can therefore be of more use. The typical structure of this method is (the text inside the asterisk will give the ranges specific to your sheet: =INDEX (**Column from which you...
Excel formula to check for duplicate words
I think this does the job (assuming you have your data in B1:F1):- =if(b1="",0, SUM(1-(ISERROR(FIND(MID("/"&B1&"/",FIND("|",SUBSTITUTE("/"&B1&"/","/","|",{1;2;3;4;5;6})),FIND("|",SUBSTITUTE("/"&B1&"/","/","|",{2;3;4;5;6;7}))-FIND("|",SUBSTITUTE("/"&B1&"/","/","|",{1;2;3;4;5;6}))+1),"/"&$A$1:A$1&"/"))))...
Get unique values in a column except values filtered from the original column - Excel Formula Only
I'm assuming you are using this formula in X13 copied down (or possibly Y12 copied across). Try using this revised formula: =IFERROR(INDEX($F$13:$F$1353, MATCH(1,(COUNTIF($X$12:X12, $F$13:$F$1353)=0)*SUBTOTAL(3,OFFSET($F$13,ROW($F$13:$F$1353)-ROW($F$13),0)), 0)),"") confirmed with CTRL+SHIFT+ENTER The additional SUBTOTAL(OFFSET part works to exclude values that are not visible due to filtering...
Ranking with subsets
There are a few related ways to do this, most involving SUMPRODUCT. If you don't like the solution below and would like to research other ways/explanations, try searching for "rankif". The function looks up the Class and Value columns and, for every value in those columns, returns a TRUE or...
Excel lookup value for multiple criteria and multiple columns
You're going to need to create a separate table that has all unique classifications in the rows and all dealers in the columns (same as yours, but with duplicate rows removed). Then, in each cell, take the average price for that classification*vendor combination. This can be done by using a...
Using AND and OR together on the IF function
You were close: =IF(AND(OR(D14="CerroVial",D14="Parinas"),E14="Low"),"8%","") All the elements of the AND need to be in the AND(...), just like for the OR, which you had done correctly....
How to create a dynamic table in Excel?
mysql,excel,excel-vba,excel-formula
If you want to repeat some text a number of times you can use a somewhat complicated formula to do it. It relies on there not being duplicate entries in the Fruits table and no entries with 0 count. Picture of ranges and results Formulas involved include a starter cell...
Excel with AND, IF, MAX
excel,if-statement,excel-formula,max,excel-2010
Well, I learned today that the And() function doesn't work inside of an array formula! In any case, try this: =MAX( IF(A3:A29>=$G$1, IF(A3:A29<=$G$2,$B$3:$B$29) ) ) (and don't forget to ctrl+shift+enter)...
Multiple search criteria within Excel Formula
It looks as if your data has merged cells. If you want to keep life simple, avoid merged cells. Your data has the same 1,2,3 sequence in each section x, y and z. I assume that these values will always be the same. With the data laid out like in...
INDEX/MATCH for closest value to a certain date
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...
INDEX&MATCH excel on rows and return SUMPRODUCT
You need a cell where you specify the month you want to look up. Give that cell the name "TheMonth". You need a cell where you specify the product you want to look up. Give that cell the name "TheProduct". Then, assuming your screenshot starts in cell A1, you can...
Changing series formula with VBA. Error Code 1004
excel,vba,charts,excel-formula,series
If you use .FormulaR1C1 = Replace(.FormulaR1C1 , "A", L) instead of .Formula = Replace(.Formula, "A", L) the '1004' goes away. Beats me why but it's alway worth trying when '.formula' results in an error. You'll still have to put some thought into the positioning of the charts though. Currently they...
Checking for different values associated with a similar descriptor entered multiple times in a list
I like Joseph's answer, however since I had worked this up, and in the event that a PivotTable (which could also be put on a separate sheet), is acceptable: 1) In the field list drag the ID numbers to the Row Labels, 2) Drag the Course Codes to the Column...
Excel INDEX isn't working with Table reference
excel,excel-formula,excel-2010
Are you sure that you should be using a ; as opposed to a ,? I get an error for that, and it directs me to $A$30 as you stated.
Excel SUMIFS formula on using operator inside criteria
You can use the following formula: =SUMPRODUCT(IF((A1:A5-B1:B5)>5,C1:C5)) this is an array formula, so press ctrl+shift+enter to calculate the formula. Hope this works for you....
Determine longest date range, from two date ranges - Excel
Using your provided example, this should work for you: =MAX($D2:$D3)-MIN($C2:$C3) ...
Auto fill in the blanks in Excel [duplicate]
Let's assume the data below: Step 1: select from cell A2 to A31 (all the data rows under Unit column) Step 2: Click on home tab and click Find & Select. Step 3: Select Go to Special. Step 4: Select Blanks and hit ok. Step 5: On the first cell,...
Search a string in multiple columns
The easiest way is probably to AND the results of a search in each column. This translates to "if not found in D and not found in E then output No". The logic is as follows: In column D | ISERROR(lookup in D) | In Column E | ISERROR(lookup in...
sumifs on excel linked sql server query with date
sql-server-2008-r2,excel-formula
Interestingly enough, Excel is unable to recognize SQL's data type dateas such. If you want Excel to recognize it as a date then you will have to convert it to datetime. So, if you convert the date field into datetime with a cast in the query then Excel will recognize...
Excel - Finding work days in a month between two dates
How about this: =NETWORKDAYS(MAX(A1,C1),MIN(B1,EOMONTH(C1,0))) It might be wise to expand it with some error checking like so: =IF(NETWORKDAYS(MAX(A1,C1),MIN(B1,EOMONTH(C1,0)))<0,0,NETWORKDAYS(MAX(A1,C1),MIN(B1,EOMONTH(C1,0)))) ...
Excel - How to concatenate 2 values to make a refference
excel,excel-formula,excel-2013
Commenting in an answer so as to add a picture. But your second formula also seems to work fine: Note that with a 2 in B3, the formula returns B from Sheet_2!B2...
Counting across rows for ###, then countif down columns for ids matching ###
By my reading, it seems as if you may be trying to do both tasks in a single formula. This may be possible with a more complex formula, but a straightforward solution is to simply add a column with activity counts to the raw data, and then count the instances...
Issue with Arrays/Match/Index
arrays,excel,if-statement,excel-formula,match
This should be as simple as multiplying in another condition that returns 1/0. You will get a 0 unless both conditions are met. {=LARGE( IF(Table_owssvr_1[HQ Name]=B1,1,0)* IF(Table_owssvr_1[Status]="Live -3",1,0)* IF(ISNUMBER(Table_owssvr_1[Live Date]),Table_owssvr_1[Live Date],0), 1)} ...
Data Reporting in Excel
Apply the golden rule of spreadsheet design: Data in one sheet, reports in other sheets. You are not doing yourself a favour by spreading the data entry over several sheets, one per month. This will make reporting very difficult. Put all the data in one sheet and add a column...
Return the list of colored cells based on certain conditions [closed]
excel,vba,excel-vba,excel-formula
I will suggest to loop every lines, and test every columns to your criteria (not NA and colored). If true, copy the colored cell. And add the header line. Here a start code working, need to be adapt to exactly reach your goal. Sub test() Dim aLine As Long Dim...
Program flow unexpected behavior
excel,vba,excel-vba,excel-formula,breakpoints
Without further information, it looks like you are changing the worksheet, which triggers the recalc. You can suspend recalculation and restore later. let savedCalcMode = Application.Calculation Application.Calculation = xlCalculationManual On Error Goto restore ... restore: Application.Calculation = savedCalcMode If you do this, try to restore even on errors otherwise you'll...
MS Excel, How to make the IF formula spit back the value of the cell if condition is FALSE?
The issue you are having is if the formula FIND does not find your search term "(" it will output the error value #VALUE!. To correct this please consider the following formula: =IF((IFERROR(FIND("(",A24)-1,FALSE)),LEFT(A24,FIND("(",A24)-1),A24) IFERROR function makes sure if the term is not found it will output FALSE. Hope this helps....
Nesting IF and OR statements
This should do it: =MAX(600*K6,I6*K6) Work out: 600*K6 (Will either be 75 or 150 depending on if K is 12.5% or 25%) K6*I6 Take the maximum value...
Excel Formula - IF & “X” > 0
You can use something like: =IF(C2-(C2*(G2/24))>0,C2-(C2*(G2/24)),0) to return a 0 result if the result would have otherwise been negative....
How to insert excel formula to cell in Report Builder 3.0
sql-server,excel,reporting-services,excel-formula,ssrs-2008-r2
Excel Formulas support as ended since SSRS 2008 (see Breaking Changes in SQL Server Reporting Services). No Formula Support in Excel In earlier versions of Reporting Services, there was limited support for translating expressions in RDL to Microsoft Excel formulas. In this release, when you export a report to Excel,...
How to set column conditionally in Excel?
If you want the name in column E and the worked hours in column F then set E1 to =IF(B1>0,A1,"") and set F1 to =IF(B1>0,B1,"") If you want the name and worked hours both in column E then set E1 to =IF(B1>0,CONCATENATE(A1," ",B1),"") and copy down the column. IF(condition, expression...
pulling and comparing dates in excel
excel,vba,if-statement,excel-formula,excel-match
If I understand this correctly, all we need to do is add an extra array criteria in your LARGE function. You already are getting the 2nd largest based on the customer ID. But we want the 2nd largest based on the customer Id and based on the scheduled date: =LARGE(('All...
Conditional formatting if a cell does not equal one of three values [closed]
excel,excel-formula,conditional-formatting
Remove any previous attempts at a Conditional Formatting Rule. Select A2:C5 and use Home ► Conditional Formatting ► New Rule. Choose Use a formula to determine which cells to format and supply the following for Format values where this formula is true: =not(or(A2=0, A2=50, A2=100)) Click Format and select...
Excel - list entities (items)
With data in column A, in B2 enter the array formula: =IFERROR(INDEX($A$2:$A$11,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$11),0)),"") In C2 enter: =COUNTIF($A$2:$A$11,B2) and copy down. Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. ...
Vloopup + INDEX and transpose
This can be accomplished in 2 parts, one formula for the Product, and one for finding the QTY. Product Formula So starting with the Product formula I was able to accomplish this by using: =IF(SUMPRODUCT(($A$5:$A$12=$E2)*($B$5:$B$12=INDEX($B$14:$B$18,MATCH(F$1,$A$14:$A$18,0))))>0,INDEX($B$14:$B$18,MATCH(F$1,$A$14:$A$18,0)),"") Explanation Assuming Product a falls under column F I will explain this using Saul This...
Formula to find value between date ranges and then use the figure next to it
Assuming the date is actually formatted like that and not stored as text, this should work: =IF( [Description] = "Diesel Oil", INDEX( TaxStatus!$B:$B, MATCH( DATE( YEAR( [Date] ), MONTH( [Date] ), 1 ), TaxStatus!$A:$A, 0 ) ) * [ValueEx.SalesTax], IF( OR( [Description] = "Furnace Oil", [Description] = "Lubricants" ), [ValueEx.SalesTax]...
Attempting to count cells in Excel using multiple criteria
You cannot use the MONTH function as a form of sub-function in a COUNTIFS function. You need to add an extra level of calculation with something like a SUMPRODUCT function. =SUMPRODUCT((MONTH(original!A2:A58)=1)*(original!D2:D58="East")) That should count the instances where the month of the date in the Original worksheet's column A is January...
Excel search and match two cells in the same row for multiple rows
You can accomplish this with =COUNTIFS(): =COUNTIFS(A1:A2000,"Pasta", D1:D2000,"Sauce") COUNTIFS will take multiple criteria and count the rows where those criteria are true. You can also use =SUMPRODUCTS if you have an older version of excel (2003 or earlier) =SUMPRODUCTS((A1:A2000="Pasta")*(D1:D2000="Sauce") * 1) This will do the exact same multiple criteria count....
Excels INDEX MATCH - Finding multiple matches
I would define the ranges more surgically when using array formulas. Given the small data set, this array formula gives the expected result: =MAX(IF(INDEX($A$2:$A$6&" "&$B$2:$B$6, 0) = A2&" "&B2, $D$2:$D$6)) - MIN(IF(INDEX($A$2:$A$6&" "&$B$2:$B$6, 0) = A2&" "&B2, $C$2:$C$6)) ...confirmed with Control+Shift+Enter to activate the array....
Finding a specific cell contanining value greater than the desired value
excel,indexing,excel-formula,vlookup
With 125 typed into D2 the first will return 101 from Range A; the second will return 500 from Range B. =INDEX(A:A, MATCH(D2, A:A)) =INDEX(B:B, MATCH(D2, A:A)) This method is known as a INDEX/MATCH function pair. When seeking approximate matches, the values to be examined must be in ascending...
Array Formula for text search
excel,excel-formula,excel-2013
You need to provide an extra layer of processing so that the formula does not halt at the first result of the ISNUMBER function. A SUM function wrapper should be sufficient. =SUM(--ISNUMBER(SEARCH(G2:G7, A1))) Array formulas need to be finalized with Ctrl+Shift+Enter↵. Any non-zero result will mean that at least one...
Is there a way to sum values to the right of a + symbol across many cells?
If you are using Excel 2013 you can use FORMULATEXT function to get the formula as text, then extract the number, e.g. with formulas in A2 down use this formula in B2 copied down =IFERROR(LOOKUP(10^9,RIGHT(FORMULATEXT(A2),{1,2,3,4,5,6,7,8,9})+0),0) That will extract numbers from the right of the text (up to 8 digits, excluding...
how to setup a excel chart with data points
First you need a table with just one header-row (create a new column for the Arbor .. Values). The value of this column is the name for the diagram line (instead of serie1 etc.)...
Dynamic formula or vba to offset a vertical list by one, and move the bottom of the list to the top?
excel,vba,excel-vba,excel-formula
If you want a formula that works regardless of what surrounds your data, you can get the same result using ROWS, COLUMNS, and MOD along with some absolute/relative ranges. Formula in cell C2 copied down and over. Probably looks a little better with a named range. If you want to...
Excel Function - Convert unicode to ascii
This sound an awful lot like you failed to import the data correctly. If you still have the original data, I suggest you import it again and use 65001: Unicode (UTF-8). If you do not have the original data, you can still trick Excel into importing your data again. I...
Creating dynamic dropdowns in Excel where values may appear in more than one column
You'd have to setup a second column for Field2 and for Field3. If you assume your dropdowns are located in cell A10 to C10 and your fields are in column A-C then you could go over to column D and make D1 =if($a$10=a1,b1,"") and D2 would be if(countif(d$1:d1,if($a$10=a2,b2,""))=1,"",if($a$10=a2,b2,"")). You could...
Converting Date Into a specific text format in excel
excel,excel-formula,excel-2010,excel-2013
You could simply turn around your first example and use the correct string: =TEXT(J3, "yyyyMM") Note, I used upper case M. Lower case m means minutes, while upper case M means month. The CONCATENATE is unnecessary. Please also note, that this is prone to localization issues, i.e. it depends on...
Excel return value or run calculation if criteria is not met. Formula needed
First, note the proper syntax for the IF() function: IF(logical_test, value_if_true, [value_if_false]) The logical_test is B2 <= 2. The value_if_true_ is 0. The value_if_false (i.e., if B2 is > 2) is B2 * 25. So the formula is =IF(B2<=2, 0, B2*25). For example (data starting in B2, formula in C2):...
Aging formula without using additional column
If your table starts in cell A1, with the first start date in cell B2, try =SUMPRODUCT(--(C2:C6-B2:B6<=30)) Edit after clarification: =SUMPRODUCT(--(A2:A6>124),--(A2:A6<127),--(TODAY()-B2:B6<=30)) ...
How do I increase from one number to another?
in b3, put =B2+(B$40-B$2)/38 with 38 being 40-2. Then, drag that formula down to B39. See: https://docs.google.com/spreadsheets/d/1SITsLCfSbsP1po6Og6gR3aquQuxUb2iyQrde9sQ2N2M/edit?usp=sharing...
SUMPRODUCT combining partial text and dates
If 06/08/05 should be 06/08/15 then you can return 3 with, =SUMPRODUCT((LEFT(B$2:B$999,4)="test")*(MONTH(AE$2:AE$999)=6)*(YEAR(AE$2:AE$999)=2015)) For versatility it would be better to put test in a cell (e.g. D2) and call it with one of these, =SUMPRODUCT((LEFT(B$2:B$999, LEN(D2))=D2)*(MONTH(AE$2:AE$999)=6)*(YEAR(AE$2:AE$999)=2015)) =SUMPRODUCT(ISNUMBER(SEARCH(D2, B$2:B$999))*(MONTH(AE$2:AE$999)=6)*(YEAR(AE$2:AE$999)=2015)) The FIND function is case sensitive; the SEARCH function is not. A more...
Search columns and return number of cells containing “5” against colleague name
excel,excel-formula,excel-2007,formula
We can do this with a helper column: Helper column, keep the name if the count of 5 equals to 5. Enter below to K2 and fill down. =IF(COUNTIF(E2:J2,5)=5,D2,"-") Then we are counting how many times name appears in the helper column. Enter below to B2 and fill down. =COUNTIF($K$2:$K$11,A2)...
COUNTIF with OR Statement
excel,excel-formula,worksheet-function,excel-2013,countif
Ugly, but might suit: =COUNTIFS(E:E;">120";E:E;"<139")+COUNTIFS(F:F;">80";F:F;"<89")-COUNTIFS(E:E;">120";E:E;"<139";F:F;">80";F:F;"<89") Counts all the applicable instances in ColumnE, adds the count of all the applicable instances in ColumnF then subtracts the instances where both ColumnE and ColumnF are within the respective bounds - this last part might not be required. An array formula might be more...
is there a shortcut to open filter>contains box in excel?
excel,excel-formula,excel-2007
Assuming you start with filtered data already, you need to have the header cell selected. From there: ALT+DOWN, F, A. If the filter is not already created then throw in a CTRL+SHIFT+L at at the start. If you are not in the header row, some combination of CTRL+ARROW keys can...
Multiply values based on numerous criteria
excel,if-statement,excel-formula,excel-2007
With a changed lookup array in Sheet2: I suggest: =ROUND(C2*IF(B2="PKG",INDEX(Sheet2!C$2:C$7,MATCH(C2,Sheet2!B$2:B$7,1)),IF(B2="POPULAR",INDEX(Sheet2!C$8:C$11,MATCH(C2,Sheet2!B$8:B$11,1)),INDEX(Sheet2!C$12:C$14,MATCH(C2,Sheet2!B$12:B$14,1)))),2) in D2 copied down to suit....
Excel 2010 MATCH function returns an error in conditional formatting
excel,excel-formula,conditional-formatting
Use INDIRECT =ISERROR(MATCH(INDIRECT("Table2[Website]");INDIRECT("Table13");0)) Similar Question Here INDIRECT():Returns the reference specified by a text string. References are immediately evaluated ...
Formula that will calculate total hours in overlapping date array
For your example, assuming start times/dates in B2:B6 and end times/dates in C2:C6 you can use this formula to get the total (non-overlapping) hours =SUMPRODUCT((COUNTIFS(B2:B6,"<"&MIN(B2:B6)+ROW(INDIRECT("1:"&ROUND((MAX(C2:C6)-MIN(B2:B6))*1440,0)))/1440-1/2880,C2:C6,">"&MIN(B2:B6)+ROW(INDIRECT("1:"&ROUND((MAX(C2:C6)-MIN(B2:B6))*1440,0)))/1440-1/2880)>0)+0)/60 That shows the result as a decimal number of hours. For that example I assumed you want to use the whole range - if you...
Improving repetitive use of IFs in Excel formula
You could use a table in the worksheet and use Vlookup, or do it all in one formula like =LOOKUP(A1,{"California","Kansas","Texas","Washington"},{3,3,1,2}) The first array must be sorted ascending. Edit: You can catch errors by wrapping the statement into an IFNA() like this: =IFNA(LOOKUP(A1,{"California","Kansas","Texas","Washington"},{3,3,1,2}),"not found") "Shorter" or "more efficient" really depends on...
Lookup value next to the first number in column
You can use an array formula like this, assuming you have up to 1000 rows of data =INDEX($A1:$A1000,MATCH(TRUE,ISNUMBER(B1:B1000),0)) confirm with CTRL+SHIFT+ENTER...
Excel OFFSET and IFERROR in a table
In fact, you are accessing the cell one above the top of the tables's data body. It is the header string value (e.g. RunningTotal) and you receive the #VALUE! error when you try to use the string mathematically with the @Change number. However, the SUM of a string is zero...
Excel Pivot Table Exclude one Row in Calculated Field
If your row variable was called "Year", your column variable called "Month", E1 was one of the cells in your pivot table and your values variable was called "Score", you could do something like =GETPIVOTDATA("Score",E1,"Month","Jan")-GETPIVOTDATA("Score",E1,"Month","Jan","Year",YEAR(NOW())) and repeat for each month. However you would need to leave room under the table...
Match pattern of a list of words and highlight the found results
excel,excel-formula,worksheet-function
Please try selecting ColumnA, HOME > Styles - Conditional Formatting - New Rule..., Use a formula to determine which cells to format, Format values where this formula is true:: =MATCH(LEFT(RIGHT(A1,8),7),B:B,0) Format..., Fill, select your choice of highlighting, OK, OK....
Excel - leave blank if reference cell is blank
You can nest another IF: This should do what you are looking for: =IF(ISBLANK(M3),"",IF(AND(TODAY()<M3+357),"NO","YES") ...
Need to find a value that matches two columns of criteria. Possible VLOOKUP
Two column matches typically come in one of two typical configurations; one being an array formula and the other being a standard formula. Both use variations of an INDEX/MATCH function pair. The standard formula for Quickbooks!A2 would be, =IFERROR(INDEX(Donations!A$1:A$999, MIN(INDEX(ROW($1:$999)+((Donations!B$1:B$999<>B2)+(Donations!C$1:C$999<>C2))*1E+99, , ))), "No match") The array formula version for...
Get the maximum values of column B per each distinct value of column A
Gary's Student's answer is correct. I added a couple things. Also, a link to the method: http://blog.contextures.com/archives/2011/07/27/finding-min-if-or-max-if-in-excel/ A distinct list of values can be generated in a couple ways, here's one: And here's links to a method or two for distinct lists. All with array formulas: Ignore Duplicates and Create...
Counting values embedded in strings inside a column (Google Spreadsheets)
excel,google-spreadsheet,excel-formula,formula,countif
You would have to use wildcards.. please try: =countif('Responses'!B:B,"*"&A2&"*") and see if that works ?...
Using OR in SUMIF Excel
You need to wrap the different OR parameters into curly brackets and enclose the whole Sumif in a SUM function, because Sumif will return an array of values that need to be summed up. Also, the two ranges need to be the same size. =SUM(SUMIF(A1:A10,{"cat","dog"}, C1:C10)) ...
Excel - Replace offset formula with actual cell reference
And here is one last approach to this that does not use VBA or formulas. It's just a quick and dirty use of AutoFilter and deleting rows. Main idea Add a reference to a cell =Sheet1!A1 and copy it down to match as many rows as there are in the...
Excel formula to VBA code
excel,vba,excel-vba,excel-formula
As pointed out in the comments by @siddarth you are looking for: ActiveCell.Formula = "=B4/B5" If you are in a hurry and just need to make the macro work, a compromise on code readability may be acceptable. In that case you could do the following: Start recording a macro Do...
Conditionally Format Minimum, Unique Value
excel,excel-formula,conditional-formatting
I don't think you can do that with just one conditional formatting formula. Here's a solution though: Use this array formula to find the lowest unique value: =INDEX(A1:A4,MATCH(MIN(IF(COUNTIF(A1:A4,A1:A4)=1,A1:A4,"")),A1:A4,0)) Be sure to confirm the formula with Ctrl+Shift+Enter. Then use conditional formatting to format a cell with that value. So if you...
How to count multiple instances of words in multiple cells in Excel
You can use an array formula (Ctrl+Shift+Enter) for this. Set up your list of names somwhere (For example, F1, F2, F3 ...) then next to it (in G1): {=COUNT(IFERROR(FIND(F1,A:A,1),FALSE))} Assuming A is you names column. Than simply drag G1 down to copy for all names....
Average of top 3 values within a specific pivot field
excel,vba,excel-vba,excel-formula,average
As for formula solution, if the cell containing "OUTPUT" was C3, the following formula should be put in D4 and pasted downwards. =IF(C4="","",IF(C5<>"","",IF(C3="",C4,AVERAGE(C2:C4)))) This will only work if your top 3 values are at the bottom of every group (model)....
How SUMIF function in Excel with brackets and “!” sign works
You have chosen unfortunate sheet names. "=SUMIF('1'!C2,C7,'1'!C[2])" The '1' is the sheet name. Sheet names can be wrapped in single quotes, but you only need to do that if the sheet name contains a space. The ! is a separator between sheet name and cell reference. C2 means column 2,...
Python not getting the right value in an Excel cell
excel,python-2.7,excel-formula,win32com
Adding this to the beginning of the _colorizeTop10Rejets method did the trick self.xl.Calculate() self.xl is the object returned by win32.Dispatch('Excel.Application')...
INDEX MATCH obtaining values for duplicate names
One solution would be to insert a column in both sheets with a "running count" of instances of the same name. For example, insert col C and in C2 enter =IF(A2&B2 = A1&B1, C1+1, 1). This starts the count at 1 if the concatenated first and last name is new,...
How do I match a date from a list of dates in excel?
excel,excel-formula,comparison
An alternative approach: Assume Date1-Date4 are in A2:A5, and Date5 is in B2: {=OR(B2=A2:A5)} Note that you need to enter the formula as an array formula using Ctrl+Shift+Enter....
Horizontal Index Match (returning column header)
It would seem that the three columns are unique; e.g. there would never be a Beef in the Color column. In that case you can simply query each column, passing back a 1, 2 or 3 as the case may be. =IFERROR(INDEX(Table1[#Headers], ISNUMBER(MATCH([@Name], Table1[Fruit], 0))*1+ ISNUMBER(MATCH([@Name], Table1[Color], 0))*2+ ISNUMBER(MATCH([@Name], Table1[Meat],...
Cannot use an Excel function without errors
Assuming you go all the way from A to ZZ, this means you are using 26*26 = 676 arguments. I am surprised this function has ever worked for you, since the maximum number of arguments is 255. Since you are using single cells, you should try just using simple addition...
Evaluating INDIRECT in an array in Excel
Are you only trying to get the SLOPE from the linear regression? If so, you can just use the SLOPE function after converting the #N/A to blanks (using IFERROR in a formula). SLOPE will then just toss out the blanks. If you want the intercept as well, use the same...
Adding division to current formula
If you need to get the amount of customers for that month and product, you can just modify that sumproduct function to get this: =SUMPRODUCT($B$3:$I$5*($B$1:$I$1=TheMonth)*(($B$2:$I$2="Actual Customers")*($A$3:$A$5=TheProduct))) That (for me) returned the value 56 for p C and 1951 for p b, so I'm pretty sure you would just have to...
How do I do a sum of contents in an adjacent cell equal a set value
You'd better lock the references to use absolute column references like this: put this formula in J and copy down: =SUMIF($C$2:$C$4,$H2,$B$2:$B$4) by add the $ sign to the column refs. ...
Filter information in two columns
excel,excel-formula,match,worksheet-function,input-filtering
With data like: In D1 enter: =IF(ISERROR(MATCH(A1,B:B,0)),1,"") and copy down and then in C1 enter: =IFERROR(INDEX(A:A,MATCH(ROW(),D:D,0)),"") and copy down. This results in: ...
Increment count in column based on value in column
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...
Sum the values from the days in a specific week in Excel
excel,excel-formula,week-number
Using SUMIF is one way. But you need to get your references straight in order to make it easy to enter. Note in the diagram below, the formula: =SUMIF(Weeknums,M$1,$B2:$K2) where weeknums is the row of calculated Week Numbers. Also note that the column headers showing the Week number to be...
Is there any formula to create a hyperlink in an excel to another sheet?
The Excel Hyperlink() function takes string arguments. You can build the string arguments by concatenating different elements. A concrete formula is a bit hard without knowing your workbook structure. Assuming that you want to link to the cell on the Domain_master sheet, and assuming that the column "Domain" is in...
Strange behavior in Excel when counting Work Hours and Work Super Hours.
When you perform math operations in Excel you will sometimes get floating point errors. They come about because Excel is limited to 15 digit precision so significant digits after 15 are ignored. Times in Excel are all percentages of days. Computers calculate in binary so all those decimals have to...
Converting letters to text and back again (Excel 2013)
excel,excel-formula,formula,vlookup
Personally, I would convert the letter grade to ASCII character using the function: CODE(A1) use the ASCII Reference Chart for the integer value of each Upper Case character. Note: A=65, B=66, etc... Perform your calculations, then you can use the function: CHAR(A2) to convert the number back into a character....