The IF function in Google Sheets is one of the easiest functions to learn, and while this holds true, it is also a very helpful one.

In this tutorial, I invite you to take a closer look at how Google Spreadsheet IF function works and what advantages you will get from using it.

Whenever you use the IF function, you create a decision tree in which certain action follows under one condition, and if that condition is not met – another action follows.

For this purpose, the condition of the function must be in a format of the alternative question with only two possible answers: "yes" and "no".

This is how a decision tree may look like:

So, the IF function allows you to ask a question and indicate two alternative actions depending on the received answer. This question and the alternative actions are known as three arguments of the function.

The syntax for the IF function and its arguments are as follows:

=IF(logical_expression, value_if_true, value_if_false)

**logical_expression**– (required) a value or logical expression that is tested to see if it is TRUE or FALSE.**value_if_true**– (required) the operation that is carried out if the test is TRUE.**value_if_false**– (optional) the operation that is carried out if the test is FALSE.

Let's explore the arguments of our IF function in more detail.

The first argument represents a logical question. Google Sheets answers this question with either "yes" or "no", i.e. "true" or "false".

How to formulate the question properly, you may wonder? To do that, you can write a logical expression using such helpful symbols (or comparison operators) as "=", ">", "<", ">=", "<=", "<>". Let us try and ask such a question together.

Let's assume that you are working in the company selling chocolate in several consumer regions with many clients.

This is how your sales data may look like in Google Sheets:

Imagine that you need to separate sales made in your local regions from those from abroad. To accomplish that, you should add another descriptive field for each sale – a country where the sales took place. Since there is a lot of data, you need this description field to be created automatically for each entry.

And this is when the IF function comes to play. Let's add the "Country" column to the data table. "West" region represents local sales (Our Country), while the rest are the sales from abroad (Rest of the World).

Place the cursor in F2 to make the cell active and type in the equality sign (=). Google Sheets will immediately understand that you are going to enter a formula. That's why right after you type the letter "i" it will prompt you to choose a function which begins with that same letter. And you should choose "IF".

After that, all your actions will be accompanied by prompts as well.

For the first argument of the IF function, enter: B2="West". As with the other Google Sheets functions, you don't need to enter the address of the cell manually – mouse click is enough. Then enter comma (,) and specify the second argument.

The second argument is a value that F2 will return if the condition is met. In this case, it will be a text "Our Country".

And again, after the comma, write the value of the 3^{rd} argument. F2 will return this value if the condition is not met: "Rest of the World". Do not forget to finish your formula entry by closing parenthesis ")" and pressing "Enter".

Your entire formula should look like this:

`=IF(B2="West","Our Country","Rest of the World")`

If everything is correct, F2 will return the text "Our Country":

Now, all you have to do is to copy this function down the column F.

`=ARRAYFORMULA(IF(B2:B69="West","Our Country","Rest of the World"))`

Let's examine the other ways of working with the IF function.

The usage of the IF function with a text has already been illustrated in the example above.

You can use numbers for the arguments just as you did it with the text.

However, what is very important here is that the IF function makes it possible to not only fill cells with certain numbers based on the conditions met but also calculate.

For example, let's say you offer your clients various discounts based on the total value of the purchase. If the total is more than 200, then the client gets a 10% discount.

For that, you need to use column G and name it "Discount". Then enter IF function in G2, and the second argument will be represented by the formula that calculates the discount:

`=IF(E2>200,E2*0.1,0)`

There are cases when your result depends on whether the cell is empty or not. There are two ways to check that:

- Use the ISBLANK function.
For example, the following formula checks if cells in column E are empty. If so, no discount should be applied, otherwise, it's 5% off:

`=IF(ISBLANK(E2)=TRUE,0,0.05)`

**Note.**If there's a*zero-length string*in a cell (returned by some formula), the ISBLANK function will result in FALSE.Here is another formula to check if E2 is empty:

`=IF(ISBLANK(E2)2<>FALSE,0,0.05)`

You can turn the formula the other way around and see if cells are not blank instead:

`=IF(ISBLANK(E2)=FALSE,0.05,0`

`=IF(ISBLANK(E2)<>TRUE,0.05,0)`

- Use standard comparison operators with a pair of double-quotes:
**Note.**This method considers*zero-length strings*(indicated by double-quotes) as empty cells.`=IF(E2="",0,0.05)`

– check if E2 is blank

`=IF(E2<>"",0,0.05)`

– check if E2 is not empty.**Tip.**In a similar manner, use double-quotes as an argument to return an empty cell by the formula:`=IF(E2>200,E2*0,"")`

As you have already learned, the text, numbers, and formulas can act as the arguments of the IF function. However, other functions can play that role as well. Let's see how it works.

Remember the first way you figured out the country where you sold chocolate? You checked if B2 contained "West".

However, you can build the logic the other way around: list all the possible regions that belong to the "Rest of the World" and check if *at least one of them* appears in the cell. The OR function in the first argument will help you do that:

=OR(logical_expression1, [logical_expression2, ...])

**logical_expression1**– (required) the first logical value to check for.**logical_expression2**– (optional) the next logical value to check for.- and so on.

As you can see, you just enter as many logical expressions as you need to check and the function searches if one of them is true.

To apply this knowledge to the table with sales, mention all the regions that belong to the sales abroad, and the other sales will automatically become local:

`=IF(OR(B2="East",B2="South"),"Rest of the World","Our Country")`

The AND function is just as simple. The only difference is that it checks if all listed logical expressions are true:

=AND(logical_expression1, [logical_expression2, ...])

E.g. you need to narrow the search to your own town and you know that it is currently buying only hazelnuts. So there are two conditions to consider: region – "West" and product – "Chocolate Hazelnut":

`=IF(AND(B2="West",C2="Chocolate Hazelnut"),"Our Country","Rest of the World")`

You can also use the IF function itself as an argument for the bigger IF function.

Let's assume that you have set stricter discount conditions for your clients. If the total purchase is more than 200 units, they get a 10% discount; if the total purchase is between 100 and 199, the discount is 5%. If the total purchase is lower than 100, there is no discount whatsoever.

The following formula shows how the function will look in the cell G2:

`=IF(E2>200,E2*0.1,IF(E2>100,E2*0.05,0))`

Note that it is another IF function that is used as the second argument. In such cases, the decision tree is as follows:

Let's make it even more fun and complicate the task. Imagine that you're offering the discounted price to the one region only - "East".

To do that correctly, add the logical expression "AND" to our function. The formula then will look the following way:

`=IF(AND(B2="East",E2>200),E2*0.1,IF(AND(B2="East",E2>100),E2*0.05,0))`

As you can see, the number of discounts have reduced greatly while their amount remains intact.

There's also an easier way to write the above thanks to the IFS function:

=IFS(condition1, value1, [condition2, value2, …])

**condition1**– (required) is the logical expression you want to test.**value1**– (required) is the value to return if the condition1 is true.- and then you just list conditions with their values to return if they are true.

Here's how the above formula will look with IFS:

`=IFS(AND(B2="East",E2>200),E2*0.1,AND(B2="East",E2>100),E2*0.05)`

`=IFERROR(IFS(AND(B2="East",E2>200),E2*0.1,AND(B2="East",E2>100),E2*0.05),0)`

One of the questions we get asked a lot is how to create the IF formula that will return whatever you need if the column contains or doesn't contain a certain record.

For example, check if a customer's name appears more than once in a list (column A) and put the corresponding word (yes/no) into a cell.

A solution is simpler than you may think. You need to introduce the COUNTIF function to your IF:

`=IF(COUNTIF($A$2:$A$20,$A2)>1,"yes","no")`

If you're tired of keeping track of all those extra characters and proper syntax in formulas, there's another solution available.

IF Formula Builder add-on for Google Sheets offers a visual way of creating IF statements. The tool will handle syntax, extra functions and all required characters for you.

All you need to do is:

- fill blanks with your records one by one. No special treatment for dates, time, etc. Enter them as you always do and the add-on will recognize the data type.
- select required comparison operators from the suggested drop-down lists.
- if needed, add multiple logical expressions in a click: IF OR, IF AND, ELSE IF, THEN IF.

As you can see, each logical expression takes its own line. The same goes for true/false outcomes. This reduces the number of possible confusion over the formula drastically.

As you fill everything out, the formula for use will grow in the preview area at the top of the window. To its left, you can select a cell in your sheet where you'd like to have the formula.

When you're ready, paste the formula into the cell of interest by clicking the Insert formula button at the bottom.

Please visit the online tutorial for IF Formula Builder to see all options described in detail.

I hope that there's no room for any doubt now that the IF function, though a very simple one at first glance, opens the door to many options to data processing in Google Sheets. But if you still have questions, feel free to ask them in the comments section down below – we'll be happy to help!

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

Category: Google Sheets add-ons

## 417 responses to "Google Sheets IF function - usage and formula examples"

I'm trying to populate a certain text in column F if column c says a specific thing. I'm using =IF(C3:C237 = "DMM901Y", "No PPW Status Available", " ") on the F column, but it's not working. All help is appreciated. Thank you!

Edit:: It works on a singular cell, but not the whole column. I want the rule to work for the whole column.

Thank you for your question, Lindsay.

Try wrapping your IF with an ARRAUFORMULA:

=ARRAYFORMULA(IF(C3:C237="DMM901Y","No PPW Status Available",""))

If you're wondering how it works, please check this blog post.

=IF(B2>=96.01%,"5",if(B2<96%,"4",if(B2<95%,3,IF(B2<94%,2,IF(B2<92%,1)))))

Help on this please.

1 96.01%

Hello Mark,

You need to introduce AND to your conditions. Please see my reply to Patrick above, he had a similar logic in his task.

If I want to use this function on existing data, and if the predicate is false I do not want the row to change, how do I go about that?

If I'm getting it correctly, just live the argument for the "false" result empty.

How do we use the if statement to redirect to a website?

if value=true

then the person is redirected to a website.

Hello Aditya,

Using the formula, you can only make it return a webpage for the user. Just enter the hyperlink as an argument, and it will appear if value=true. However, the person will have to click the link in order to open it.

I would like that if A1 is 0 then put cell B1 in cell C1.

Is this possible?

Looking forward to your help!

Hello Michael,

yes, it is possible. Enter the following formula to your C1:

=IF(A1=0,B1,"")

Is it possible to do this but searching for a value in a column?

I would like that if A1 = value in any cell in a column B, put cell C in the same row in cell D in same row.

Hope it makes scense.

Hello Lea,

I believe it's a VLOOKUP function you need for this task.

I am trying to add an IF statement that would add a multiplier if they play for a certain team. So like, I have rows of stats, name and team. So IF they play for the rockies they would have a .9375 multiplier. However the thing I need it multiplied to is already a formula. So can I add an if statement to a formula, if so, how?

Hello Jackson,

For me to be able to help you out, please share a small sample spreadsheet with us (support@4-bits.com) with your source data and the result you expect to get. I kindly ask you to shorten the table to 10-20 rows.

Note.That email for file sharing only. Please do not email there. Once you share the file, just confirm by replying here.I am looking for help to create a formula that will calculate tax liability as follows:

1.) If annual income is less than $42,900 then tax is calculated by multiplying the tax amount by 5.8%

2.) If annual income is $42,900 or greater and is less than $101,550 then tax is calculated by multiplying the difference between $42,900 and the annual income by 6.5% then adding $2,488.

3.) If annual income is greater than $101,500 then tax is calculated by multiplying the difference between $101,550 and the annual income by 7.15% and adding $6,447

Any assistance would be helpful!

Hello Christie,

Please check if the formula below works as desired. It is written supposing that your tax amount is in column A and annual income is in column B, so just make the necessary adjustments based on your data:

=IF(B2<42900,A2*5.8%,IF(AND(B2>=42900,B2<101550),(B2-42900)*6.5%+2488,IF(B2>101500,(B2-101550)*7.15%+6447)))

Hello,

I'm trying to create a fixed daily summary of the data below.

Date Fruit Name Quantity

4-Nov-19 Apple 10

4-Nov-19 Orange 20

4-Nov-19 Grapes 50

4-Nov-19 Banana 15

5-Nov-19 Apple 20

5-Nov-19 Orange 50

6-Nov-19 Orange 20

6-Nov-19 Grapes 50

6-Nov-19 Banana 15

This below is the summary format I want to do

Summary

Date Apple Orange Grapes Banana

4-Nov-19

5-Nov-19

6-Nov-19

...

Hello,

I'm afraid IF is not the function you'd normally use to find matches.

You can either try the add-on described here to lookup matches by all conditions (date and fruit), or create a pivot table out of your source data.

I'm doing of spread sheet of our household bills and want to show the difference from the previous month with some result being + change some being - change. How do i do that?

Hello Jerel,

We described different ways of comparing Google sheets in this blog post:

https://www.ablebits.com/office-addins-blog/2019/04/30/google-sheets-compare-two-sheets-columns/

For google sheet

If(or(A1="",A1="TBD"),"",text(A1,"yyyy")))

Why this formula returns TBD if A1 contains TBD?

Hello Nadeem,

I'm sorry but the formula you provided can't return TBD in case it's already in A1. A cell with this formula will remain empty in this case.

I kindly ask you to double-check the formula in your spreadsheet and the contents of A1.

I have a column that will a yes or no. Based on the answer, I would like for another column to calculate a tax. Example, if cell F13 is Yes, then I would like to look at an amount in cell G13 and calculate the tax based on the amount in cell G13. Then if cell F13 is No, I would like for it to look in cell G13 and calculate the tax based on the amount in cell G13. Bottom line, if a person is married (Yes) then there will be one calculation and if a person is not married (No) there will be a different calculation.

Hello Robert,

If I'm getting your task clear, a formula like this will do:

=IF(F13="Yes",calculation_for_YES_with_G13,calculation_for_NO_with_G13)

In case you're still not sure how it works, please describe how your data is arranged in more detail.

Hello, I am trying to create an inventory tracker to track multiple items being sold at multiple locations. I have an Inventory sheet that has Product A listed (lets call that A2) and then a numerical quantity under Original Stock at Location A (B2). Then there's Current Stock at Location A (C2). Then I have a Sales sheet. What I'd like is when a sale is recorded for Product A at Location A, for C2 on the Inventory sheet to equal (B2- the new sale). I was able to make an IF formula that works when the sale is recorded in a specific cell, but the sales will be listed in chronological order so I'm trying to make the formula work for the entire column all the way down. Is there any way to do that? Thanks so much!

Hello Jamie,

I'm really sorry but the description is a bit confusing. Please share a small sample spreadsheet (support@4-bits.com) with your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.

Note.The email address above is for file sharing only, please do not email there. Once you share the file, just confirm by replying here.I'll look into it and do my best do advise you.

Hi there, I am trying to create a ticket tracking system that will display pricing based on location and date. I have 5 locations named "1J", "3A", "4Y", "10Y", and "ND" that are listed using Data Validation in column C, and Dates in November are Listed in Column A. I would like to have the price populate in column B based on the Location selected AND if the date listed in column A is before 11/18/19 the price would show one amount, and if the same location was selected but it was after 11/18/19 then it show a different amount. Example: If "1J" was selected on 11/5/19 the price would be "$20", If "1J" was selected on 11/19/19 the price would be "$22" because 11/19/19 is after 11/18/18. I want cell B2 to change if the date is before 11/18/19 and a certain location has been chosen in cell C2. So far this formula works but how would I create a formula that allows for the other 4 locations to possibly be selected and location relevant pricing populate?

Also FYI 11/18/19=43787

=IF(AND(A2<43787,C2= "1J"), "$18", "$20")

Hello,

If I understand your task correctly and the prices vary not only based on dates but also on locations, you need to create a nested IF and list each price for each set of conditions, for example:

=IF(AND(C2="1J",A2<43787),"$18",IF(AND(C2="1J",A2>=43787),"$20",IF(AND(C2="3A",A2<43787),...))))

Hello, I am trying to make a sheet that connects to a form. The form will be asking what tools are needed, if they put needed i want it to put a 1 in the column and if its not needed than put a 0. How would i formulate that? i tried =if(B4 "needed", "1", "0") but that did not work.

TIA!

Hello Madison,

You missed an equal sign after the cell reference. Also, numbers don't need to be in quotes. Please try this formula:

=IF(B4="needed",1,0)

Hi, I'm trying to create a cell that will show if the date in one cell is "outdated" or "updated" and a blank return if there is no date listed on that specific cell. I have already create the first part, but I'm having a hard time in introducing the "BLANK" part. I'm new in this kind of stuff and still exploring. Hope you could help.

Thank you!

Hi Eana,

Use a pair of double quotes ("") to check if a cell is blank (=IF(A2="",...)) or to return a blank cell if the condition is met (=IF(A2=20,"YES",""))

Hi,

i wanted to make due date format based on three criteria. I wanted a texts on a specific cell like "Processing" if date on other cell is later than 7 days from today, "Coming soon" if date is within next 7 days from today and "Pending" is date is earlier than today. i used below formula but only for "coming son " criteria not working properly. cell shows "coming soon" if the date is one day earlier than today. others criteria working properly.

Formula used:

=IFS(I2<TODAY(),"Pending",I2TODAY()+7,"Processing")

Please help me on that.

Hi Munna,

You need to use AND for the "Coming soon" criteria:

AND(I2>=TODAY(),I2<TODAY()+7),"Coming Soon"

I'd recommend adding it for "Pending" as well to avoid getting it if I2 is empty:

AND(I2<TODAY(),I2<>""),"Pending")

how can I input the value from the cell in a formula? for instance

=If($B2="Campbell", substitute($H2," [ ", " Campbell "),$H2)

The value of B2 is Campbell but the value of C2 is another name. So rather than have to type the name manually or pasting it into the formula, how can I input the value from any cell into the formula above?

Hello JB,

I'm sorry, can you please provide examples of what is exactly in C2 (since you're looking for a square bracket) and what you need to have as a result of the formula?

I pickup cars in different zones and each zone has a different pay rate. Is there an if formula to calculate each zone and pay rate on one sheet?

Hello Scott,

You can either build a nested IF and specify each zone and its pay rate there (they can be listed in an additional table and simply referenced in the formula) or use IFS instead.

Principal:Interest:Balance:

$330.29 $69.71 $23,465.76

$83.32 $131.68 $23,382.44

$587.18 $27.82 $22,795.26

$606.29 $8.71 $22,188.97

$536.55 $78.45 $21,652.42

$0.00 $21,652.42

I'm wanting the spreadsheet to show "$0.00" if there is no principal or interest is added in the row. How would I do the formula to make it show this? =SUM(K10-I11) is what the formula is looking like!

Hello Mike,

I'm sorry I'm a bit confused. Is it the balance that should show $0.00 if principal and interest are missing or something else? What do you use the SUM formula for?

Please provide more details on your task so I could understand it fully and suggest accordingly.

Thank you.

i want to permit serially typing to 50 staff members in excel(google spread sheer) eg. 1.ABC, 2.DEF, 3.GHI, 4.JKL, in this serial other than 1 will not type data before 1st NO., then only No. 2 will type data, then only 3, then only 4 ---will type the data by using e-mail

Hello there,

I'm really sorry but your task is not clear to me. Please describe it in more detail and provide an example of what you have at the start and what you're trying to get.

I'll try my best to help.

If(N2,N2,if(O2,$P$1,""))

IN THIS FORMULA,EVERYTHING IS PERFECT. BUT THE PROBLEM IS THAT WHEN I ENTERED A GOOGLE FORM SHEET AND IT APPEAR RESULT ON GOOGLE SHEET. THE FORMULA AUTOMATICALLY SHIFTED INTO NEXT CELL.

PLEASE HELP ME: JUST TELL ME HOW I CAN ALLOCATE FORMULA EACH AND EVERY CELL IF ANOTHER INFO. IS ENTERED FROM GOOGLE FORM LATER...

Hello Sayra,

I'm afraid you will have to copy the formula manually since each submitted Form response is added as a new row to Google Sheets.

I'm trying to do a function where if F2, G2, or H2 are yes then I2:M2 are 0 but if F2, G2, or H2 are no then it's the autosum of I2:M2. I know this can be done in excel but the computer I have I am not able to put excel on. Can anyone please help me write this out to where it works or tell me if it can even be done?

Hello Tawana,

I guess this is a formula you need:

=IF(OR(F2="yes",G2="yes",H2="yes"),0,SUM(I2:M2))

However, you can't enter it to I2:M2 since these cells should be counted if any of F2, G2, or H2 have "yes" in them.

So in other words, either the formula should be entered to other cells or you need to adjust the conditions.

=IF(and(B2="Wellness Pod","70")IF(and(B2="45min Pod Roll","47.40")))

B2 column is a drop menu that we select which service the client had.

I then need the value or a percentage of that value to display in the total column.

Please help.

(Also this is in google sheets)

Hello Jessie,

If I'm getting it clear, your formula should look like this:

=IF(B2="Wellness Pod",70,IF(B2="45min Pod Roll",47.4,IF(...)))

Hi,

I'm trying to write a formula that will give me a text statement in column B if column A contains a specific number.

For instance Column A can have a number that is 1-24, each number has a different item assigned to it. What I would like to do is put in the specific number in column A and have column B auto populate with the item name that correlates to it.

1 = Apple

2 = Orange

7 = Pumpkin

So on and so forth.

Is there a formula that will actually do this?

Hi Ricky,

Sure. Assuming your numbers start from A2 and end in A10, here's the formula for numbers you specified:

=ARRAYFORMULA(IF(A2:A10=1,"Apple",IF(A2:A10=2,"Orange",IF(A2:A10=7,"Pumpkin"))))

Hi,

I am trying to write a formula and was hoping someone may be able to help.

I have created a training matrix for my work.

Column A is the name of each employee, and Column B is labeled (Safety Training) Column C is labeled (ESD Training). Under each column I place an X next if the person in column A has been trained.

On a second sheet, I am making a page for (Safety Training). I want that page to list each name that has an X under the safety training column on Sheet 1.

I have created this formula =IF(Matrix!T6="X", Matrix!A6) which will bring the names into sheet 2, but In a list of 10 names, I see the names in column A on sheet 2, but the word "FALSE" elsewhere.

Is there a way to look at sheet 1, and take just the names in Column A, that also have an X in Column T and list them on Sheet 2?

I hope all of this makes sense.

Thanks for the help.

Hi Dan,

Your formula looks correct, so it's hard to tell why it doesn't work without seeing your data.

I should also mention that it is VLOOKUP that is usually used to match and pull records from one table to another. We also feature a special tool that returns all matches based on your conditions. Feel free to check it out.

Hello!

I am working with a donation spreadsheet that lists the companies and the years 2017, 2018, 2019 in separate columns. In each column for the years, the amount each company donated is in those columns. Some companies donated one year and not the others while others donated all three years. Is there any way for me to sort or filter which companies donated in 2019, in all three years, and the companies who donated in 2017 & 2018 but not in 2019? Would a filter be the best option, or are there ways to do a table or chart of some sort to organize this data by who donated which years?

Hello Katrina,

The easiest way, to my opinion, is to try our special tool that works like an advanced filter: it will create different lists based on different conditions (empty/not empty) without interfering with your source data.

There are a few other standard ways to try though:

Hello

I am trying to create a formula but not able to crack it. There are 3 parameters based on which the result is calculated and also there is All parameter which is not there in the data. When I select All it should calculate for all the data and when i select a specific parameter is should calculate for that. I also need the result based on any combination. This is the formula i have come up with but it does not populate result If I select All for all the parameters.It does only for one selection of All

=IF(BD41="All",(SUMPRODUCT(($AY$2:$AY$6600=$BD$2)*($AO$2:$AO$6600=BE41)*($B$2:$B$6600=BF41)*($AQ$2:$AQ$6600=BF42)*1)),IF(BE41="All",(SUMPRODUCT(($AY$2:$AY$6600=$BD$2)*($C$2:$C$6600=BD41)*($B$2:$B$6600=BF41)*($AQ$2:$AQ$6600=BF42)*1)),IF(BF41="All",(SUMPRODUCT(($AY$2:$AY$6600=$BD$2)*($C$2:$C$6600=BD41)*($AO$2:$AO$6600=BE41)*($AQ$2:$AQ$6600=BF42)*1)),(SUMPRODUCT(($AY$2:$AY$6600=$BD$2)*($AO$2:$AO$6600=$BE$41)*($C$2:$C$6600=BD41)*($B$2:$B$6600=BF41)*($AQ$2:$AQ$6600=BF42)* 1)))))

Please help

Hello Sandeep,

I'm really sorry but I'm afraid we won't be able to trap the error without seeing your data.

I am trying to create a If formula the if x=1 then pull results from another tab and cell. x could equal 1-5. When I creat this function: =if(F$12=1,'Tier Monthly Incomes'!B2,if(F$12=2,'Tier Monthly Incomes'!C2),if(F$12=3,'Tier Monthly Incomes'!D2),if(F$12=4,'Tier Monthly Incomes'!E2),if(F$12=5,'Tier Monthly Incomes'!F2)) .

I am getting an error message that says, "Wrong number of arguments to IF. Expected between 2 and 3 arguments, but got 6 arguments." Can you think of a way that I could make this work or suggest an alternative way to address this? Thanks!

Hi Tom,

The problem is that you put a comma and each new IF after the closing bracket, while they should go inside of those, like the very first one you built. In other words, you need to move three first closing brackets to the very end of your formula:

=IF(F$12=1,'Tier Monthly Incomes'!B2,IF(F$12=2,'Tier Monthly Incomes'!C2,IF(F$12=3,'Tier Monthly Incomes'!D2,IF(F$12=4,'Tier Monthly Incomes'!E2,IF(F$12=5,'Tier Monthly Incomes'!F2)))))

I am creating a Workbook for my organization and am trying to pull data from different sheets into another sheet. I understand how to do that but I want one cell to pull data on the page from a different sheet, but then a different cell on that same page to read the data from the first cell and then pull the data from another sheet. I hope that makes sense.

Hello Jason,

I described different ways of pulling data between sheets in this blog post. You can also incorporate them to your IF function to check some condition first and then pull the needed data based on it.

Is there a way to make this to not spit out false if the cell is blank? We would like to keep adding to this spreadsheet without a running list of FALSE down column K, if that makes sense.

=if(F3="Chace: xxx","@ChaceChilds",if(F3="James: xxx","@JamestheRich"))

Hello Cindi,

Sure. It is double quotes that are used to indicate a blank cell. If I'm getting your task right, try adding the pair as the third parameter at the very end of your IF:

=IF(F3="Chace: xxx","@ChaceChilds",IF(F3="James: xxx","@JamestheRich"

,""))Hello, I am trying to sum several cells into another cell. Just by reading this, you would say "oh, that's easy". Not as simple as it is though. My aim is to have 3 options in a cell and each option should equal to a specific number (eg. D2 cell has options "Red" which is 0, "Yellow" which is 1, and "Green" which is 2. So the text in the cell should equal a number. I have several of these cells to create and all of them should be summed into another cell (eg. D2,E2,F2 to be all summed into J2). Hope it's understandable. :-)

Hello Victor,

Just to get it clear: are all 3 words in one cell? Or are they in different cells that you need to sum eventually? Also, are there any other words in cells?

Please provide the exact contents of all the cells that you'd like to sum.

Thank you.

I am trying to calculate break times out of hours worked, but my break times change depending on how many hours you work in a day - e.g work more than 6h10m but less than 8h10m = 30 min, any higher and it's 45 min.

My formula is

=IF(OR(E3>TIME(6,10,0),E3TIME(8,10,0),E3<TIME(9,9,0)),E3-TIME(0,45,0)))

I have tried adding a the second "IF" argument, but it keeps returning only the 30 min deduction even when the result is greater than the 8h10min and I am not sure where it is going wrong. Am I asking too much of the formula for the one cell?

Hello Chris,

Will this formula do what you need?

=IF(AND(E3>=TIME(6,10,0),E3<=TIME(8,10,0)),E3-TIME(0,30,0),IF(E3>TIME(8,10,0),E3-TIME(0,45,0),""))

The first part checks if the time spent falls between 6h10m and 8h10m. If so, it subtracts 30 min from E3. The second IF checks if more than 8h10m was spent, in which case 45 min are subtracted.

The very last double quotes mean that a cell will remain empty if it took less than 6h10m for the job.

Hi! I'm trying to make a formula that will return a numerical value (1 or .25) based on the information in 2 cells. If a cell has "trainer" in it, then return ".25", and if a cell has "sponsored", then return "1".

I tried using the if(and formulas listed above, but get an error message. Here's my formula for reference.

=IF(and(C4="Sponsored",F4<1),1,if(and(C4="Trainer",F4<1)0.25,))

I also tried a different approach with nested if statements, and can get a result of 1 for sponsored, but get a blank cell for trainer. Here's that formula for reference.

=if(F5 <1,if(C5="TRAINER",0.25),if(C5="SPONSORED", 1,))

Any ideas?

Hi, David,

You missed a comma right before 0.25 (after the closing bracket) in your first formula, that's why it fires an error.

As for the second one, the closing bracket after 0.25 is an excess one.

Hey I am trying to populate my table using the if statement where I would like to check the value of a Boolean expression from column I so that if the statement returns as correct the value of column H should appear as the same in Column J So how should I write the statement

Hello Kevin,

I believe a formula like this should help:

=ARRAYFORMULA(IF(I2:I10=TRUE,J2:J10,""))

If number in cell E2 equals 1-3, cell G2 equals $0.

If number in cell E2 equals 4-6, cell G2 equals $100.00

If number in cell E2 equals 7-9, cell G2 equals $250.00

If number in cell E2 equals 10 or more, cell G2 equals $500.00

Can you help me with this please?

Thank you! Peg

Hello Peg,

You need to create a nested IF and introduce the AND operator for your conditions, like this:

=IF(AND(E2>=1,E2<=3),0,IF(AND(E2>=4,E2<=6),100,IF(AND(E2>=7,E2<=9),250,IF(E2>=10,500,""))))

Is it possible to do this but searching for a value in a column?

I would like that if A1 = value in any cell in a column B, put cell C in the same row in cell D in same row.

Hope it makes scense.

Hello Lea,

I believe it’s a VLOOKUP function you need for this task.

Hi,

how would I put If field A bigger than field B then x, otherwise y.

=IF(C3>A2,"Achtung","ok")

doesn't work.

Thanks

Hi Sebastian,

try checking your spreadsheet locale, perhaps you should use other delimiters than commas. It is explained in details here.

Hi, great article, but I'm still struggling a little with this and am hoping you could help, please! I've created a dropdown with 5 options (Option A, Option B, Option C, etc.). If I select Option A in my dropdown, I want to display multiple cell values (x15) using QUERY. I can get this to work if I pick one option only using this:

=IF(D10="Option A",QUERY('A-Different-Spreadsheet'!E4:E19,'F5))

Picking 'Option A' in the dropdown will return the correct data. However, I can't work out how to chain together an IF statement so if Option B is selected, then I QUERY a different part of Spreadsheet 1, or if I select Option C, it queries a yet another part of the spreadsheet etc.

Any suggestions on how to link these together would be super helpful! Or even being told that it's not possible would be useful!

Hi Jonny,

You will find the answer to your question in the very last paragraph of this article:

IF in combination with other functions

I'm trying to get a function that if a score is greater than 31 it says "exceeds" and turns blue, 22-28 it says "meets" and turns green, 14-21 it says "approaches" and turns yellow orange, and <=13 it says "Does Not Meet" and turns red but having some issues getting it to do all of that at once.

Hello Kris,

you're right, you can't do it all at once.

To make cells return a certain text, you need to build a nested IF in those cells. You will find how to do that in the last paragraph of the blog post above.

As for colours, you will have to create conditional formatting rules with the same IF formulas for each colour.

Okay, I'm still having trouble with the nested IF statements though. Specifically the ones that are between two values like 14-21.

Kris,

to indicate ranges, you are to incorporate AND function like in those examples above:

=IF(A2>31,"exceeds",IF(AND(A2>=22,A2<=28),"meets",IF(AND(A2>=14,A2<=21,"approaches",IF(A2<=13,"Does Not Meet","")))))

Please bear in mind that this logic misses numbers 29-31 and adjust the formula accordingly if needed.

I have a tracking sheet for projects with dollar amounts. Is there a way to do it that if the checkbox is checked (which in Google, it's marked TRUE) in cell A1, then the $ amount listed in cell F1 is added to the Total amount in cell F10. But if the checkmark isn't checked (FALSE), then that's not added to the Total in F10.

All my brain can handle is: =IF(A1"TRUE" ... it's mush after that.

Thanks!

Hello Rachel,

I believe that's the formula you're looking for:

=IF(A1="TRUE",F10+F1,F10)

Receiving a "VALUE" message when trying to add two if statements;

D1 cell input; =SUM(IF(A2="x",E15,0)+IF(A3="x",E16,0); D1 displays "#VALUE"

Cell References; E15=$40; E16=$20

When separating the if statements into separate cells, values appear:

D2 cell input; =IF(A2="x",E15,0); D2 Displays $40 value

D3 cell input; =IF(A3="x",E16,0); D3 Displays $20 value

D4 cell input; =SUM(D2+D3); D4 displays "#VALUE"

Is Google unable to add simple IF statements without retrieving a "#VALUE" message?

Hello Nick,

Sorry, everything works on my end. Make sure you format all source cells correctly and add one more closing bracket to the very first formula.

Also, you may want to try the SUMIFS function instead.

Hi, I am a teacher and I'm trying to create a spreadsheet to track whether or not my students' test grades meet certain criteria.

I have typed: =if(H7>=65,"yes","no)

But when I hit Enter, the entire, exact formula is what shows up in the cell (not the 'yes' or 'no').

Please disregard my previous email. A simple 'refresh' did the trick. Duh!

Is there a way to write a function that keeps track of the sum of certain values in a column, if different column values meet a certain criteria into one cell.

I have a chart to keep track of certain expenses for my building business.

In one column it gives the "type of material" and then in another column it has the purchase amount. The type of materials change(ie: plumbing, framing, kitchen), so i need to get the sum of only certain values.

Materials Type Purchase Amount

Plumbing $20.00

Trim $10.00

Plumbing $20.00

Is there a way on one cell to find the sum of only the plumbing total?

Disregard previous question.

Hello Joshua,

Just in case you found the solution with the IF function, I'd recommend looking into SUMIF or SUMIFS :)

Hey,

I am trying to design a grade calculator for my students but I am stuck!

I have made a dropdown with YES or NO in one column for certain units of work, and I need the formula for if they put 'yes' in all boxes it needs to show distinction, if they put yes in some and no in others its a merit or pass (dependence on answers to yes and no's)

Anyone have any answers? I have been googling for a while!

Any help would be ace!

Hey Jacky,

I'm afraid your task is not clear. With those little details you provided, I can only suggest how to start the formula: =IF(COUNTIF(A2:A10,"Yes")=9,...), where A2:A10 is a supposed column with the answers.

What distinction should be seen and where if all answers are 'yes'? Also, "its a merit or pass (dependence on answers to yes and no’s)" please specify when it should be 'merit' or 'pass' exactly.

I'm looking for a formula for this

A B C

1 Red 50 Lunch

2 Black 72 Dinner

3 Blue 83 Lunch

4 Orange 17 Lunch

5 Green 23 Dinner

Totals

8 Lunch

9 Dinner

I want the SUM of Lunches that is in column B to show in B8 (150) and the SUM of Dinners that is in column B to show in B9 (95). How would I write out the formula? This is just an example of what I want to do, it's really for different bills (A) that are in different categories (C). The amount is in column B. I basically want the B's to add up by category below in row 8 and 9.

Hello Jeremy,

please refer to blog posts about SUMIF and SUMIFS to solve your task.

I am trying to use an if statement based on a data validation "yes" or "no" If the answer is yes the result will be a hyperlink (in a label) which the user can click to. If the the data validation is blank then the result is blank. If the answer is "no" I wish to return a text response. I am getting the hyperlink by getting the link from a different area in the sheet.

Can anyone help as its driving me mad!

Hello Lloyd,

Supposing your data validation is in A2, the link is in B2, the formula you need can be as simple as this:

=IF(A2="yes",B2,IF(A2="no","your_text_response",""))

I am trying to change the color based on a column with a dropdown to color a gantt. Any help would be great

=IF(F4 ='Ministry', SPARKLINE(if(OR(today()D4),{int(C4)-int($C$2),if(C4=D4,1,int(D4)-int(C4))},{int(C4)-int($C$2),today()-int(C4),1,int(D4)-today()}),{"charttype","bar";"color1","white";"Light Blue"}),

IF(F4 = 'Project',SPARKLINE(if(OR(today()D4),{int(C4)-int($C$2),if(C4=D4,1,int(D4)-int(C4))},{int(C4)-int($C$2),today()-int(C4),1,int(D4)-today()}), {"charttype","bar";"color1","white";"Light Blue"}),

IF(F4 = 'Cerner/AscTech',SPARKLINE(if(OR(today()D4),{int(C4)-int($C$2),

if(C4=D4,1,int(D4)-int(C4))},{int(C4)-int($C$2),today()-int(C4),1,int(D4)-today()}),{"charttype","bar";"color1","white";"Light Blue"}),)));"max",int($D$2)-int($C$2)}

Hello Anthony,

You may find this YouTube video tutorial useful: Creating a Gantt Chart in Google Sheets. The author starts introducing colors at 9:10.

Hi there thanks for the amazing article! it's helped a lot.

I currently am having an issuewith my if + vlookup statements.

My current string:

=if(B1=5,=VLOOKUP(I1,data!A:D,2),VLOOKUP(I1,data!A:D,3))

If B1=5 I want it to look up I1's data in Collumn 2

If B1=6 I want it to look up I1's data in Collumn 3

If B1=7 I want it to look up I1's data in Collumn 4

example

B1 I1 J1

5 10 =look at number 10 Collumn 6 and give that back

Hi Caleb,

If I'm getting your task clear, you should nest several IFs for multiple conditions like this:

=IF(B1=5,VLOOKUP(I1,data!A:D,2),IF(B1=6,VLOOKUP(I1,data!A:D,3),IF(B1=7,VLOOKUP(I1,data!A:D,4))))

My question is:

I have two drop downs of salespeoples names (one in column K and column L) and I have another column (AF) that calculates how many times a salespersons name is chosen in column K. I want to know if there is a way that if "James" is used in column K and no name is in column L it produces a numerical count of 1 next to James' name in column AF. But if "James" is in K or L and "Kevin" is in the opposite one, can it produce a .5 for each person in column AF?

Hello Kevin,

For us to get a better understanding of your case, please share a

small samplespreadsheet with us (support@apps4gs.com) with 2 sheets: a plain example of your data and the result you expect to get. I kindly ask you to shorten the table to 10-20 rows.When sharing, make sure the option 'Notify people' is checked.

Note.We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying here.We'll look into the file and try to help.

I added you to the spreadsheet in view mode only. As I stated, I have sales reps in Column K and L (sometimes). I want it to populate in column AF with the follow criteria:

If a rep is in column K only then it counts 1 in column AF. If there is a rep in Column K and L then each rep gets a .5 in column AF.

I hope this makes sense.

Thank you for sharing the file, Kevin.

There's one thing I need to clarify. Should the formula give .5 to each occurrence of a rep in both columns

in the same row only? (And at the same time give 1 to all other occurrences when there's no name in the same row in column L?)Or should it give .5 to

alloccurrences of a rep in column K if the same name appears anywhere in column L at all?If their name is in Column K and there is no name in colomn L of the same row, then they get a 1. If their name is in either K or L with another name in the same row, then both reps get a .5 for that row. Each row should have either a 1 assigned or 2 - .5's assigned depending on an entity in column K or L. I hope that helps.

Kevin,

Try this one in AF4 and copy it down the TOTAL column:

=(COUNTIFS($K$3:$K,"="&$AE4,$L$3:$L,"="&""))+

((COUNTIFS($K$3:$K,"="&$AE4,$L$3:$L,"<>"&""))/2)+

(COUNTIFS($L$3:$L,"="&$AE4,$K$3:$K,"<>"&"")/2)

Hmmm. That didn't work. It gave the rep a full count if they were in column L. On the sheet I shared with you, look at row 31. It has Aaron in column K and Adam in column L. In column AF it should give each of them a .5 count next to their names. In row 32 it has Evamarie in column K and no one in column L. This should give Evamarie a 1 count in AF next to her name. Let me know what I'm doing wrong....

Kevin,

I'm sorry, I don't see the formula I provided in your sheet so I don't see the result you're getting.

This formula checks both columns at the same time. If only K or L is filled on the row, the name gets 1. If both columns are filled, both names get 0.5. Then all these numbers are summed in your Total next to the name of the interest.

If this is not what you expected - my apologies if I get something wrong from your description. I kindly ask you to create and share a small example table that would illustrate the exact result you're trying to get.

You know, It is EXTREMELY possible that it is my fault. Which quotations in your formula does that reps name go in?

Natalia,

I just gave you edit permission. Please help, if you can.

Thanks again

Thank you, Kevin,

I'll look into it and get back to you by email as soon as possible.

Maybe someone can figure this out; I'm trying to modify a template I use to keep my checking balance by adding a column that can show me my balance based on what check have cleared, and a balance showing me what my actual balance is, including checks that may not have yet cleared. So, column D is a flag to tell me whether a check has cleared or not that I manually set. If that "x" is not true, or present, I'd like column H to show what is showing in the bank. Any suggestions are appreciated. https://docs.google.com/spreadsheets/d/1CDpexKUMuTcJEgCDXcq1XREFOoyjOsSyfBdISdiStNQ/edit#gid=0

Hello Michael,

I looked into your file and adjusted the formula in column H for you based on how I understood your task. Try putting the below into H5 and then copy the formula down:

=IF(D5="x", $G$4-SUM($E$5:E5)+SUM($F$5:F5), G4)

If that's not really what you're trying to achieve, please describe the task and the desired calculations in more detail.

hi , my condition is if in priority column value is "high" or "medium" AND in status coloumn value is "open", then "to do" must be printed else "hold" must be printed

how to do this ?

columns are priority(values="high","medium","low"),status(values="completed","open")

Hi Aswin,

I believe this is the formula you're looking for:

=IF(AND(OR($A2="high",$A2="medium"),$B2="open"),"to do","hold")

Hello there

Hi I have a google sheet with guest check in and check out.

my problem is I have the following function for timestamp when a value is entered in column A and gives me current timestamp in column C:

//----

function onEdit(e) {

var ss = SpreadsheetApp.getActiveSheet();

var r = ss.getActiveCell();

//1.Change 'Sheet1' to be matching your sheet name

if (r.getColumn() < 3 && ss.getName()=='Daily Guest Info') { // 2. If Edit is done in any column before Column (I) And sheet name is Sheet1 then:

var celladdress ='C'+ r.getRowIndex()

ss.getRange(celladdress).setValue(new Date()).setNumberFormat("MM/dd/yyyy hh:mm");

}

};

I also have a check box at K5 to be checked at the time of payment so I have a timestamp on J5 with a formula below entered in J5

=if(K5,NOW(),"").

However, if I already have a cheked button on K6 and have a timestamp in J6

whenever I enter a value in A1 all the timestaps stored in J6 change together.

I know this is caused by the formula =if(H5,NOW(),"")

do you have a better formula or function for this issue

Thanks Mack

Hello Mack,

I'm sorry but NOW is the only function that returns a timestamp. It's a volatile function, so it recalculates itself in all cells each time anything is edited in the sheet. Thus, the standard formula is not an option here, I’m afraid. You need to use scripts to solve the task.

Since we don't help with scripts, you may try to find the solution here – an overview of Google Apps Script with a lot of helpful content and links:

https://developers.google.com/apps-script/overview

I wish I could help you better.

Hi,

Let me explain the problem:column A is the asset of Jack & column B is the asset of Pot and column C is difference between A & B.

=IF(A2>B2, "Jack has C2 more asset than PoT ","Pot has B2-A2 more asset than Jack").

Is it correct???

How to add both statement & cell value in same function.

Hi,

An ampersand will help you concatenate strings and cell references:

=IF(A2>B2, "Jack has "&C2&" more asset than PoT","Pot has "&B2-A2&" more asset than Jack")

You will find more info on this and other ways in this blog post: Concatenate in Google Sheets

Hello again Natalia,

I have another formula question that is similar to what you helped me with last time. I am sharing the sheet with you now.

What I am needing: I need a count of 1 or .5 next to "TOTAL =" depending on if their is a "SPLIT WITH" or not. If there is a persons name in the "SPLIT WITH" column then it needs to count ".5" and "1" if not.

Thanks

Hello Kevin,

Thank you for getting back in touch!

I've got your file. I'll look into it and reply as soon as possible. Thank you for understanding.

Kevin,

The formula is actually the same as before. You just need to change the ranges and remove one excess condition from the previous formula:

=(COUNTIFS($C$3:$C$52,"<>"&"",$G$3:$G$52,"="&""))+((COUNTIFS($C$3:$C$52,"<>"&"",$G$3:$G$52,"<>"&""))/2)

Copy and paste it to your E1.

Though I'm using COUNTIFS (count based on multiple criteria), you can get to know how COUNTIF works (count by one criterion). The functions are similar and you will understand how these formulas work:

COUNTIF in Google Sheets

How would I nest more than three if statements in this formula?

=if (h2 = 3560,44190),if(3570,44290),if(3580,43390),if(3590,43490),if(3600,43590),if(3610,43690),if(3620,43790),if(3630,43890),if(3640,43990),if(3650,44090),if(3660,44190),if(3670,44290),if(3680,44390),if(3690,44490))

super simple replacement here but it errors after 3. not sure if I should be using a different method here.

Hello Joe,

Here's how the correct syntaxis should look:

=IF(H2=3560,44190,IF(H2=3570,44290,IF(H2=3580,43390,IF(...,IF(...)))))

Or you can use the IFS function instead:

=IFS(H2=3560,44190,H2=3570,44290,H2=3580,43390,...)

Hi.

How do I work out a formula if I want to charge 50p for every unit after 50 (the first 50 are free)?

Thank you

Hi Mark,

For me to be able to help you, please describe how your data is arranged. Thank you.

Hello Natalia, thank you for your help

I try to create a google sheet which picks up by name (drop down menu) a multiplication.

example - if cell A2 = x then Cell B2*'Another Sheet'!A2' if cell A2 = y then B2*'Another Sheet'!A3' etc

I have created a short formula with 3 items but I have 100+ items, might be an easier way or simpler? Would be a lengthy formula with a few brackets at the end :) :)

My formula

=IF(A2="x",B2*'Base Sheet'!B2,IF(A2="y",B2*'Base Sheet'!B3,IF(A2="z",B2*'Base Sheet'!B4)))

Thanks in advance

Hello,

Try using IFS instead:

=IFS(A2="x",B2*'Base Sheet'!B2,A2="y",B2*'Base Sheet'!B3,A2="z",B2*'Base Sheet'!B4,...,...)

You will still have to list every condition and its outcome, but no nested IF is required.

Alternatively, you could try building scripts, but we don't help with that. You may try to find a solution here - an overview of Google Apps Script with a lot of helpful content and links:

https://developers.google.com/apps-script/overview

Hope it'll help.

Hello, I'm trying to condition a formula to pull info from two columns and then recognize which country it belongs to and from there show one number. So far I'm only able to pull from the first option "US" and if it pulls "CN" shows up as blank.

=ARRAYFORMULA(IF(B5:B50="","",(IF (C5:C50="US",(Items!B2:B50),(IF(C5:C50="CN",(Items!C2:C50),(IF(C5:C50="UK",(Items!D2:D50)))))))))

Hello Andie,

I'm sorry but I'm a bit confused by the formula without seeing the data structure. Would you mind creating a small example of your data and share it with us (support@apps4gs.com)? I kindly ask you to shorten the tables to 10-20 rows. Please include your formula there as well. I'll look into it and see what may be wrong.

Note.That account is for file-sharing only, please do not email there. Once you share the file, just confirm by replying here.Most helpful and clear explanation on the internet. I seem to have encountered a problem. The cell I'm referencing has drop-down selector. The value of that cell from my view is whatever value has been selected. But the true statement never returns as true but only as false. =IF(E7="Business Analyst", "BA", "Nope") that only ever returns "Nope" despite that Business Analyst is selected and visible in the cell. Will this just not work or is there some other magic?

Hello Christopher,

Thank you for your lovely feedback!

Please make sure the values in your Data validation are written exactly the same as you put them into your IF formula. I mean you should keep the same text case, spacing, etc. If you're still unable to make it work, please consider sharing a small sample spreadsheet with us (support@apps4gs.com) where the problem occurs. I kindly ask you to shorten the tables to 10-20 rows.

Note.We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.I'll look into it and do my best to help.

Hi, I need help correcting this formula which refers to a VLOOKUP before an IF command with an "and" condition:

=IF(VLOOKUP(B69,$B$25:$C$43,2,FALSE)10000,C2560000,1800)))

Hi Therese,

I believe, you just miss the comparison before 10000. If you're looking if the match equals to 10000, it should be:

VLOOKUP(B69,$B$25:$C$43,2,FALSE) = 10000

Replace the equal sign with whatever comparison character suits your case.

Also, there are two excess closing brackets at the very end of the formula.

I am trying to utilize Google Sheets to take a list of email addresses and see if it has ever been seen before.

Emails are listed in Colum C and my string is:

=IF(C2="C2;C1000","YES","NO") however it is returning NO no matter if it has been seen before.

Hello Hayden,

a simple IF is not enough here, I'm afraid. You should incorporate the COUNTIF function to your formula:

=IF(COUNTIF(C2:C1000,C2)>1,"YES","NO")

I have a sheet where data copies from a master tab to a slave where I want some information removed or hidden as it will be shared outside our business. What I would like to achieve is IF it reads (in this case) 4S then the information shows on that second tab, and IF it reads anything else, for example CL, RN or EC then it does not automatically show on the second tab. This is all very new to me so would appreciate if you could advise how I go about this...

Hello Bronwyn,

If I get your task correctly, you should pull data using the QUERY function since you can add conditions for pulling there. I explained how it works in this blog post.

Hi

I am trying to pick shipments from a list whch has columns with several information. I try to fix a correct formula which gives me the result 1 or 0, or True or False or a colour or whatsoever.

Lets say I want the in Cloumn T see the result if either one of the conditions are fullfilled. Aka if one of the conditions is fullfilled should the result be 1/True/Green cell, etc.

First condition: IF C3<=3,99 and D3<=999

2nd condition: IF G3="Yes" and E3<=3

3rd condition: IF C3=6 and D36 and D3>1000 and D3<2500

I do not know what I am doing wrong, but I don't get it together, and with only 2 conditions (which are probably wrong) I get 1 as a result for a row with "yes" in cloumn G, whereas if the data in the column is "no", but it fullfills i.e. the first condition, the result is instead of 1, true? Appreciate your help so much. Thank you!

Hi

I forgot the conditions in the 2nd condition

which is if G3 s "yes" and E3<=3 is kind of missing the part, that if E3=1 should D3<=800, but in case E3=2 should D3<=1600 or in case E3<=3 so should D3<=2400

how is it possible to combine all the conditions in a formula?

Sorry for the unconvinience.

Looking forward to your reply

Kindest regards

Hi Fatos,

I'm sorry but it's hard to get a clear understanding of what you're trying to achieve.

For me to be able to advise you anything, please consider sharing a small example spreadsheet with us (support@apps4gs.com) with 2 sheets: a copy of your source data and the result you expect to get. Please include the formula that doesn't work as well and shorten the tables to 10-20 rows.

Note.We keep that account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying here.In the meantime, please look through the last part of the blog post more closely. It describes how to enter multiple conditions to your formula.

Hi Natalia

Thank you sooo much, I will send you an exempel

Kind regards

Hello

I've shared the file now

thanks again!

Hello Fatos,

I'm sorry, I can see no files shared by you. Please follow these steps to share the file correctly:

Sharebutton at the upper right corner of the Google spreadsheet.Done.The file will then appear in the

Sharedsection of our Drive. For more details on sharing the files, please turn to this help page. Thank you.Hi,

so sorry, I'll try to send it again. Sorry for the unconvenience... In the meantime did I manage to find I formula which appears to work. But I am not sure if that is the best way to do it. I'll try to send it now

Thanks a lot & sorry again

Hi there

1 - I've shared as requested a simple version of the file. hope it worked this time

2- does anybody know how I can create an automated list of tabs/sheets in the same worksheet. Its a schoolproject; a (daily) spreadsheet with over 70 tabs/sheets and we need to have a summary-tab (we need to "analyze" data for a period of 120 days, and have no knowledge or info on coding etc)

All kind of help is greatly appreciated

Thank you!

Hi Fatos,

Thank you, I've got the file. I'll look into it as soon as possible.

As for your second question, we have an article describing different ways of combining data from multiple sheets, make sure to check it out.

Hi,

Thank you again.

Re my second question; I need to get a list of the tabs in another (summary tab) tab of the same spreadsheet. Like an index, which I want to use to create a table and "extract and summarize" information, so the names of these tabs are going to be the rows in this table. I was not able to find the funtion to use for this

Kindest regards

Hi Fatos,

I looked into your file. I created an additional column Q, named it Ablebits, pasted the formula there, and copied it down the column. Here it is:

=IFERROR(IFS(AND(M2<=2,4;K2<1000);1;AND(AND(M2>2,99;M2<=6);K2<=50);2;AND(M2="pall";AND(N2>0;N2<=3);K2/N2<=800);3);0)

The formula returns 0 instead of the error when there's no match to any of your conditions. Also, the column cells turned green due to the existing conditional formatting, but I'm not going mess with that. Please adjust your conditional formatting accordingly.:) Hope the formula solves the first task for you.

As for the second one, I appreciate the additional info. But I'm afraid there's no easy way to avoid entering each sheet name manually. I'd advise you to search for a solution here - an overview of Google Apps Script with a lot of helpful content and links: https://developers.google.com/apps-script/overview

I wish I could help you better.

Thank you for all your help !

Have a great day...

Fatos

I've used an IF statement to populate a column with a '1' for a true value and a '0' for a false value. In another cell, I want to sum all of the '1' values but the sum formula isn't picking them up. Is it possible to sum up all of the 1 values returned from all of my IF statements?

Hello Harry,

Can you please share the exact formula you're using to sum those numbers returned by IF?

Hi Natalia,

I managed to resolve myself thanks. The problem was I has quotation marks around the true and false values which was stopping them displaying as values that could be summed.

My original statement was =IF("B3=Test","1","0")

And changing it to =IF("B3=Test",1,0) has fixed it.

Hi Harry,

Thank you for the update. Good to know you managed to fix the formula!

Double quotes are used for text strings. When used with numbers, they turn numerics into the text and prevent them from being calculated.

Hello,

Can you please help?

I would like a formula to do the following. I have three columns. Column 1 are a list of dates, Column 2 is also a list of dates, Column 3 is a list of number. I want a sum of column 3 for only the following cells. Cell 1 has a value and Cell 2 does not have a Value.

Hello Pierce,

You should use the SUMIFS function to do that, it is described in this blog post. Please pay closer attention to a part explaining how to sum one column if cells in other columns are (non)-blanks.

Hi,

I was wondering if I could receive some help with a formula. I'm trying to figure out how to create a scenario where I can view the commission rate (it differs by company) by company?

It would be such that column B (where the companies are) would dictate the percentage of commission that's taken, and the percentage is taken from the sum of columns K-M. I want to create a commissions column in column N, and would like for the formula to run the length of the column.

The % taken is such that Company B has 33% taken, Company IT, S-Corp, and AA has 28% taken, and every other company has 25% taken. I've linked the sheet below for easier viewing:

https://drive.google.com/file/d/1SZ3U1SXiorYnsTt9566ymPWQDX734CY_/view?usp=sharing

Thank you!

Hi Amanda,

Thank you for the description and the file.

To calculate the commission rate by company, use this formula in B7 and copy it down the column:

=IF(B7="","",IF(B7="B",33%,IF(OR(B7="IT",B7="S-Corp",B7="AA"),28%,25%)))

To deduct the percentage from the total of columns K-M at the same time, here's another formula:

=IF(B7="","",IF(B7="B",SUM(K7:M7)-33%,IF(OR(B7="IT",B7="S-Corp",B7="AA"),SUM(K7:M7)-28%,SUM(K7:M7)-25%)))

Hi,

This is for a construction project...looking to put how many people will be on a job for each day. Using Google sheets....

I have the dates in the main cell as follows:

P5 is the date in question 5/26/2020

H6 is the start date 5/26/2020

I6 is the end date of the project. 11/9/2020

once i run this formula i can make all cells (for this job) a specific color Perfect, and starts on 5/26/2020 stops on 11/9/2020.... :)

=AND(O$5>=$H6,O$5=$H6,P$5<=$I6,D6*1,"")

this does give a blank for the false but how do i get the true statement to read the D6 cell?

Is there a way to get this number into each cell for the job by not typing it in. Seem so simple yet i am at a loss.

any help would greatly appreciated

Thank you

Hi,

figured it out finally!

=IF(O$5>=$H6,if(O$5<=$I6,$D6,""),"")

Patience and time solves everything!

Thank you for the follow-up, Scott,

Good to know you figured the solution out :)

I'm trying to make a formula but I can't seem to find the answer to what I try to achieve.

What i'm trying is to automatically subtract a percentage on an array when the cell next to it contains a name.

=IFERROR(IF($J4:$J = "name", $H4:$H*0.85, $H4:$H*0))

So if a cell in array J contains a "name, the cell left of where the name is substracts 15% That my formula is wrong, is for sure! But where? Thank you in advanced.

Maybe an Example sheet is easier:

https://docs.google.com/spreadsheets/d/1CmkI8pyqC0oHKVXH0JIRL_yb9BbasbrIbAvy2NO-hio/edit?usp=sharing

Hello William,

Though your formula works on my side, I'd adjusted it a bit:

=ARRAYFORMULA(IF($J4:$J="name",$H4:$H*0.85,IF($J4:$J="","",0)))

ArrayFormula auto-populates the entire column with the formula while the second IF returns an empty cell for those rows where column H is not yet filled with data.

I tried to look into your file, but I can't access it. If you still need my assistance with the formula, please share an editable copy of your spreadsheet with us directly: press the Share button at the upper right corner of your spreadsheet and enter support@apps4gs.com.

Note.We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying here. Thank you.I'm trying to black out cells if the cell prior has N/A from a drop down list. This would also need applied to the entire column.

Thank you in advance!

Hi Kim,

I'm sorry but your task is not clear.

For us to be able to help you, please share a small sample spreadsheet with us (support@apps4gs.com) and include two tabs there: 1 - your source data, 2 - the result you need to get.

Note.We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here. Thank you.Hello Natalia, I'm need to have multiple IFS and am having trouble with the formula. I need a cell to populate a dollar amount based on a percentage range in another cell. If the percentage is between 11 - 11.99 = $125, 12 - 12.99 = $250, 13 - 13.99 = $375, 14 - 14.99 = $500, 15 or more = $750. How can I write this formula?

Thanks

Hello Kevin,

When it comes to IFS, you simply need to alternate your criteria with its result. The example is illustrated here.

Assuming the percentage starts in A2, here's how the formula should look like:

=IFS(AND(A2>=11,A2<=11.99),125,AND(A2>=12,A2<=12.99),250,AND...,etc,etc)

PERFECT! Thank you!!!

If I want to past a formula into consecutive vertical cells but want to keep ONE of the sections of the formula the same, is this possible? When I paste it, the L30 goes to L31 then to L32, etc., and the D1 goes to D2 to D3, etc. I'm wanting the L to stay at 30 and the rest to move with each line.

e.g., If I paste =IF(L30>=12.5,D1*25%,D1*20%) in A1, and =IF(L31>=12.5,D2*25%,D2*20%) in A2, and =IF(L32>=12.5,D3*25%,D3*20%), etc., etc.

Hello again, Kevin,

Yes, it's possible: proper cell references will do that for you. Please have a look: types of cell references.

That is great! Thank you!

My pleasure, Kevin :)

My business focuses on retail, contractor, wholesale sales, but also includes stock. We do batch ordering and a majority of what we order is already pre purchased. I am trying to separate in one column a letter for each: R, C, W are what we are using as our definers. We want a count from each grouping, and also a percentage. Please help....

Hello JB,

If I understand your task right, you may find the following blog posts helpful:

Split text to columns in Google Sheets

Google Spreadsheet COUNTIF

Google Sheets percentage formulas

Could you please help me with a formula,

IF the cell is EE then the number is 3, if the cell is ME then the number is 2, if the cell is NW , then the number is 1.

Hello Rogelio,

Assuming your cell is A2, here's a formula you can use:

=IF(A2="EE",3,IF(A2="ME",2,IF(A2="NW",1,"")))

I would like to total the prices (Column D) of rows assigned in Column G. I have Data Validation to make a dropdown of values "1-4" (column G). I want to sum the values of all group 1 column D's, all group 2 column D's, etc to another cell.

Hello Dave,

To total numbers based on records in other cells, you need to use the SUMIF function. We have a nice tutorial about it, please take a look:

SUMIF in Google Sheets

How do i make the cell that has the formula in it blank until it populates? at the moment it has FALSE until the other cell is written in ?

Hello Tania,

Use a pair of double quotes as an argument to indicate a blank cell, like this:

=IF(A2="West","Yes","")

I created an order form for my craft shows and want the prices to populate when i select each item. I figure its an If:Then type of situation but Its been a long time since I've worked on things like this and could use some guidance

Column B= Item Description

Column D= Price

I made a drop down list for column B to show all the items I currently have in stock. I also have a drop down list in Column D and can manually click on each price. I would love to figure out the proper way to bypass this.

Example: Items- lollipop and chocolate are $1 each. bubbles, reindeer food, lip balm, and hand sanitizer are $3 each.

Do I need to say something like If B=lollipop or chocolate then D=$1???

Hello Jessica,

Yes, you will need to use the OR logic in your nested IF, like this:

=IF(OR(B2="lollipop",B2="chocolate"),1,IF(OR(B2="bubbles",B2="reindeer food",B2="lip balm",B2="sanitizer"),3,""))

The following parts of the blog post above will help:

Hi, I am looking for a formula to help with our staff holiday tracking sheet. We have a new policy where holiday entitlement run from Birthday to birthday or each staff (we used to run the from 01/01 to 31/12) As it is our first year doing this it will have run from 01/01 until their birthday this year and then start again. I need a formula that will: one, pull through the start date of the holiday year and the end date and two, change date after the birthday has passed this year. I also then need one that will calculate how man holiday days are accrued between the start and end date of the holiday year.

E2 - Staff name

E3 - Holiday year start date (Need formula for this)

E4 - Holiday year end date (Need formula for this)

E5 - Holiday entitlement (need formula)

A separate page will have all names and birthdays.

Sheet name is Bdays - A1 has the bday date in (12/02/2021)

Any help would be much appreciated. Im unsure if i need a Vlookup, or IF or maybe both?

Hi Michaela,

Do you intend to use a drop-down list of names in E2 and want other cells to change accordingly or you'd prefer having each staff member's info displayed at the same time?

For me to understand your task better, please share a small sample spreadsheet with us (support@apps4gs.com) with an example of your data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.

Note.We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.I'll look into it and try to come up with a solution.

I am trying to create a IF formula where IF cell P2 = Y then "Need to be invoiced" is entered into Q2, however, if R2 has a date inputted into it, Q2 will have "Invoiced" into it. I am having trouble as I get one or the other to work but not together.

Hello Stacy,

Try this formula:

=IF(ISDATE(R2),"Invoiced",IF(P2="Y","Need to be invoiced",""))

That formula worked with the exception when P2 is yes but there is no date in R2, the Need to be invoiced did not populate. do I need either an OR or And function in there?

Nevermind - I figured it out! P2 uses the word "Yes" not just a "Y" as soon as I fixed that it worked.

Thank you for your help!

Thank you for letting me know, Stacy.

Good to know you found the solution!

Want to set True if cell contains a specific word. How to do that?

for ex: Let's say cell F4="Rest of the world". Then, if "Rest" contains in the cell F4, set True, else False.

Hello Pritam,

Try this formula:

=IF(ISNUMBER(SEARCH("rest",A2)),TRUE,FALSE)

I am working with schedules, for Example: 0300-1100,1100-1900,1900-0300 three different shifts, How can I make all 0300-1100 scheduled employee names appear in a group?

Thank you

Hello Jake,

Please create a small sample spreadsheet with 2 sheets: (1) example data, (2) the result you want to get, and share it with us: support@apps4gs.com. I kindly ask you to shorten the tables to 10-20 rows.

Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment.

I'll look into it and try to come up with a solution.

Hi Natalia,

I just shared the spreadsheet to that email. Thank you.

Hi Jake,

I've got the file, thank you.

Please look at B16, B19, D16 and D19. I used QUERY to return the required records. You just need to create similar formulas for other days.

The same can be done (with and without formulas) with our Multiple VLOOKUP Matches add-on.

Also, B3:I12 contained some excess spaces that prevented from getting the correct results. I trimmed them with our Remove spaces tool.

Hope this helps!

Hi

I am trying to populate a yearly expense dashboard I created. I have sheets for every month. In column B (expense category "Grocery") and column D ($ amount)

I am trying to add all values in Column D ($ amount) that have "Grocery "in column B to populate a monthly total for groceries on my dashboard.

I am tired of sorting the column by category and redoing the formula to calculate totals every few week.

Is this possible?

Hi Natan,

I'm sorry I'm afraid I need more details on your task to be able to suggest anything.

Please create a small sample spreadsheet with (1) a couple of sheets with the example data and (2) with a sheet showing the result you want to get. Then share this file with us: support@apps4gs.com. I kindly ask you to shorten the tables to 10-20 rows.

Note.We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment.I'll look into it and see what may help.

Hi I have sent this.

Hi Natan,

Thank you for sharing a sample. You need to use the SUMIF function for the task. I entered the correct formulas to the first 3 columns on the Dashboard sheet so you could see what to do. If you need more details on the SUMIF function, we have a cool blog post about it, feel free to look through :)

Hi everyone,

I've read thru many comments but still not understanding exactly how to approach my specific calculation need.

My need is specific to the Canadian Recovery Benefit, which has a cap of 18K for my situation. I am allowed to make up to 38K in salary/profit without penalty or obligation to reimburse any portion of the 18K. But for every dollar made over 38K that I make (with the exlusion of the 18K govt benefit) I would need to reimburse half of the benefit received (0.50) until I reach the threshold where I am obligated to reimburse all of it (which if my calc is correct, is 74K total salary).

ie. If I make 38K or less, and also receive the 18K govt benefit, I am not obligated to reimburse any of the benefit. I am naturally obligated to pay the income tax on the 56K total.

If I make 48K (exluding the govt benefit), I am obligated to reimburse 5K of the 18K govt benefit received. 0.50 cents per every dollar of the 10K I am over the limit.

If I make 74K (excluding the govt benefit), I am obligated to reimburse the full 18K govt benefit. (74K being the threshold of 38K, plus 2X the 18K maximum benefit)...

I'm trying to generate a sheet in which I can enter the amount of money I have made to date into one cell, and have the sheet tell me 1) If I am over the limit - this calc is easy, and, 2) tell me how much I need to reimburse in the even that I make of over the 38K.

I am assuing this is not that hard, but the formulas for it are above me.

Here's as far as I got:

=IF(I41=">$38,000.00","No Pay", "Pay") this would simply alert me if I had crossed the threshold and needed to pay. Its not very elegant, but for the first part seems to work.

But what formula (or addition to the original) would tell me how much of cell I41 would be payable if it crossed the 38K...? Iaking into account the .50 of every 1.00 more made... I hope this makese sense.

Any help or tips to making this work are greatly appreciated. Thank you!

Bob

(a rudimentary preview of the issue)...

https://www.dropbox.com/s/zzbqe32xac09mcb/Screen%20Shot%202021-03-11%20at%209.59.16%20PM.png?dl=0

Hi Bob,

Thank you for the detailed description and the screenshot. If I understand your task correctly, the following formula should do the trick:

=IF(I41<=38000,"No Pay",IF(AND(I41>38000,I41<74000),(I41-38000)/2,18000))

if the two column cells are red in colour then write 0.5 in another column how to write a formula can you please tell any one

Hello Sethu,

There are no standard formulas that would check cell colors. We have a special tool for that: Function by color. You can use it to count colored cells. However, it can’t return the value you need based on colors. You will need to build the simplest IF formula afterwards to return 0.5 if our add-on returns 2.

So I'm trying to use a pretty basic IF OR formula in Google Sheets: =IF(OR(I299),"I2","").

The trick is that for my value if true, I want to use a cell reference, not just a static value / word. When I use just the IF statement without OR, it accepts I2 as a cell reference, but when I make it an IF OR it no longer recognized my "value if true" as a cell reference.

Is there anyway to adjust that?

Hello Natalie,

If you want I2 to be treated as a cell reference, you should omit double-quotes. Double-quotes make values text strings.

Also, OR requires some condition, not just a cell reference, and is usually used to list several conditions to see if at least one of them is true. Hence, the correct formula for you should look like this:

=IF(I299=SOMETHING,I2,"")

If that doesn't really work for you, please describe your task in detail and I'll help with the whole formula.

I'm trying to have a cell automatically round up no matter if the value is less than .5 or not. I need the value to round up always. So for instance if the value is 2.35, I need it to round up to 3.

Thank you!