IF is one of the most popular and useful functions in Excel. Generally, you use an IF statement to test a condition and to return one value if the condition is met, and another value if the condition is not met. Continue reading
by Svetlana Cheusheva, updated on
IF is one of the most popular and useful functions in Excel. Generally, you use an IF statement to test a condition and to return one value if the condition is met, and another value if the condition is not met. Continue reading
Comments page 4. Total comments: 4813
I'm trying to do a if function but use a formula. Is this possible or is it another function i should be using?
=IF(H12="RO","0","=SUM(I12-H12-I13)")
Hi! I don't know what you wanted to calculate. Read the article above carefully, as well as the instructions for the SUM function. Here is one possible variant of your formula:
=IF(H12="RO",0,I12-H12-I13)
Hi, I have different city names and I want to group it in different state by using IF function - pls support
For Example, if one cell has the city name as "X" / "Y" / "Z" should be named as "Apple", If "A" / "B" / "C" as "Orange", so on - how to do it
Hi! For multiple conditions, you can use the IFS function. For example:
=IFS(OR(A1="X",A1="Y"),"Apple",OR(A1="A",A1="B"),"Orange")
You can also find useful information in this article: Excel Nested IF statement: examples, best practices and alternatives.
HI,
i am trying to using IF command for following application but unable to success,
=IF(H100=100A-3P,"100")
will you please suggest is this formula is Ok or need correction
Best Regards
Mehmood Saleem
Hi! 100A-3P - is text. You must use quotation marks for text: H100="100A-3P"
I currently have this formula:
=IF(B1601<0,"Currently Overspending by $"&IF(B1601<0,ROUND(SUM(B1601*-1),2),ROUND(B1601,2)) &"!","Can Contribute $"&ROUND(B1601,2) &" to Savings.")
I was wondering if and how a different text format can be passed for the Value? For example I would like the "true" value to be red and bold and the "False" value to be blue and bold.
Hi! Create a separate conditional formatting rule for each of the TRUE and FALSE values. Then each of these values will be highlighted as you want. I recommend reading this guide: Excel Conditional Formatting tutorial with examples.
Greetings. I am a 4th-grade teacher working on a data tracking spreadsheet and cannot figure out how to get the data I desire from two columns into the third column.
Column C will have a student's test score (either a 1, 2, 3, or 4). Column D will have the student's retake test score (either a 1, 2, 3, 4, or it will be left blank because he or she did not need to take the retake). Currently, I am using the formula below to show the score change for a student's test in Column E:
=IF(C5"",C5-D5,"")*(-1)
I am looking to see data in the following manner:
1) If there is data in Column C and in Column D, I want to show the data change in Column E
- if the score goes up from a 1 to a 3, Column E would show a 2 (and of course any variation of a score increase would be represented)
- if the score goes down from a 4 to a 3, Column E would show a -1 (and of course any variation of a score decrease would be represented)
2) If there is data in Column C but not in Column D, I do not want data to show in Column E
- if a student's data in Column C is 3 and there is no data in Column D (they did not take the retake test), I do not want anything to show in Column E
I cannot wrap my head around how to get this done and have had great success with assistance on this Blog. For someone that understands the ins and outs of functions, my guess is this will not be too difficult, but for me, I am stumped. Any help would be appreciated. Thank you.
Trigg
Hi! If I understand your task correctly, try the following formula:
=IF(NOT(ISBLANK(D1)),D1-C1,"")
Read more: ISBLANK function in Excel to check if cell is blank.
How i can i calculate time used for to supply order ?
For example:
Distributor delivery order between 07am and 4PM (Agreed time), and he received order 11h10am on November 28 and deliver order on 14pm on November 29 2023,
Hi! If I understand your task correctly, the following tutorial should help: How to calculate time in Excel - time difference, adding / subtracting times. I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.
its a great tutorial but unfortunately I have problem and I couldn't fix it yet. I want to convert cells(in a column) to another thing(first column is 1" , 2" , 3" and etc. and second column should be 1 mm , 2 mm , 3 mm) but i have a problem, since the first column has double quotation the excel fails to proceed.
P.S: I use this formula like this : IF(A1="1""), "1 mm", "")
Hi! Replace quotes with text using the SUBSTITUTE formula:
=SUBSTITUTE(A1,"""","mm")
I have to create a rating system in excel. For example, if 2 or more ratings are listed as "marginal", then the final rating is marginal. If there is one marginal, but the other ratings are satisfactory then the rating is satisfactory. How do I make that into an excel formula?
Hi! Unfortunately, this information is not enough to recommend a formula to you. If I understand your task correctly, the following tutorial should help: Excel IF statement with multiple conditions.
Hi I have to create a formula with multiple AND OR.
Example: =IF(OR(D2="MXP", J2="RASO"), "190 €", IF(AND(D2="TRN", J2="RASO"), "160 €", "")) but it doesn't work.
Any idea of how I can make it work?
Thanks
Hi! I can't check your formula because I don't have your data. I can't guess what result you wanted to get.
Hi,
I have a 25 rows (e.g., F2:AD2). Some of the cells in these rows contain a specific text, while the rest are empty. I need a formula which can extract the cell number of the rows that contain the specific text.
for example, if N2 and Z2 has the specific text, i want the formula to return "N2, Z2". I used IFS and it only returns the first true value and omits the rest.
How do i get it done?
Many thanks.
Hi! To get a list of cells in row 2 that have values matching the criterion in cell D5, try the formula:
=TEXTJOIN(", ",TRUE, IFERROR(ADDRESS(2,SMALL(IF($D5=$A$2:$AD$2,COLUMN($A$1:$AD$1)), COLUMN(A1:AD1)),4),""))
You can learn more about ADDRESS function in this article: Excel ADDRESS function to get cell address and more.
hi i dont know what formula will i use
"55+0= 0 or blank"
if the value in column b is 55 and column c is 0 i would like to see it blank in column d or show it as 0 value but if i input value in column c operations should be done on column d (55*1=55) hope you get my point
Hi! I hope you have studied the recommendations in the tutorial above. For example,
=IF(AND(B1=55,C1=0),0,B1*C1)
or
=B1*C1
Hello!in sheet1 from column A - K are all occupied,in column G there are mixture up trucks registrations numbers of 3 different countries,i need to maintain that column but link every country registration number to go to different sheet depending on first letter of the registration
Hi! Use hyperlinks and these instructions to link to the needed workbook worksheet: Excel Hyperlink: how to create, change and remove.
Greetings, I am trying to complete a trip sheet / logbook. In column "I" there is an address, in column "H" it should be the name of the business visited. I am not sure if possible to nest 138(unique paired) IF or IFS based on text. The address is text and it should return a text. Also the must be a "else" for any unknown address. If there is a limit on nesting how many can be nested?
The entire worksheet is over 2000 lines so to copy a formula would make things easy.
Thank you so very much for any help.
Hi! In your case, it is not possible to use the IF function. To find an address by name (or vice versa) use the VLOOKUP formula or a better alternative - INDEX MATCH formula.
This should solve your task. If you have any other questions, please don’t hesitate to ask.
Greetings
My formula has a syntax problem.
=VLOOKUP(G14, lookup!$A$1:$B$127)
=VLOOKUP(G14, lookup!$A$1:$B$127, 2 0)
=VLOOKUP(G14, $lookup.A1:B127)
Column G contains the address.
I have tried this in column F and H and neither is working.
The main sheet is where I am trying to the formula.
lookup is a sheet on the same workbook as the main.
Column A contains address
Column B contains a name that matches A
Thank you.
Hi! I can't understand and correct your formulas as I don't have your data and don't know what result you want. Maybe this article will be helpful: Excel LOOKUP function with formula examples.
Hi just trying to get a formula for:
if a sheet of data with names and numbers. a specific name "Steve" and to locate his name in a seperate sheet and add up his expenses for the month of january please,
its probably simple im just confusing myself
Hi! To find the sum by name and by date, use the SUMIFS function as described in this manual: Excel SUMIFS and SUMIF with multiple criteria.
How to write a condition with a date correctly, read here.
You can also use a pivot table to show the sum by name and dates on another worksheet.
I'm using a formula "=TEXTJOIN("",TRUE,IFERROR((MID(C39,ROW(INDIRECT("1:"&LEN(C39))),1)*1),""))" to copy numeric contents of a cell to another cell, now I would like to modify it to the same but only if it is 9 digits or more if it is less than 9 digits to copy the complete cell
Hi! If I understand correctly, use the LEN function to find the length of the text string that TEXTJOIN returns.
=IF(LEN(TEXTJOIN("",TRUE,IFERROR((MID(C39,ROW(INDIRECT("1:"&LEN(C39))),1)*1),"")))>9, C39, TEXTJOIN("",TRUE,IFERROR((MID(C39,ROW(INDIRECT("1:"&LEN(C39))),1)*1),"")))
Please help to create an If statement formula identifying the Peak (P) and Off peak (OP) in the time of a day with the following conditions:
Peak (P) - from 0801H - 2100H Mon-Sat and 1801H-2100H during Sundays and holidays
Off Peak (OP) - from 2101H-0800H Mon-Sat and 2101H-1800H during Sundays and holidays
Tried so many formulas but returns a #value. 26/12/2022 12:05:00 am is the date-time format reference that Iam using.
Thank you.
Hi! To get the day of the week, use the WEEKDAY function. Since time in Excel is the fractional part of the date, get this fractional part by subtracting the integer part from the date using the INT function.
=IF(OR(AND(WEEKDAY(D1,2)<7, (D1-INT(D1))>TIME(8,0,0), (D1-INT(D1))<TIME(21,0,0)), AND(WEEKDAY(D1,2)=7, (D1-INT(D1))>TIME(18,0,0), (D1-INT(D1))<TIME(21,0,0))),"P","OP")
helloo Dear Alexander
I need a function a liittle unusual. just wanted to ask you to help me.
Let Jump to an example:
=IF(Condition = TRue, condition1, get data from user)
in other word If condition is True do an affair an calculation, but if is not true, get as data entry.
would you please to help me if there is a way to do this?
Thanks alot.
Hi! What does the “get data from user” phrase mean? If you want to get data using an external reference, specify it in the IF formula. If you want to get data for multiple cells or a table, use VBA.
Hello!in sheet1 from column A - K are all occupied,in column G there are mixture up trucks registrations numbers of 3 different countries,i need to maintain that column but link every country registration number to go to different sheet depending on first letter of the registration
Hi! If I understand your task correctly, the following tutorial should help: Hyperlink to a specific pace in the current workbook.
Hi,
Can i use if formula "Monthly data same date if there is same name is enter double then it should be highlighted "i dont want to filter and check each date if the name is double entry or not
Unfortunately, I can't guess what formula you are talking about or what you want to do.
My concern is I have a data with many names and different date's
A with name and B with date
Cell "A " if the name is entered 2 times on the same date .
example: name John - 1st oct 2022 is OK ,should not be there again in the same date .
i need a formula to confirm there is no double entry on the same date
Hi! If I understand your task correctly, the following tutorial should help: Highlight duplicate rows in Excel.
You can use a formula to indicate a duplicate record:
=IF(COUNTIFS($A$2:$A$100,$A2,$B$2:$B$1100,$B2)>1,"Duplicate","")
Can I return a number and a text value? I am trying to get the value in the cell with the IF statement to display the numerical difference between two cells and also say Under or Over. For context, I want it to subtract an estimated budget with an actual budget and then return the value saying how much the difference is and if it is over or under budget in the same cell. Is this possible?
Hi! To merge text and number, I recommend studying this guide: CONCATENATE in Excel: combine text strings, cells and columns.
The formula might look like this:
=IF(B2-A2>0,"over budget "&(B2-A2),"under budget "&(A2-B2))
Hi
How to write this formula:
If cell B4 is >1 or =15 then B5=1
If cell B4 is <15 but < 30 then B5=2
If cell B4 is 305 but < 45 then B5=3
Ihave been trying to use IF(AND/ OR but I am not getting it write.
Hi! Try to follow the recommendations from this article: Nested IF in Excel – formula with multiple conditions.
Mohonbantuan untukrumusnya
jika A1 saya isi P maka A2 isinya 100.000, jika A1 disi T maka A2 isinya 200.000, jika A1 di isi K maka A2 300.000
mohon bantuannya, trus cara aturnya gimana.
terimasaik,
I want the numbers to showi the first column automatically, as i enter the names or items in the next column. How can i do that? I remember something like: =if(b2=???, row-1, ""), but cannot remember the exact formula.
Thanks in advance
Hi! I don't know what numbers you want to display. You can get the row number by using the ROW function. Check for an empty cell using the ISBLANK function or the <>"" expression. For example:
=IF(NOT(ISBLANK(B2)),ROW(B2),"")
=IF(B2<>"",ROW(B2),"")
You can find the answer to your question in this article: Nested IF in Excel – formula with multiple conditions.
Hi!
I have tax rate ranging from 5% - 20% and each are give the rate
5,000 is nil tax
5,001 - 10,000 is 5%
10,001-15,000 is 10%
15,001 - 20,000 is 15%
20,001- above is 20%
How can this be incorporate in the formula?
I have 2 columns. One of the column is labeled "Earnings Overpriced". In that column I have successfully calculated either "Overpriced" or nothing appears in the cells. In the second column labeled "Cash Flow Overpriced". In that column I have successfully calculated either "Overpriced" or nothing appears in the cells. In a third column I want excel to look at both these column cells and when "Overpriced" appears in both columns I want excel to calculated in the third column "Earnings and Cash Flow Overpriced". When the "Overpriced" appears in only the Earnings column and the Cash Flow column has nothing in the cell, I want excel to calculate "Earnings Overpriced". When the "Overprice" appears only in the Cash Flow column but the Earnings column has nothing in it, I want excel to calculate "Cash Flow Overpriced". When both columns have nothing in them, I would like excel to place a "Buy" in the cell. This seems simple to me but I can't get my formula to work.
Appreciate any help you can provide. Tony
Hello,
- Column A contains % change values that are positive and negative. Column A has rows that contain no values.
-I'd like to display the % change values that are positive in Column B and nothing if a Column A row contains no value.
-I'd like to display the % change values that are negative in Column C and nothing if a Column A row contains no value.
Thank you very much!
Hi! Please re-check the article above since it covers your task. If I understand correctly, thy this formula
IF(A1>0,A1,"")
Hi there!
Loved using this for my basic data entry but now i have a super complicated entry! (in my mind at least)
I have multiple entries containing different "finishes" eg Black 16mm in A B C D E
I need a cell to basically run a bunch of formulas and add text where the values are matching
for example i have a black 16mm board but i have 5 different finishes for this board - we'll call them A, B, C, D, E
Can I get a single cell formula to basically look at all these values (A-E) and spit out the value i need like "Black 16mm C"
I currently have a formula saying if "A" then TEXT, if not ATTENTION but having to do this for many seperate values is time consuming!
Hi! You can use the drop-down list to view the list of values and select the desired value from it. I hope it’ll be helpful.
I compare values in two columns of a row and using ÍF" condition I confirms whether they are same.
But , even ifthe values are same, the Ïf"condition formula returns "False" in certain cases( not in all cases).Why it is so?
Hi! If you are comparing numbers, check all decimal digits. To do this, set the format to General. If you are comparing text strings, check for spaces and line breaks. Read more: How to remove blank spaces in Excel - leading, trailing, non-breaking.
I need to write a formula that states if a values multiplied by another value plus a value divided by 2 is less than a value in a cell it will equal that number in the cell plus 1
I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
Hi,
I have a value in cell b1("Date") and b2("Transferred"). If B2=Transferred, then b1 and b2 must return a blank values
If b1 and b2 have no values, then cells remains blank
if b1("date") and B2 says Dismissed or Resigned, then both cells stay as is.
Please assist.
Thank you
I don't understand how B2 can simultaneously have the value "Transferred" and return an blank value. Explain.
A2, B2 & C2 having NA then result come delete or A2, B2 & C2 HAVING OTHER VALUE THEN result come no action
I am not sure I fully understand what you mean.
Help. Can you help me with a formula for this scenario:
If a number is between 40,000-83,999 the multiply that number by 15% or 8,399, whichever is less, if a number is between 84,000-124000 then multiply that number by 10% or 9,999, which is less...
Thank you so much if you can help me with this.
Hi! Use the nested IF function. Find the minimum value with the MIN function.
=IF(AND(A1>40000,A1<83999),MIN(A1*0.15,8399),IF(AND(A1>84000,A1<124000),MIN(A1*0.1,9999),""))
This is a fantastic resource. I am diving into making better spreadsheets at work, and already enjoying conditional logic built into Excel even though we are using MS Excel 2010.
I have a spreadsheet that I have made for job numbers. I have a "due date" in Column Cell D3 which is using icon sets to place a red circle icon and second condition for the same cell to have format of the cell shaded red. This works well for a visual of when the date has reached and is now expired, the visual is perfect.
However, I have another column with "Completed date" which is a manual entry in column Cell K3. I would like that when we add a date in K3, this is a trigger to change the icon set to use a green tick icon, and then change the shading of the background to green.
I cannot work out the conditional logic for this scenario.
Many thanks
Hi! Create a conditional formatting formula, where you use the logical AND function to combine the conditions for cells D3 and K3.
The formula might look like this:
=AND([conditions for Cell D3],(ISNONTEXT(K3),K3>0)
I have an excel sheet I am working on and can't figure out the formula to use.
So I have column G "Procedure Type" (Example: XXX, YYY, ZZZ) and column L Start date. If Coumn G is XXX, I need it to calculate how many cells in column L has a date (not counting the blank cells).
Hi! To count values by multiple criteria, use the COUNTIFS function:
=COUNTIFS(A1:A20,"xxx",B1:B20,"<>")
You can also find useful information in this guide: Count if blank or not blank.
Hi!
Thanks for your article. I have some work to do more. Is there any way to put “else condition” into another column?
Thank You.
hi, I tried to write a formula about if a certain column appear a certain key word "gas", then the column of CoC equal the column contractor, or appear "na"
=IF([@[Facility
Owner]]="gas",[@COC]=[@[Contractor
/Excavator]],"na")
it appear" na" correctly if the value is not "gas", but when the context is gas, the value appear 0, I don't know how to fix this problem. can you help?
Hi! I can't check your formula because I don't have your data. When the context is "gas" then the expression [@COC]=[@[Contractor/Excavator]] is fulfilled and returned FALSE or zero.
Seeking help on how to assign formula if you have a range of variables
For example
If the cell contains 0, then the result should be 0
If the cell contains Nos 1 - 5, then the result should be A
If the cell contains Nos 6 - 10, then the result should be B
If the cell contains Nos 11 - 15, then the result should be C
....
Hi! You can find the examples and detailed instructions here: Nested IF in Excel – formula with multiple conditions.
I work in a Coffee plantations where our estates are coded in the following format:
"EAPCPK51" - The first three letters denote the estate name, "EAP", the second three letters denote the division name, "CPK" and finally the number denotes the block number. How do I decode these codes into three columns namely, estate, division and block number. Is there a formula for this?
Hi! If I understand your task correctly, the following tutorial should help: Excel substring functions to extract text from cell.
To extract characters and split your text into three cells, use these formulas:
=LEFT(A1,3)
=MID(A1,4,3)
=MID(A1,7,10)
I'm building an attendance tracker where several conditions must be met.
Cell B4 - Duration >4.00 or <4.00
Cell C4 - Reason = Illness/Injury, Family Emergency, Other
Cell D4 - Excused/Unexcused = Unexcused
Cell E4 - IF B4 is greater than 4.00 and all other conditions are met, result is be 1.00; IF B4 is less than 4.00 and all other conditions are met, result is 0.50; IF D4=Excused (regardless of other conditions), result is 0.00
Hi! Try to use the recommendations described in this article: IF AND in Excel: nested formula, multiple statements, and more.
Correction...here is the formula I used... Still getting tripped up somewhere.
=IF(AND(B4>=4.00), (c4="Illness/Injury","Family Emergency","Other"), (D4="Unexcused"), "1.00", IF(AND(B4<4.00), (c4="Illness/Injury","Family Emergency","Other"), (D4="Unexcused"), "0.50")))
Hi!If one of several conditions is true, I recommend using IF OR statement. For example
=IF(AND(B4>=4,OR(C4="Illness/Injury",C4="Family Emergency",C4="Other"),D4="Unexcused"), "1.00", IF(AND(B4<4,OR(C4="Illness/Injury",C4="Family Emergency",C4="Other"),D4="Unexcused"),"0.50"))
A little more direction is needed. Are you able to show me the formula?
=IF(AND(B4>=4.00, c4="Illness/Injury""Family Emergency","Other") D4="Unexcused"), "1.00", IF(AND(B4<4.00, c4="Illness/Injury""Family Emergency""Other"), D4="Unexcused"), "0.50")
Basically, what I need is:
If H2 is "A" then enter 234 in K2, OR if H2 is "AB" then enter 456 in K2, etc...etc...
Note. This sequence has four letter to number combinations.
Thank you
Hi! You can find the answer to your question in this article: Nested IF in Excel – formula with multiple conditions.
Thank you
Hello, I have columns that has "Yes, No, Yes" as an answer. If I have all 3 as "Yes, No, Yes", my formula should reflect "Yes". And if one of the 3 has a different answer, (i.e "No, No, Yes"), my formula should reflect as a "No". How can I do this?
i.e Column H = Yes, Column J= No, and Column K=Yes and result in Column N should be Yes.
if Column H answer is No then Column N Should reflect as NO
Hi!
If I understand your task correctly, the following formula should work for you:
=IF((H1="Yes")+(J1="Yes")+(K1="Yes")>1,"Yes","No")
Thank you for your help Alexander. Unfortunately, it did not work.
(H1=Yes)+(J1=No)+(K1=Yes) should give me a result of "Yes"
And all other combinations should result as "No"
I tried and modified the above given formula but it does not work as I want it to be.
Hi! The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one.
Use IF AND statements:
=IF(AND((H1="Yes"),(J1="No"),(K1="Yes")),"Yes","No")
Hi! Sorry if I was not clear on my first message. The new formula worked! Thank you
I am trying to write a nested formula. In cells A25:C25, the value is either "Paid" or "Unpaid". I would like to write a formula in a single cell (G30) such that the IF statement evaluates the entire range (A23:C25) ... if the value of a cell = "Paid", do nothing and move to next cell in the range, ... but ... if the value of the cell = "Unpaid", I want the value of another cell to be added together (Cell G30 should return either 0 or a SUM (of all cells in this range =Unpaid)).
For example, if A25="Paid", do nothing and move to B25. If B25= "Unpaid", go to B23 and capture that value (5), then go to C25. If C25="Unpaid", go to C23 and capture that value (10). Now add B23 + C23 (but not A23). If this can work, the return value on G30 would be "15" (because two of the cells in the range =Unpaid, so their associated values (in Row 23) were added together.
I tried IF with AND and with OR, but I could not get the "calculate across the range" to work; I keep getting errors or the value of only one cell (like B23). I cannot get the SUM calculation to work properly.
Hi! If I understand the problem correctly, you can try the SUMIFS function to calculate the sum based on the criteria.
For example,
=SUMIF(A25:C25,"Unpaid",A23:C23)
Here is the article that may be helpful to you: How to use SUMIF function in Excel with formula examples.
Hello,
I have a column with different values "RN, OT, PCC, " I am trying to ask if J2 = RN, match H2 cell to these values, if the value matches return 4 or 5, and so on if cell J3 matches RN, Ot, Pcc, and give them all different values to match?
=IFERROR(J2="RN",IF(AND(H2>=1.48,H2=1.47,H2=1.63,0,0))))*IF(J2="PT",AND(H2=1.19,H2>=1.33,4,IF(H2=1.34,0,0))))
Hi! It is very difficult to understand your formula. However, AND(H2>=1.48,H2=1.47,H2=1.63,0,0) makes no sense because these conditions can never be met at the same time. It is not clear why 0 is needed here. I can assume OR(H2>=1.48,H2=1.47,H2=1.63).
Maybe this article will be helpful: Excel IF OR statement with formula examples.
I'm trying to do a long list of variable consequences. The line I am trying to do is, in simple terms, IF cell=A, 1,m if cell =B, 2 etc. But I am getting an error that it is not complete.
Never mind, I found my error. Thank you.
I have sheet as: rows has days like: sunday, monday etc for a whole month and column has present or absent. now i want to make formula which will give me count of how many days someone absent on friday and saturday on that month
Hi! To calculate the count of values by condition, use the COUNTIFS function.
We have a special tutorial on this. Please see: Excel COUNTIFS and COUNTIF with multiple AND / OR criteria.
Create a formula in cell I4 that displays the word Yes if the value in cell H4 is less than the discontinue threshold in cell B21, otherwise the word No is displayed. Your formula must use an appropriate reference to cell B21 so that it will copy correctly. Make sure to use structured references when referencing cells in the table.
Use the recommendations from the article above to solve your task. This guide will also be helpful: Structured references in Excel tables.
I need help with a formula that will look at Column A and compare it to Column B in sheet 1. If there is a number in column B in sheet 1 display the contents of column A in sheet 2, if it is blank display "Need" where the number would go in sheet 2.
Please re-check the article above since it covers your case.
Hello,
I have a formula that will pull registered number of attendees over expected when there is a value. However, there can be duplicated rows of registered attendee numbers. I inserted another formula into the J column to flag this and it inputs the text "duplicate".
Is there a way to add to the formula below to input 0 into I2 when the "duplicate" text exists"?
=IF(I2="",G2,I2)
Thank you,
Lisa
Hi!
If I understand the question correctly, try this formula:
=IF(J2="duplicate",0,G2)
You may find another method described in this manual helpful: How to count unique entries ignoring blank cells.
I mean 0 into H2 which is where this formula exists.
Sorry.
I am trying to have a column that states if a specific cell has a number greater than or equal to 30 than show "overdue" otherwise blank. I am using this formula [=IF($J5>= 30, "OVERDUE", "")]
however it also will mark overdue for the J column cells that have the formula [=IF(G4>1, TODAY()-G4, "")] in their cell even if the cell is technically blank. How do I exclude the formula and only look for cells that have a number value greater than or equal to 30
Hi!
Based on your description, it is hard to completely understand your task. However, I’ll try to guess and recommend using ISFORMULA function to check whether a cell contains a value or a formula.
If this is not what you wanted, please describe the problem in more detail.
I have column J contains the formula =IF(G4>1, TODAY()-G4, "") and I want to use the formula =IF($J5>= 30, "OVERDUE", "") in column K so that it showcases whether a specific entry has been over 30 days. The formula for K works except that it also puts a value in column K for rows that column J is technically empty but still contains the formula text string. I am looking to find a way where the column k formula will only place "overdue" when connected to a number value >=30 and exclude those cells with the formula text string. Hope that makes sense.
I have a list of names in one column with a column of one of 9 possible values associated with it. e.g A1 = Alan, B1 = Apple, and Apple & Alan are associated.
I want to visually show by variable the names associated with that variable, ideally in a 9 box chart. E.g under the variable Apple would be all the names associated with Apple. Under Banana would be all the names associated with Banana, is there a way with a nested IF statement to print those names automatically? The list will change periodically so want to avoid manual intervention is possible.
Thanks!
Hi!
If I understand correctly, you want to determine a partial match of text strings. Perhaps this instruction will be helpful: How to find substring in Excel
I'm trying to do a combination of an If then statement and a textjoin function.
Common language of what I'm trying to say if A26:A167="SB", then TEXTJOIN C26:C167 together into one cell for the ones that match "SB"
I'm at: =IF(A26:A167="SB",(TEXTJOIN("",TRUE,C26:C167)))
This will dump all the email addresses into one field that are marked SB.
I am getting a #SPILL! error.
Hello!
#SPILL! error you get because your formula returns an array of 140 values, not 1 value. Read more about #SPILL! error in this article.
You can get a list of values by condition using the FILTER function. See this manual for detailed instructions and examples: Excel FILTER function - dynamic filtering with formulas.
Try this formula:
=TEXTJOIN(" ",TRUE,FILTER(C26:C167,A26:A167="SB"))
I think I figured it out...not sure if it is the most efficient or best formula, but it seems to work.
=TEXTJOIN("; ",TRUE,IF(A26:A167="SB",C26:C167,""))