I just tried your first and second method but it wasn't working. The formula wasn't recognizing cells after the condition. So instead of "," I've put ";" and it worked. It gives =SI(E6"";E6-C6;""). Maybe an update so if people face the same problem they know how to fix it. Anyway thank you for your video, I wouldnt find this without your base.
Hi @lilpalmed1482, Thank you for your comment. The use of as argument separators in spreadsheet formulas in Google Sheets is influenced by regional conventions. In most , while are used in some countries, , as argument separators. When you go to what country is it listed? I have the United States listed, so commas are used. Thank you for bringing this to my attention, as I am sure others who view the formulas in other regions may come across similar issues. Cheers! 🍀
Great video. Thank you. Question for you if you don’t mind. I’m struggling to write a function for this problem. Example: F4 needs to equal the value in I4, unless K4 has a value, then F4 needs to equal K4?
Hi Robert, Try this in cell F4: =IF(K4"", K4, I4) If K4 has a value (not empty or the sign), then the formula returns the value in K4. If K4 is empty, then the formula returns the value in I4. Let me know if that works.
Thank you for your tutorial...but if i have 2 different cell in which value is dependent, Like in second option, if b19 and c19 both are empty then show d19 blank What will be the formula for it..?
Sorry, I missed your comment. You could try something like the below...using IF(AND) so both conditions have to be met in order for the d19 item to be blank. Replace the "Value if not empty" with desired action if either of the b19 or c19 has value. See if that works. =IF(AND(ISBLANK(B19), ISBLANK(C19)), "", "Value if not empty")
I have a freeze row on top of each column with a title for the column. If a cell is blank below that column, I want to be able to change the color of the that cell to indicate that there is a blank cell in the below or more than one. There aren't supposed to be any empty cell. If there is one, a person is supposed to fill in with data. A change in color on that top cell allows the data entry person to know there is an empty cell and enter data as needed. I tried conditional formatting, but it changes the color of that specific cell that is empty, and I want to only change the color of that top cell on top. Thanks for the other reply.. I appreciate it. I guess this could be achieved with a combination of a formula and conditional formatting, but I am not sure how.
How can I apply this to ranges of potentially blank cells? When I try =IF(BR3:BR36="","",=COUNTA(BR3:BR36)) Then I get an error and the calculation for actual entries doesn't happen. I also can't put parentheses around the first range expression.
Hi @markloundy6720, To count the non-blank entries in the range BR3:BR36, you can use the . Try the following formula: This formula checks each cell in the specified range for non-blank values and then counts them using COUNTA. Let me know if that doesn't work! Cheers!🍀
I have a sheet that imports data from 2 different sheets through import range formula. The data appears in A1:A10 from one sheet and B1:B:10 from the other. We want to be able to type in any field without affecting the formula on the first row, which is import range. Is there a way of doing that. We want to be able to write over cells that are left blank, but with a formula on top we can't. Is that even possible?
Hi @miguelate, If I understand this correctly, you can do the following. First, so select the cells with the important range formulas and choose “ Second, create a new range (e.g., C1:C10) where you want . Apply data validation to this range to ensure valid data can be entered, by selecting it and going to and setting the criteria. Third, create a combine the data from the import range and user input. For example, =IF(ISBLANK(C1), A1, C1) in D1 and drag it down to D10, or any other iteration or what you want it to be. I hope this helps you. Cheers!🍀
I have formula IF=(F6>=(TODAY(),"Not Due","Passed Due") but I'm trying to make it stay blank if there is no data yet with IF=F6="",""'(F6>=(TODAY(),"Not Due","Passed Due") but it comes as an error
Hi @hugogarcia2681, Your formula has 2 small issues (missing comma & if statement). Here's the corrected version, try it and let me know if it works. It worked for me: This formula will check if cell F6 is empty. If it is, it will stay blank. If F6 is not empty, it will then check if the date in F6 is greater than or equal to today's date. If it is, it will display "Not Due," and if it's not, it will display "Past Due." Cheers ( ＾◡＾)っ
I'm trying to pull text from another cell which is =D28. However, if D28 is empty I want a text shown, but whatever I try, it always shows an error. =IF(D28="", "Edit in DEPARTURES section", =D28) =IF(ISBLANK(D28)=TRUE, "Edit in DEPARTURES section", "TEST") =IF(D28"", =D28, "Edit in DEPARTURES section") etc, etc, My goal is to show the text from cell D28 in cell D55, but if cell D28 is empty, I want it to show "Edit in DEPARTURES section" in cell D55. Does anyone know how I can achieve this? Thanks
Hi @WNxRadeon, To achieve your goal of displaying the text from cell D28 in cell D55, but showing "Edit in DEPARTURES section" if D28 is empty, you can use the following formula in cell D55: =IF(D28"", D28, "Edit in DEPARTURES section") This formula uses the IF function to check if D28 is not empty. If it is not empty, it will display the value from D28. Otherwise, it will display "Edit in DEPARTURES section". Let me know if that works!
This should have like a billion likes
Thank you so much! I really appreciate 🍀
Thank you! This is exactly what I was looking for, and you explained it simply enough for me to understand 🙂
So glad @steve4nlanguage it was explained well, which is always my goal. Thank you for your comment.
Omg thank you so very much....ive been watching videos for hours trying to figure this exact thing out....and it all worked !!!!
I am so glad you were able to fix your issue @lizgannon6378. Cheers! 🍀
I just tried your first and second method but it wasn't working. The formula wasn't recognizing cells after the condition. So instead of "," I've put ";" and it worked. It gives =SI(E6"";E6-C6;""). Maybe an update so if people face the same problem they know how to fix it.
Anyway thank you for your video, I wouldnt find this without your base.
Hi @lilpalmed1482,
Thank you for your comment. The use of as argument separators in spreadsheet formulas in Google Sheets is influenced by regional conventions.
In most , while are used in some countries, , as argument separators.
When you go to what country is it listed? I have the United States listed, so commas are used.
Thank you for bringing this to my attention, as I am sure others who view the formulas in other regions may come across similar issues.
Cheers! 🍀
Here is a list of countries that list decimal vs comma as their separator: www.wikiwand.com/en/Decimal_separator
Thanks, amazing and wonderful explication. God Bless you
nice tutor. really help. thanks 👍👍
Thanks Aki! ... Your video was useful to me
I am so glad you found it helpful!🍀
Sounds Gr8 🎉
Great video. Thank you.
Question for you if you don’t mind. I’m struggling to write a function for this problem.
Example: F4 needs to equal the value in I4, unless K4 has a value, then F4 needs to equal K4?
Hi Robert,
Try this in cell F4: =IF(K4"", K4, I4)
If K4 has a value (not empty or the sign), then the formula returns the value in K4. If K4 is empty, then the formula returns the value in I4.
Let me know if that works.
Thank you! That’s what I needed.
Thank you for your tutorial...but if i have 2 different cell in which value is dependent,
Like in second option, if b19 and c19 both are empty then show d19 blank
What will be the formula for it..?
Sorry, I missed your comment. You could try something like the below...using IF(AND) so both conditions have to be met in order for the d19 item to be blank. Replace the "Value if not empty" with desired action if either of the b19 or c19 has value. See if that works.
=IF(AND(ISBLANK(B19), ISBLANK(C19)), "", "Value if not empty")
This video helped me out so much, thanks for the great info!
So glad, Chris! Thank you for the nice comment. ( ＾◡＾)っ
Thank you!
This was super helpful !!
I have a freeze row on top of each column with a title for the column. If a cell is blank below that column, I want to be able to change the color of the that cell to indicate that there is a blank cell in the below or more than one. There aren't supposed to be any empty cell. If there is one, a person is supposed to fill in with data. A change in color on that top cell allows the data entry person to know there is an empty cell and enter data as needed. I tried conditional formatting, but it changes the color of that specific cell that is empty, and I want to only change the color of that top cell on top. Thanks for the other reply.. I appreciate it. I guess this could be achieved with a combination of a formula and conditional formatting, but I am not sure how.
How can I apply this to ranges of potentially blank cells? When I try =IF(BR3:BR36="","",=COUNTA(BR3:BR36)) Then I get an error and the calculation for actual entries doesn't happen. I also can't put parentheses around the first range expression.
Hi @markloundy6720,
To count the non-blank entries in the range BR3:BR36, you can use the . Try the following formula:
This formula checks each cell in the specified range for non-blank values and then counts them using COUNTA.
Let me know if that doesn't work!
Cheers!🍀
I have a sheet that imports data from 2 different sheets through import range formula. The data appears in A1:A10 from one sheet and B1:B:10 from the other. We want to be able to type in any field without affecting the formula on the first row, which is import range. Is there a way of doing that. We want to be able to write over cells that are left blank, but with a formula on top we can't. Is that even possible?
Hi @miguelate,
If I understand this correctly, you can do the following.
First, so select the cells with the important range formulas and choose “
Second, create a new range (e.g., C1:C10) where you want . Apply data validation to this range to ensure valid data can be entered, by selecting it and going to and setting the criteria.
Third, create a combine the data from the import range and user input. For example, =IF(ISBLANK(C1), A1, C1) in D1 and drag it down to D10, or any other iteration or what you want it to be.
I hope this helps you.
Cheers!🍀
@@akistepinska will try and let you know. Thanks
Thanks for this worthy knowledge Miss Aki!
Thank you for the sweet comment Glan ( ＾◡＾)っ
Thanks super helpful!
so glad it helped! 🍀
I have formula IF=(F6>=(TODAY(),"Not Due","Passed Due") but I'm trying to make it stay blank if there is no data yet with IF=F6="",""'(F6>=(TODAY(),"Not Due","Passed Due") but it comes as an error
Hi @hugogarcia2681,
Your formula has 2 small issues (missing comma & if statement). Here's the corrected version, try it and let me know if it works. It worked for me:
This formula will check if cell F6 is empty. If it is, it will stay blank.
If F6 is not empty, it will then check if the date in F6 is greater than or equal to today's date.
If it is, it will display "Not Due," and if it's not, it will display "Past Due."
Cheers ( ＾◡＾)っ
how do I show items with no data in a class interval on a pivot table in google sheets?
I'm trying to pull text from another cell which is =D28.
However, if D28 is empty I want a text shown, but whatever I try, it always shows an error.
=IF(D28="", "Edit in DEPARTURES section", =D28)
=IF(ISBLANK(D28)=TRUE, "Edit in DEPARTURES section", "TEST")
=IF(D28"", =D28, "Edit in DEPARTURES section")
etc, etc,
My goal is to show the text from cell D28 in cell D55, but if cell D28 is empty, I want it to show "Edit in DEPARTURES section" in cell D55.
Does anyone know how I can achieve this?
Thanks
Hi @WNxRadeon,
To achieve your goal of displaying the text from cell D28 in cell D55, but showing "Edit in DEPARTURES section" if D28 is empty, you can use the following formula in cell D55:
=IF(D28"", D28, "Edit in DEPARTURES section")
This formula uses the IF function to check if D28 is not empty. If it is not empty, it will display the value from D28. Otherwise, it will display "Edit in DEPARTURES section".
Let me know if that works!
@@akistepinska Yes, that worked.
Thank you very much! :D
The problem was that I used
=D28 instead of D28.
When I try =IF(ISBLANK(BR3:BR36),,COUNTA(BR3:BR36)) I get an ERROR.
now google sheets uses ; instead of ,
so all old tutorials are confusing 😥
Thank you!