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 on: How to use IF function in Excel: examples for text, numbers, dates, blanks
by Svetlana Cheusheva, updated on
Comments page 79. Total comments: 4830
Hi,
I have some values in column A(row1:row10) and column B(row1:row5). I need to compare column A values with column B, if any matches means, I need that value in column C.
Example:
Column A Column B Column C
1 5 1
2 6 2
3 1
4 2
5 8 5
6
7
8 8
9
10
Pls give me the formula to perform above operation
Hi There,
I want to create a multiple formula as follows:
=IF(D3=1,16000000,IF(D3=2,12000000,IF(D3=3,5000000,3000000)))
But, it doesn't work on my Excel 2013. Is there something wrong with the formula?
Please advise.
Thanks.
columnA ColumnB
mango fruit
apple fruit
orange fruit
pepper spice
salt spice
How do I input a formula under columnB to have answer either fruit or spice?
i want to formula for whose name or spell starting or includes in name e.g. s the name selected.
such as suman,sumit ,hans,kiss, then what is the formula for takeing all.
Teacher 1 02:10:05 history
Teacher 2 09:12:12 social
Teacher 2 01:12:12 math
Teacher 2 00:12:12 physics
Teacher 4 06:12:12 chemistry
Teacher 2 09:12:12 evs
Teacher 1 07:12:12 robotics
Teacher 2 06:12:12 syc
Teacher 2 02:12:12 health
Teacher 2 09:12:12 geo
Teacher 3 02:12:12 indian his
Teacher 3 01:12:12 french revo
Teacher 3 01:12:12 language
here i need to add all the value of the teacher 1.
i.e., if "teacher 1" is true then it has to display "history,robotics"
i use the =COUNTIF(A1:A13,"teacher 1") to count the entry of user (teachers)
and
i use =SUMPRODUCT(--(A1:A13="teacher 1"),B1:B13) to find the some of the product of an user (teacher)
and i need your help for the top most "to add all the value of the teacher 1"
All provided learning about excel is very helpful. Thank you Svetlana & team
Hi,
I want to to create an IF function where the true value will equal the text in a given cell from another sheet. Is it possible to set up?
thanks
Hello BETHAN,
Sure, you can do that. For example:
=IF(1=1, Sheet2!A1, "")
Hi want to get
if i entered yes in A1,then it should return value 5 in B1,and if i entered NO in A1,then it should return value 0 in B1, is it possible to impelement.Please help me
Hi Anish,
Here is the formula for B1:
=IF(A1="yes", 5, IF(A1="no", 0, ""))
Dear Madam,
i have a cell for date and need to check the month and another cell will appear text according to the month. Example:
Jan = Cohort 1
Feb = Cohort 2
A1 = 5-Jan-2015 and i want A2 to be equal to Cohort 1
what is the formula for A2 ?
Thank u in advance!
hi, I need to replace a cell by searching another cell when contain a specific text, would you please help me to know if it is possible or not?
How do I write this in the "if function" If the Quantity in Stock is less than 50, then the cell should display Order. If not, then the cell should display Ok
how to find cell position in selected area or specifica area
a b c d
1
2 b2
3
4 a4 4d
like this
i m using this formula but it working only till two slabs......
=IF(I15=90,"120",IF(I15>=100,"124",IF(I15>=125,"130",IF(I15>150,140,IF(I15>175,142.5,IF(I15>225,143,IF(I15>250,144))))))))
Dear Sir/ Madam,
I have a rate slab.
how i use slab by if function??? Plz Help
KG Rate
90 below 100.00
90 up 120.00
100 up 124.00
125 up 130.00
150 up 140.00
175 up 142.50
200 up 142.50
225 up 143.00
250 up 144.00
I have a data set where i have to find if a date is between 1 March 2016 to August 2016) and if they are then yes, else No.
Hello Kevin,
You can use a formula similar to this:
=IF(AND(A1>DATEVALUE("3/1/2016"), A1
My university boards horses. Monthly boarding fees are due the 3rd day of the month for the current month -- for example, for September, fees were due September 3rd. If they pay after that date, we charge them $5 a day until the 15th, for a possible total of $60.
I would like to create a formula that will calculate how much they owe if they pay late. For example, if a person paid on September 6, they would owe $15.
I think I can do an IF function incorporating DATEVALUE. When I tried it with the September 6 example above, I got $55 instead of $15.
Here is part of the formula I used:
=IF(O6DATEVALUE("9/4/2016"),5,0)+IF(O6DATEVALUE("9/5/2016"),5,0)
which continued with the dates to 9/15/16.
I need a formula that will replace the 'YES' or 'NO' with a number as I want to use this formula
=75+(A1*3) but if that number is below 125 I want it to automatically default to 125.
Any idea how to do this?
I also want to be able to do this without adding an additional column?
Thanks
Hi,
I need a cell in lets say Column A = a cell in Column B.
It's a Reference number like this 2016SEP15-1 that needs to appear in Column B as I type it in Column A.
As what do I set those two Cells? TEXT or GENERAL?
How do I get them to relate or equal?
I hope I've described it well.
Thanks
I have three set of numbers, I want the cell to tell me which on is greater by using 3,2,1.
Example
80 3
50 2
10 1
To give me the order my the 3, 2, 1.
Hi this formula correct, pls advive
=IF(COUNTIF(P112:P133,)=_T110,(T112*0.07))*(1+$I$1)
Hi
If D5:D12 contains an X I need this to link to another sheet to show as the value of 20. I am trying to link a sheet with ticks/crosses to show the selection with a sheet that then gives these a numerical value
I am a basic coder.I want to know if my cell contains any"text" and I want to take some value from another cell. Is it any Way to do that.
example=IF(T2="text",pic value from E2)
I am using the following Countifs formula:
=COUNTIFS(C1:C200,A2,D1:D200,B1)
The data in columns C and D are text (not numbers).
This formula generates a zero for those that do not meet this criteria, but I would like it to display nothing (be blank) instead of displaying a zero. Is it possible to do this? THANKS!
I need help formatting a pivot with labels that were not properly repeated prior to emailing it to me.
Column C2 has a store name and column C12 has a different store name
=If(C3="",C2,if(c4=c3,c3,___)
But how to I get it to pick up a different store name without having to lock cell C2? My goal is to formulate this down a spreadsheet of 5,000 lines.
Is this possible to help with my formatting?
Which Formula Use In This Condition If "This=This=This==True"
Hi Svetlana Cheusheva,
Your posts helps me a lot in excel and today I am stuck in a situation. Situation is:
In C:C column I have the codes (BR, DHN, RS, AN and so on), in F:F column I have the months number (6, 7, 10 and so on) and in I:I column fee is mentioned (2500, 4200, 3500 and so on).
Now I wish to apply a formula in column J2, that whenever there is a code "BR" is mentioned in column C:C then it automatically multiplies the values in column F:F and I:I against the mentioned code and then gives me the sum of total value that will be generated by multiplying the different values in column F:F and I:I.
If you can help me out.
Regards
Abatis
Hi Svetlana Cheusheva,
Your posts helps me a lot in excel and today I am stuck in a situation. Situation is:
In C:C column I have the codes (BR, DHN, RS, AN and so on), in F:F column I have the months number (6, 7, 10 and so on) and in I:I column fee is mentioned (2500, 4200, 3500 and so on).
Now I wish to apply a formula in column J2, that whenever there is a code "BR" is mentioned in column C:C then it automatically multiplies the values in column F:F and I:I against the mentioned code and then gives me the sum of total value that will be generated by multiplying the different values in column F:F and I:I.
If you can help me out.
Regards
Absti
if i want to do something like this then
1 = 350 then when i type 20 value should go to 700
Col A Col B Col C Col D Col E
FAIL Total Accuracy
Account XXX 2 2 =IF(OR(B1="pass","fail"), B2/C2,C2/D2)
anyone, please help for me to get accuracy calculation in column e
column b, sometimes pass or fail since this is pivot
I am using a drop down box
what i am trying to achieve is if the value 1 is selected specific text will appear if the vale 2 is entered specific text will appear and so on. so far i go to the drop down box select format cells custom
and use this formula [=1]"octagonal";[=2]"round";"not selected";
this works so far but i can not add options for if 3,4,or 5 are entered
please please held of offer another solution.
I want to look up a value (B1) in a specific column (B:B). If the value is found then "1", otherwise "0".
Hi
I am trying to apply a 15% discount if a "Yes" is present or 0% if a "No" is present.
C5 has my yes/no criteria
D5 has the amount 15% discount needs to be applied to
Really struggling to get no errors in my formula
Is there a way to carry over the excess of two numbers to another cell? For example:
A1=74
A2=80
I want to add these two numbers together but it cannot exceed 100. If it does exceed 100, the excess will go to A3.
How can I do this?
Hi John,
check below...formula
=IF(A1+A2>100,A1+A2-100,"A1, A2 sum is below to 100")
i want to use the vlookup formula as per the below:-
=IF(D349=(VLOOKUP(D349,D350:D9883,1,0)),0,VLOOKUP(D349,Stock!A:B,2,0))
but it create error.
please check and give solution.
Here is the scenario:
A technician grabs a piece of PC Desktop RAM and scans a bar-code into "C2" that represents a specific model number.
The formula I need, needs to take that bar-code scanned, search through an already generated list "L1:L20" and decipher whether to change "A1" into the text "Bin1" or not.
"Bin1" Representing if the barcode scanned is on the list "L1:L20" or not.
I need help with a formula please.
I am trying to make a formula that can write the time zone.
so if the number starts with 458, 541 or 775 then write Pac* and if 340, 506 or 902 then Atl
is there a way to do that ?
Hi, I am using this formula and would like the answers to come back with a colour (e.g. "Within Budget" to either have green text or a green filled cell and red for any items coming back with "Over PO"). Is this possible? If so how?
=IF(J14<=J16,"Within Budget","Over PO")
Hi
Great then =10
Less than = 8
what formula using in answer less than value showing
Hi
am trying to use conditional formatting to create a formula for the below but having difficulties. Please can you help!
IF cell G3 is "Yes" highlight (Fill with colour) cell G3 to Cell A3
Many Thanks
GT
Hey
I need help with IF form. I now have tryed everything but still can't make it work.
If C12 is TÖÖ then calculate in F12, E12-D12 if the C12 is ERA then calculate id G12, E12-D12 other wise it should stay empty or 0
Hi, i hope you can help me with this. I'd really appreciate it. I'm not that good with formulas. I need to do a calander. I have a drop down box with every month. I need the calendar to put the dates 1-31 under the right day for each month that is selected from the drop down box. Any suggestings? Please
hi,
I need to have 2 cells which only can have 2 times to use,
if the 3rd data occurred , then it will show nok.
example :
352 5 ok
352 5 ok
353 4 ok
353 4 ok
354 3 ok
354 3 ok
354 3 nok
Hi Svetlana, Can you please help. I have about 50 persons names who must complete +-twenty documents before a following process can start, How can I put that into a If statement. I would like to use a right mark for yes and X for not complete. Then I would like to hyperlink from a summary back to the detailed spread sheet. The summary contains several steps with several document per step.
Your help is appreciated,
Anton
Hello Anton,
To help you better, we need a sample table with your data in Excel and the result you want to get.
You can email it to support@ablebits.com. Please add the link to this article and your comment number.
Can anyone figure this out? IF there is duplicate text in column A (customer's email) THEN add their sales up in column B. (So we can determine a total customer's sales.) Thanks!!
Hi I need to make performance monitor for my team mates,
If >2 mean Poor
2 to 3 mean Good
5 mean excellent
For example:
If these are my students score
exam 1 exam 2 exam 3 exam 4 Description
72 100 100 60 .............
100 50 90 60 ............
then i want to give the description on each of exam (for example good in exam 2 and 3 but bad in exam 1 and 4) How to do like that? please help me?
Hi there! Love your site, and I use it all the time! Thanks for the help in advance.
I have dates 8/22/16-12/31/2016 (past and future)across row 2 from H2:CX2, and data for each date in cells H3:CX3. There are 650 total rows of data for each date.
I am looking to make a column starting in CY3 that provides a simple "Y", "N" if 2 conditions are met for that particular row. The formula will be dragged down to all 650 rows, and each row needs to be tested independently.
1. The date in H2:CX2 is greater than todays date. AND
2. The value in H3:CX3 is greater than 0.
This is what I have right now.
=IF(AND(COUNTIF($H$2:$CX$2,">"&TODAY()),COUNTIF(H3:CX3,">0")), "Y","N")
It reports a Y if the value in H3:CX3 is >0, but ignores the date part. I only want a Y if the value is associated to a future date, and an "N" if the value is from a past date.
Thank you!
I am working in time office, i want to prepare a labour wage sheet. I am giving Food only to those labour who are working at site. we have differencet trade Like mason, carpenter, Cook, washerman etc. but I want to give food allowance USD 05 per day only Mason, Carpenter, Fabricator only. Any one can help me to find out solution by using MS Excel.
I will thankful to you.
Regards
Hi Svetlana,
Can you please guide or help on my concern, It would be of great help.
I have data in sheet1 & almost same data in Sheet2 (eg:- below)
please note-the only difference is in the formation of name of medicine.
Sheet1 Sheet - 2
CLINXA 20GM GEL CLINKA GEL 20GM
CLINXA A 20GM GEL CLINXA A GEL 20GM
CLOBEN G 15GM CREAM CLOBEN G CREAM 15GM
CLOBETAMIL G 20GM CREAM CLOBETAMIL G CREAM 20GM
CLOBIREX G PLUS 10GM CREAM CLOBIREX G PLUS CREAM 10GM
how can i get value (prescription not required) after knowing that sheet1 & sheet2 has same name of medicine regardless the formation of name.
Thanks
Hitesh
Hello.
I need simple logical formula, but not understand.
It is a cell with a value of 1 or 2, and need to be a value corresponding to the name.
Please help to understand how to do it.
Thanks.
=IF(D2=1,”JĀ”,IF(D2=2,”Nē”,”Varbūt”))
This formula doesn't work :(
=IF(B2=1,”JĀ”,IF(B2=2,”Nē”,”Varbūt”))
Doesn't work :(