If Condition Formula 


If Condition Formula  Excel 
View Answers 
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
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
Similar Excel Video Tutorials
SUM or COUNT only certain items! SUMIF COUNTIF functions
 See how to use the SUMIF and COUNTIF functions to add and count given a certain condition. With this trick you can add only the sales for the salesper ...
Excel Functions COUNTIF & SUMIF Count and Add with one Condition (Criteria)
 The Excel 2010 Basics Series shows a systematic description of what Excel can do from beginning to end.
Download files at: https://people.highlin ...
Download files at: https://people.highlin ...
Any Number Not Zero = TRUE
 See that the IF function sees 1, 1, 10 and 1.23 as TRUE and 0 as FALSE. See this trick in an IF, SUMIF and SUM formula for Conditional summing. Addi ...
Excel Functions & Defined Names, SUM, COUNT, COUNTA, SUMIF, COUNTIF
 The Excel 2010 Basics Series shows a systematic description of what Excel can do from beginning to end.
Download files at: https://people.highlin ...
Download files at: https://people.highlin ...
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
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
PopUp 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
 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
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
PopUp 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
 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
 This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Similar Topics
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?
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 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?
Hi,
Can someone help me: how to make Array Formula in cell B11 with condition "A or B & Y"
Thanks
Jason
===================================================
Sheet1
* A B C 1 Condition 1 Condition 2 Data 2 A Y 0.20 3 C Y 0.45 4 B N 1.10 5 B Y 12.00 6 A N 9.40 7 A N 0.01 8 * * * 9 * Y * 10 A 0.20 * 11 A or B ? *
Spreadsheet Formulas Cell Formula B10 {=SUM(IF($A$2:$A$7=A10,IF($B$2:$B$7=B9,$C$2:$C$7,0),0))} Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
Excel tables to the web >> Excel Jeanie HTML 4
Can someone help me: how to make Array Formula in cell B11 with condition "A or B & Y"
Thanks
Jason
===================================================
Sheet1
* A B C 1 Condition 1 Condition 2 Data 2 A Y 0.20 3 C Y 0.45 4 B N 1.10 5 B Y 12.00 6 A N 9.40 7 A N 0.01 8 * * * 9 * Y * 10 A 0.20 * 11 A or B ? *
Spreadsheet Formulas Cell Formula B10 {=SUM(IF($A$2:$A$7=A10,IF($B$2:$B$7=B9,$C$2:$C$7,0),0))} Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
Excel tables to the web >> Excel Jeanie HTML 4
Guys ,
I need some help here regarding to If condition , what i need it is how to make If condition help me to solve an equation (Specific formula ) and this formula will be decide it upon the condition if agree or not and showing the reult in different cell , to make it easer for you this is an example
IF H1 > 0 THEN I NEED TO APPLY A FORMULA OF A1= x+y
if H1
I need some help here regarding to If condition , what i need it is how to make If condition help me to solve an equation (Specific formula ) and this formula will be decide it upon the condition if agree or not and showing the reult in different cell , to make it easer for you this is an example
IF H1 > 0 THEN I NEED TO APPLY A FORMULA OF A1= x+y
if H1
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
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
Fellow Forum Members.
The function below is my horrific attempt at figuring it out on my own and I should point out it does not work:
=IF(NOT(ISERROR(FIND("Cell Empty or Cell Full",A1))),"Condition 1 Txt", "Condition 2 Txt","")
What I'm trying to develop is a function that checks for two conditions in Cell A1.
The first condition to check is if Cell A1 is devoid of data. If cell A1 is empty it returns "Condition 1 Txt" to the cell where the formula resides.
The second condition to check is if Cell A1 is occupied with data. If cell A1 is contains any kind of data it returns the text "Condition 2 Txt" to the cell where the formula resides.
The text string "Condition 1 Txt" and "Condition 2 Txt" are just examples. I plan to replace both with different text messages.
Any help will be greatly appreicated. Thanks
The function below is my horrific attempt at figuring it out on my own and I should point out it does not work:
=IF(NOT(ISERROR(FIND("Cell Empty or Cell Full",A1))),"Condition 1 Txt", "Condition 2 Txt","")
What I'm trying to develop is a function that checks for two conditions in Cell A1.
The first condition to check is if Cell A1 is devoid of data. If cell A1 is empty it returns "Condition 1 Txt" to the cell where the formula resides.
The second condition to check is if Cell A1 is occupied with data. If cell A1 is contains any kind of data it returns the text "Condition 2 Txt" to the cell where the formula resides.
The text string "Condition 1 Txt" and "Condition 2 Txt" are just examples. I plan to replace both with different text messages.
Any help will be greatly appreicated. Thanks
Hey guys,
Total newb question but if I have an if/then condition in a cell that references another cell for that condition, is there a way to make the referenced cell condition a text wildcard? As in if(C40=*"John"*, X, Y). And the condition will be true if C40 contains the word John in it along with any other text.
Does this make sense?
J.
Total newb question but if I have an if/then condition in a cell that references another cell for that condition, is there a way to make the referenced cell condition a text wildcard? As in if(C40=*"John"*, X, Y). And the condition will be true if C40 contains the word John in it along with any other text.
Does this make sense?
J.
Is there another formula that works like vlookup but isn't concerned about the source data being in ascending order? I know of one that works when you can look up based on 2 conditions but can't seem to make it work when I only have 1 condition to meet.
My 2 condition formula is a CSE and is: =index(array to lookup(match(1,array of first condition=condition)*(array of 2nd condition=2nd condition))
Can I do something like the above if I only have one condition to meet?
My 2 condition formula is a CSE and is: =index(array to lookup(match(1,array of first condition=condition)*(array of 2nd condition=2nd condition))
Can I do something like the above if I only have one condition to meet?
Hi everyone,
I have a nested IF function I'm having issues with. My workbook is laid out as follows:
Sheet1 contains two dropdown 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 freeform 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 correctlyformatted version of that statement, or point me to where I need to look to figure it out myself?
Many thanks,
Rich
I have a nested IF function I'm having issues with. My workbook is laid out as follows:
Sheet1 contains two dropdown 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 freeform 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 correctlyformatted version of that statement, or point me to where I need to look to figure it out myself?
Many thanks,
Rich
I am trying to condition format cells that have a formula in them. I would like the condition to be based onthe result of the formula. The first condition I want is if the result is between tomorrow and 3 months from now I would like to make the result written in green. I tried the following to achieve this:
"cell value is","between","TODAY()90","tODAY()1"
The other condition I want is if the result is today or an earlier date, I would like the result to be written in red. I tried this to achieve:
"cell value is","less than or equal to","TODAY()"
Any other result I would like to be left alone. My only possible results will be a date or the test "N/A". But my results seem to only satisfy the second condition because everything was written in red.
"cell value is","between","TODAY()90","tODAY()1"
The other condition I want is if the result is today or an earlier date, I would like the result to be written in red. I tried this to achieve:
"cell value is","less than or equal to","TODAY()"
Any other result I would like to be left alone. My only possible results will be a date or the test "N/A". But my results seem to only satisfy the second condition because everything was written in red.
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 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.
Trying to do the following:
IF:
Condition 1: $J$3:$J$5000=1
Condition 2: $C$3:$C$5000="Base"
Condition 3: (TEXT($AF$3:$AF$5000,"mmmyyyy")=TEXT(NOW()DAY(NOW()),"mmmyyyy"))
This third condition makes the formula only look at rows where column AF has a date value that falls during the prior month, whatever that may be relative to the current month.
THEN:
Average all corresponding integers that appear in column AH where the integers are greater than or equal to 0.
Also trying the same with a twist:
IF:
Condition 1: $J$3:$J$5000=1
Condition 2: $C$3:$C$5000="Base"
Condition 3: (TEXT($AF$3:$AF$5000,"mmmyyyy")=TEXT(NOW()DAY(NOW()),"mmmyyyy"))
THEN:
Calculate the number of times "Under" appears in column AV divided by the combined total number of values "Under" and "Over" appear in that same column, in Percentage format.
IF:
Condition 1: $J$3:$J$5000=1
Condition 2: $C$3:$C$5000="Base"
Condition 3: (TEXT($AF$3:$AF$5000,"mmmyyyy")=TEXT(NOW()DAY(NOW()),"mmmyyyy"))
This third condition makes the formula only look at rows where column AF has a date value that falls during the prior month, whatever that may be relative to the current month.
THEN:
Average all corresponding integers that appear in column AH where the integers are greater than or equal to 0.
Also trying the same with a twist:
IF:
Condition 1: $J$3:$J$5000=1
Condition 2: $C$3:$C$5000="Base"
Condition 3: (TEXT($AF$3:$AF$5000,"mmmyyyy")=TEXT(NOW()DAY(NOW()),"mmmyyyy"))
THEN:
Calculate the number of times "Under" appears in column AV divided by the combined total number of values "Under" and "Over" appear in that same column, in Percentage format.
Code:
I use the above formula down a column in sheet 2 of my worksheet. Basically, it matches the text found in column N of sheet 1 to a corresponding code and inserts that into the cell the formula is in.
However, when I delete the row that the formula references in sheet 1, I get the ref# error in the corresponding cell in sheet 2.
Is there any way to make it more robust so that if the referenced row in sheet 1 is deleted, it will then reference the new row that replaces it?
Many thanks in advance!
=IF('Sheet 1 '!N5="Condition 1",1,"")&IF('Sheet 1 '!N5="Condition 2",2,"")&IF('Sheet 1 '!N5="Condition 3",3,"")
I use the above formula down a column in sheet 2 of my worksheet. Basically, it matches the text found in column N of sheet 1 to a corresponding code and inserts that into the cell the formula is in.
However, when I delete the row that the formula references in sheet 1, I get the ref# error in the corresponding cell in sheet 2.
Is there any way to make it more robust so that if the referenced row in sheet 1 is deleted, it will then reference the new row that replaces it?
Many thanks in advance!
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.
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
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.
 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.
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.
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.
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?
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
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
Hi everybody,
I have a text file named "C:\Text.Txt" with records in the following format:
EventDate Message
31032009,Please Prepare Quartely Reports
15042009,Send Dunning Letters
30062009,Issue Second Qtr Reports
I need a macro that should work when excel starts and read each record in the above text file and display the message given after the EventDate on a message Box if the following condition is met per record:
If the EventDate minus Today's Date is >= 0 and < 60
If multiple records meet the above condition, then those records should be concatenated and be displayed on a single message box rather than many message boxes.
The idea is to pop up a message box when excel starts and displays the events to be done within the next 60 days.This serves as a reminder.
Hope I have explained well.
Thanks
I have a text file named "C:\Text.Txt" with records in the following format:
EventDate Message
31032009,Please Prepare Quartely Reports
15042009,Send Dunning Letters
30062009,Issue Second Qtr Reports
I need a macro that should work when excel starts and read each record in the above text file and display the message given after the EventDate on a message Box if the following condition is met per record:
If the EventDate minus Today's Date is >= 0 and < 60
If multiple records meet the above condition, then those records should be concatenated and be displayed on a single message box rather than many message boxes.
The idea is to pop up a message box when excel starts and displays the events to be done within the next 60 days.This serves as a reminder.
Hope I have explained well.
Thanks
Hello! I need a bit of help, please. I have a running spreadsheet that uses the mod to highlight alternate cells. I've set it up for the entire document under conditional formatting as =MOD(ROW(),2)=0
I have one particular column that contains a sum formula. If the sum=0 I want the text to appear clear or white (or ideally be deleted) so as not to appear distracting as a column of zeros with an occasional value. It just seems to make the true values harder to spot. Anyway, I added a second condition to this column that simply states if cell value=0 then format text as "white".
The rows not higlighted by the first condition work fine and the text is white, but the text in the highlighted rows remains black in this column. I tried alternating the order of the two conditions, but then the second condition cancels out the alternating highlights from the first condition.
any suggestions?
thanks
bj
I have one particular column that contains a sum formula. If the sum=0 I want the text to appear clear or white (or ideally be deleted) so as not to appear distracting as a column of zeros with an occasional value. It just seems to make the true values harder to spot. Anyway, I added a second condition to this column that simply states if cell value=0 then format text as "white".
The rows not higlighted by the first condition work fine and the text is white, but the text in the highlighted rows remains black in this column. I tried alternating the order of the two conditions, but then the second condition cancels out the alternating highlights from the first condition.
any suggestions?
thanks
bj
I have rows with text and numbers. In order to ensure that the numbers are accurate, I have a "QC formula" that calculates a check using all of the numbers from 1 row. The challenge is that the "QC formula" needs to vary depending on a text value within the row.
How can I lookup up the text value and then return the correct active formula for that row? I have too many differet text values to do a nested If statement. any help is greatly appreciated! see simplified example below. thanks!
Condition A B C Formula' Needed based on Condition
Red 1 2 3 A*B*C
Blue 1 2 3 A+B+C
Green 1 2 3 (A+B)*C
How can I lookup up the text value and then return the correct active formula for that row? I have too many differet text values to do a nested If statement. any help is greatly appreciated! see simplified example below. thanks!
Condition A B C Formula' Needed based on Condition
Red 1 2 3 A*B*C
Blue 1 2 3 A+B+C
Green 1 2 3 (A+B)*C
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
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
Looking to input a formula in A1 to check for a condition if B1=E1. If the condition is true, C1 needs to be populated with a text string already inputed into D1. Instead, I just get the (True/False) condition in A1. Sample formula below:
=IF(B1=E1,C1=D1,"")
I understand that the issue lies in the C1=D1 declaration. Help would be greatly appreciated.
Thanks, cashaau
=IF(B1=E1,C1=D1,"")
I understand that the issue lies in the C1=D1 declaration. Help would be greatly appreciated.
Thanks, cashaau
When I post the following message I can't get all of the condition 1 formula to show up in the post. What do I need to do?
Cells C16:C29 have conditional formatting as follows
First Condition ($C16<AVERAGE($A16:$B16)$C$10
Second Condition ($C16>AVERAGE($A16:$B16)+$C$11
Goldi
Cells C16:C29 have conditional formatting as follows
First Condition ($C16<AVERAGE($A16:$B16)$C$10
Second Condition ($C16>AVERAGE($A16:$B16)+$C$11
Goldi