Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



If Condition Formula

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

How can I make a IF formula to pop up a text message if a particular cell is filled by number or text.

What I don't know is how to give a condition on entering anything into the defined cell (numbers or letters) to make the message pop up.

not
=IF(C4= "company ","Name"," ")

I need this defined condition (i.e Company) to be removed & let the formula to accept anything if entered into C4

Please help me

View Answers     

Similar Excel Tutorials

Break out of or Exit Different Types of Loops in VBA Macros in Excel
How to Exit, End, or Break out of loops in Excel. This tutorial includes breaking out of While, Do, and For loops. ...
How to Find and Fix Errors in Complex Formulas in Excel
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to f ...
AND Function - Check if All Arguments are True
The AND function in Excel checks if every argument you put in it evaluates to TRUE.  If everything evaluates to TRU ...
OR Function - Check if Any Argument is True
The OR function in Excel checks if ANY argument in it evaluates to TRUE.  If anything evaluates to TRUE, then the f ...

Helpful Excel Macros

Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Pop-Up Message Box When a Cell Reaches a Certain Value or Contains Certain Text
- This macro will display a message box in excel when a cell reaches a certain value or contains certain text. This means
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Pop-Up Message Box When a Range of Cells Reaches a Certain Average
- This macro will display a message box when the numbers within a range of cells reaches an average of 5. The current mac
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means

Similar Topics







Hello,

My Cell C3 is a numeric value.....I have set conditional formatting to
make the text red and bold when the number is equal or less than 10,000
is there a way I can make cell E3 display a message when the C3
condition is true?? or if not a message is there a way to make a
message box pop up when my C3 condition of less then or equal to 10,000
is true?

how would I do something like this?




My formula is checking for a certain condition. When the condition is met, I want it to return an error message, so the result cannot be summarized into an aggregate number for that column and I can easily spot it later in a pivot table. The complexity is that I am trying to create an custom message showing "NEED PRICE" as a result of that cell. I am trying to use an IFERROR formula, but since the result is still a text ("NEED TEXT"), the cell simply gets skipped over when being summarized and the summary of that column (Total Sales) still contains a number. How can I make the result of the cell read as an error (so it doesn't tally that column), but still have the custom text "NEED TEXT" in there?


Hi,

I am trying to build a code where some message needs to pop out when a certain condition is met.
lets say the condition is that Cells("B12").Value > 500.
This cell has a formula which is the sum of some other cells. As soon as this sum crosses 500, i need a message to pop up using msgbox.

Note: The if conditions will not work for me it checks the cell value only when it runs. As in, if the value in the cell is already greater than 500 at the time of running the code, then only it will pop up the message. i want the code to be such that when it runs, it should put some condition in the cell so that message pops up if the value exceeds 500 even later. (like how conditional formatting works...its not just an if condition there...whenever the given condition meets, the format changes).

Please help me do this.
I searched a lot and found lots of info about conditional 'formatting' but couldn't work my way around this.

Any help would be appreciated.

Regards,
nonie

Hi everyone,
I have a nested IF function I'm having issues with. My workbook is laid out as follows:

Sheet1 contains two drop-down lists, in Cells E2 and F2, and a number field, G2.

The list in E2 may have the following values: "Condition 1" or "Condition 2". The list in F2 may have the following values: "Condition 3" or "Condition 4". Both are text fields.

I need to evaluate for the appearance of the following combinations: ("Condition 1" AND "Condition 3") OR ("Condition 1 AND "Condition 4"), ("Condition 2" AND "Condition 3") OR ("Condition 2 AND "Condition 4").

The results of the conditions use the value of the free-form number field, G2, and manipulate it referencing various values on Sheet3. I'm confident the results portion (the manipulations of G2) are correct, it's the formatting of the IF statement I'm having problems with.

I created the following statement to try to accomplish this:

=IF(AND(E2="Condition 1", F2="Condition 3"), G2*Sheet3!C1, IF(AND(E2="Condition 1", F2="Condition 4"),(G2*Sheet3!C1)*Sheet3!F4, IF(AND(E2="Condition 2", F2="Condition 3")(G2*Sheet3!C1)*Sheet3!F2 IF(AND(E2="Condition 2", F2="Condition 4") (((G2*Sheet3!C1)*Sheet3!F2)*Sheet3!F4), 0))))

However, it doesn't work, Excel tells me "The formula you typed contains an error." I've been searching this site but can't seem to find a readily available answer.

I realize I could be way off, or it could be an easy fix -- I'm new to more complex logic in Excel, so I'll thank you in advance for your patience with the newb.

Can anyone give me a correctly-formatted version of that statement, or point me to where I need to look to figure it out myself?

Many thanks,

Rich


Hello,

I am trying to conditionally sum numbers over 52 worksheets based upon up to 3 conditions. One condition is a date, the other condition is a company name. Right now I am using only 2 conditions but may add a third.

I am using an array formula to get the result I need for that worksheet. Here it is:
{=SUM((I4:M4=V5)*(F6:F42=V4)*I6:M42)}

My question is this.....How do I get the results I need for all 52 worksheets in one formula?

All information on all worksheets are in the exact same cell references.

My goal is to have a reporting worksheet that has all 365 days of the year and depending on what company is is in the first cell; all dates have the respective number that for that day that is contained on another worksheet.

I know my description is a little vuage, if I need to clarify, please let me know.


I somehow want to make an error if a certain condition is not met and have a message box pop up to explain the error and then end my macro, but continue if there is no error.

The condition is this: if the number (value) in a particular cell is not equal to the total number of sheets in my workbook then I want the macro to end and display the message: "You need to Press Ctrl+Shift+C."

The value of the particular cell can be found by the following statement:

Sheets("Initial Input").Range("O10").Value

and the total number of sheets should be able to be found with something like this:

Application.Sheets.Count

I was thinking about using an if statement, but I'm open to using any kind of idea.


Hi friends,

MrExcel forum has been of great help in learning the uses of Sumproduct function as I searched many useful threads on this.

Now I am trying to find out how to apply AND, OR, NOT combination for getting a count/sum.

e.g.

1. Count/sum, If (condition 1) OR (Condition 2) OR (Condition 3) are met
2. Count/sum, If (condition 1) AND ((Condition 2) OR (Condition 3)) are met
3. Count/sum, If ((condition 1) AND ((Condition 2)) OR ((Condition 3) AND (Condition 4)) are met
4. Count/sum, If ((condition 1) OR ((Condition 2)) AND ((Condition 3) OR (Condition 4)) are met
5. Count/sum, If (condition 1) is NOT met
6. Count/sum, If (condition 1) OR (Condition 2) NOT met
7. Count/sum, If (condition 1) AND (Condition 2) NOT met
and similar ones.

Can anyone put light on general arrangements for AND, OR, NOT combination in Sumproduct function. I could not find any thread on this.

Regards,
Uttam


Can anyone help with this please? The formula I have checks for these conditions:
- to see if there is text is cell C221, and if there isn't then the target cell remains blank. (Working).
- if there is text in cell C221, and cell G221 is empty then the target cell should say "No". (Broken! Returns 'FALSE' value instead of "No"...)
- if there is text in cell C221, and cell G221 contains one of the 5 criteria, then the target cell should say "Yes!". (Working).

Here is the formula - how do I fix the broken bit?

=IF((C221=""),"",IF((G221="Condition 1"),"Yes",IF((G221="Condition 2"),"Yes",IF((G221="Condition 3"),"Yes",IF((G221="Condition 4"),"Yes",IF((G221="Condition 5"),"Yes"))))))

Many thanks,
Jeremy.


Two queries:

I'm trying to do an IF(AND where the first logical is that one cell contains the specified text and the second logical is that another cell in a multiple of 5000 but i'm completely stuck.

If TRUE give me the text "Loan"
If FALSE give me a blank cell

1st condition: Find some (not all) the text within cell S1. The cell will start with "ABC LIMI" but then can contain an random combination of numbers, spaces, *'s or letters.

2nd condition: Cell I1 value is a multiple of 5000 -

=IF(AND(S1="ABC LIMI",I1 is a multiple of 5000),"Loan", zero)

Any chance this can be done?


Hi,

I have several hundreds of different formulas in one column. I need to modify every formula. Each formula should be extended with if statement. This statement is almost identical for all the formulas - condition is always same, the only part that differs is that it should compare the value of the cell that is on the left for the formula. So if I give the address, the row number would differ.

I thought I reach my aim by CONCATENATE function. All I would need is to:
1) take actual formulas as text
2) prepare if condition for each row by dragging
3) CONCATENATE texts
4) Paste a new formula as text into cells that where old formulas were.

Unfortunately I don't know how to copy the text (not values) of my formulas somewhere else



Best,


Michal


Hello Everyone,

I have 1700 company names populating the column of a large table. All the company names are text, without any zero or blank entries. There are, however, many duplicate names in this column.

I'm doing some analysis that requires me to calculate the number of unique company names. For reasons that are too complex to explain here, it is not appropriate in this situation to filter out, hide or remove the duplicates names (and the rows of data of which they are a part).

So, I found a lovely little array formula that counts the distinct values in a table column that only contains text entries. In this case, the name of my column is "C7", for column seven. Here's the formula:

{=SUM(

1/COUNTIF(Table[C7],Table[C7])

)}


This formula returns an intelligent answer of 1135 unique company names.

I've also realized that I can add a simple little condition that will exclude some specific entries (though not necessarily all instances of that company name) before the number of distinct names is counted. That formula is:

{=SUM(

IF(Table[C8]="Yes",

1/COUNTIF(Table[C7],Table[C7])

))}


The answer now is an intelligent 630, meaning that 630 unique company names remain in the column after removing those entries whose adjacent cell in column C8 is not "Yes".

Now, here's where things go haywire. If I add a second condition, like this:


{=SUM(

IF(Table[C8]="Yes",

IF(Table[C12]<>0,

1/COUNTIF(Table[C7],Table[C7])

)))}


Suddenly, the answer is a nonsensical 591.9184412 !!!

How can that be? At a minimum, I expect a whole number answer from this formula! What is happening? Have I stumbled on an Excel bug?

I look forward to your workarounds and comments!

Cheers,

Jay

PS: I am, however, looking for a single (array) formula that will go into a single cell.




Hello all,
After a bit of googling and searching these forums, I can't quite find an answer to what seems like a pretty simple problem.

I have a list of around 20 companies that belong to either Group A, or Group B. These companies and their groups are listed in Column A (Company) and Column B (Group) on Worksheet B;

Company Name........Company Group
Name 1...................Group A
Name 2...................Group A
Name 3...................Group B
Name 4...................Group A
Name 5...................Group B
etc..........................etc

On Worksheet A, in Column A, company names (that match company names on Worksheet B) are manually entered. I would like Column B to populate with the company's group as defined on Worksheet B, Column B automatically. This is to speed up data entry, so the person entering each company doesn't have to remember, or look up that company's group (A or B).

In short, if I enter "Name 3" in Column A, I would like Column B to automatically populate with the text "Group B" as defined on Worksheet B.

I'm not quite sure where to begin. Company names will be added and removed from time to time, so ideally I would just update Worksheet B and those changes would be reflected on Worksheet A.

Any help would be greatly appreciated.

Thanks.

I am having trouble with conflicting conditional formatting. I have a list
that I need to have highlighted in one of three colors based on the result of
the formula in column D, and I need to have the text adjusted according to
the result in column G. I have tried having the text formatting as condition
one and shading as condition one, in either case, only the first condition is
applied. (Although the cells that meet only one of the conditions format
properly.)

Here are my conditions:
Condition 1: Formula is =$D23>40 (Shading)
Condition 2: Formula is =$D23>25 (Shading)
Condition 3: Formula is =$D23=1 (Text formatting)

I though of using an AND function, but had trouble getting it to work and I
have 6 potential states and the limit of three will not cover them:
No shade, normal text
No shade, grey ital text
Shade 1, normal text
Shade 1, grey ital text
Shade 2, normal text
Shade 2, grey ital text

Thanks for any help you can offer

--
L



Hi

I have a problem in getting a Pop up message in excel. I dont know whether formula can do the trick or should i take a help of micro.Kindly advise which is preferable and the solution for the same.

The problem is :-

I have 2 conditions and if any of the condition comes true , i want a pop up stating that "you have to calculate"( It should serve like a reminder).

Condition 1: In cell B if i insert "CA", and in cell E if i insert "no" and in cell G if i insert "lease".. I want a pop up stating "you have to calculate"

Condition 2: In cell B if i insert "TX" and in cell H if i insert "yes".. I should get a pop up "you have to calculate"

Awaiting a reply.

Thanks




Hello, I have a various difficulties within this excel problem.

counting combinations.xlsx


I must calculate all combinations of 7 numbers that give sum of 100. Numbers must be in interval from 1 to 39.
First condition is that sum of numbers must be changeable (i mentioned before sum of 100 to be simpler so I typed number 100 in the document). The sum can be also 150 or 88 for example, depending what number is entered in cell K2.

Second condition is that every combination must be unique (no such things like 7,8,9,10,16,20,30 and 30,20,16,10,9,8,7 and 7,8,10,9,3,20,16). This is example how results shouldn't be because all 3 combinations have same numbers only with different position/order). Only one combination of same numbers is possible and numbers must be ordered from smaller to larger numbers).

Is this possible to do, what is best option: functions or maybe some macro?

How someone can help

I know you can change a colour of a cell using a condition, But what im trying to do is colour a entire row or the number of cells that make up that row, depending on a condition of a formula in a particular row.

example, a sheet with 5 columns A B C D E, is the answer in cell E1 is false then colour that row i.e A1, B1, C1, D1 and E1 etc

How this explains what im trying todo

G


Fellas,
Here's the problem...I'll start with the formula I thought would work and go from the

=SUMPRODUCT(--(PLAYSHEET!$H$4:$H$1000="*"),--(PLAYSHEET!$N$4:$N$1000="*"))

I have two ranges:
H4-H1000 is Motion
N4-N1000 is Yards

There are many different kinds of motion, so the first condition would have to be any data entered in a cell within that range (hence the asterisk).

The second condition would simply look to see if any + or - integer was entered. THis is where I think I need the help...but I may be wrong.

So, if condition 1 and condition 2 are met, count. Anyone have any ideas? Thanks in advance.


Afternoon all,

Can anyone help me with this - I've had a hunt around and can't find anything on this so I hope someone can help me.

I have a list of 10 conditions that could be entered into a cell (G210 in this case). What I need to do is to enter into cell AJ210 a formula that checks G210 and returns the result in AJ210, based on what it finds.....e.g:

If G210 = Condition 1, 2 or 3, 4, 5, enter into AJ210 result 1.
or...
If G210 = Condition 6, 7, or 8, enter into AJ210 result 2.
or...
If G210 = Condition 9, or 10, enter into AJ210 result 3.

(I then need to count the resultant conditions, but that's another story....)

I tried to use something along these lines:
=IF(C210="","",IF(OR(G210="Condition 1",G210="Condition 2"),"Result 1","Result 2"),IF(OR(G210="Condition 6",G210="Condition 7",G210="Condition 8"),"Result 3","Result 1"))
...but I get too many arguments error.

Can anyone set me on the correct path?

Many thanks.
Jeremy.


Hi
I woulf like to know if there is a way to setup or configure a validation message to a cell if the result in this cell doesn't match a parameter.

For example:
I have a formula in a cell to calculate a date result, let's say "12 Oct 2010". In another cell I have a parameter, let's say 01 Oct 2010. Because the result is more recent then the parameter, I would like to appear a validation message sayng that the result is different.

I use format Condition to make the cell red, but I woulf prefer the message.

Thanks




I have using the code below to extract a ten digit number sequence from a cell, regardless if there is other numbers sets or text in the cell. The only problem is that the condition is returning the other text that is in the cell as well. I am looking to only show the ten numbers. Any ideas? Thanks.



Please Login or Register  to view this content.



I need to write a conditional formula that uses the OR function and nested inside an IF function is the ISTEXT logical condition checking to see if a cell has text. If it finds the cell has text, the condition tells the formula to check to see if it says "ATM" OR "Cash". If it satisfies either condition...the formula subtracts a certain amount from the original balance.

When I've tried to write such a formula, I always get a !VALUE! error or formula error creation errors. Thanks in advance.




Is there any way to have a function that behaves like =if(condition,formula,Leave the cell value unchanged) ?? I'm a new user and don't see a way to keep the if statement from changing the cell value whether or not the condition is met. If the condition not met, the cell value is changed to blank or zero or False.

Hi,

I am trying to build a code where some message needs to pop out when a certain condition is met.
lets say the condition is that Cells("B12").Value > 500.
This cell has a formula which is the sum of some other cells. As soon as this sum crosses 500, i need a message to pop up using msgbox.

Please help me do this.
I searched a lot and found lots of info about conditional 'formatting' but couldn't work my way around this.

Any help would be appreciated.

Regards,
Ankita


I'm getting wrapped around the axel trying to test multiple conditions in a little time-card spreadsheet. I'm certain someone on this board can make short shrift of the problem and I would welcome the help.

Column AT is used for messages to draw attention to exceptional situations. I'm testing for two possible exceptional situations that may arise independantly or together. The combinations a If the value in any of cells F7, J7, N7, or R7 > 6.0 the message in AT7 is " NB " (in bold red).
If the value in cell AR7 > 8.5 the message in AT7 is " OT "
If both are true ( F7 or J7 or N7 or R7 > 6.0 and AR7 > 8.5 the message in AT7 is " NB ... OT " I can write the OR for the first condition but I don't know how to AND it with the second condition to handle when both conditions are true.


Hi there!!!
I was looking at the message board about what i want but i didnt find it as a whole
What i mean is, that i found the sum of the visible cell only or the sum of a condition
Anyway what i want is:
I hide some rows using a vba code and i want to sum what is left under condition
I have 5 columns , the 2 have the interest.
The column e5:e5000 have values
The column f5:f5000 have specific text --> ("On","Off","Other")
I want ,using VBA, to know, its time i run my the code, the sum of the visble cells under the "On","Off","Other" condition in 3 cells
eg. E1 will have the sum of the "On"
F1 will have the sum of the "Off"
G1 will have the sum of the "Other"

thanks a lot
Stathis-Greece