Count with two Conditions
Good Day to All
I got a colum with values like 2 , 3 , 4 , 5 , 9 , 7 , 8 , etc.
I would like to count how many values i got higer then 5 and smalle
Can anyone help me plse.
Thanks in advance.
Message posted from http://www.ExcelForum.com
> Good Day to All
> I got a colum with values like 2 , 3 , 4 , 5 , 9 , 7 , 8 , etc.
> I would like to count how many values i g...Copy and Paste Conditional Formatting
I have set a conditional formatting for Cell B10
$G$1 is the absolute referece and the other cells are relative reference.
This means the formula for condtional formatting of Cell B11
Is there a way to copy the conditonal formatting up to Cell B2000
Copy, Paste Specal - formatting only
Select the entire range of cells you want formatted, with cell B10 being
active. Then input your conditional format as you have it written. Format
will be applied to all cells, varia...Sum with condition
The range K7:H794 has range name of expensescode. In cell H5 the formula is
=SUBTOTAL(9,H7:H794). This gives the SUM of the mentioned range.
I used the SUBTOTAL because I might use the filter depending on expensescode
and have he sum.
What changes do I need so that the sum in cell H5 does not include the
amounts in colomn H (numbers with currency format) that has an expensescode
of 999 or 888 not to be included in the total.
i.e. If the expensescode is 999 or 888, do not include that number in the
...How to Reconcile Two OnOpen Conditions in a Report?
We have a form frmStudentMarks with a date already in a textbox.
We use a macro "macDateControl" with the Windows Mode "Masked" that opens
With that macro we had not to write the date when we had to open a
But now we have a module which needs to write =LabelSetup() on the property
OnOpen of that report.
So how to reconcile these two OnOpen conditions together?
Any help will be appreciated.
...Conditional format to highlight only cells with formula, not const
I am trying to set up a spreadsheet that will change the cell color if the
formula in the cell has been replaced with a specific value.
Is there any way to conditional format all cells that contain a formula, but
not a constant?
Go to the Name Manager (Ctrl+F3) and click on New (In Excel 2007). In Excel
2003, Ctrl+F3 would do. In the name box type cellhasformula and in the
Refers to box, type the foll
Now go to conditional formatting and in Formula is, type =cellhasformula.
Select the desired format
Hope th...Case Statement Problem
Can someone please help me with my Case Statement
Public Function FEE_TYPE(DET_REF As String) As String
Dim strRef As String
Dim strFeeType As String
strRef = DET_REF
Select Case strRef
strFeeType = "LP"
strFeeType = "PPP"
strFeeType = "Prop Tax"
FEE_TYPE = strFeeType
SELECT FEE_CATEGORY: FEE_TYPE(DET_REF)
I do not get any results when I know there are many of them :(
Pu...Conditional Formating (how to use Offset() in cell reference)
Using XL 2003 & 97
How do enter into VBA code; the cell Offset R-12,C into
the Contitional Formatting below? (I want the "not equal to" referrence a
cell 12 rows above in the same column)
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual,
This might help you get to the next step:
If .Row < 13 Then
'too high in the worksheet
'remove any existing format first?????
.FormatConditions.Add ...SUMIF with two conditions ? #2
I have 3 colums
colum A G7705
colum B 300
colum C Units
I need sum the units in colum C if colum A & B = "G77053000"
Can somone Help
> I have 3 colums
> colum A G7705
> colum B 300
> colum C Units
> I need sum the units in colum C if colum A & B = "G77053000"
> Can somone Help
Ok I did that & it worked, now when I try to get the data from another
worksheet it gives me wrong data, do you know why?
...HELP!!! How to Nest AND OR functions
I am attempting to nest the and or functions.
I would like the argument to state:
IF C47 and D47 are negative... take the absolute value of g47/d47
IF C47 or D47 are negative.... take g47/c47+1
IF C47 and D47 are not negative... take g47/d47
Here is what i have written.... can someone please help?????? IT IS
GIVING ME ERRORS
=IF(AND(C47<0,D47<0),ABS(G47/D47),IF(O...Need Help In Update Statement
Hello all SQL gurus,
I would like to update one field in a table. The field should have data
K:\pictures\XXXXX.jpg. XXXXX is the employee id number. "K:\pictures" &
"jpg" are constant string. I need to update about 1500 employees which file
path is null and their paystatus is A. There are about 3250 employees in MS
SQL 2005 db.
eeLink Int (4) <-- a unique and appears on all
eeEEnum char (10) <-- employeeid
...Choose Value on Conditional basis
I have a to calculate incentive of about 100 employees based on their %Ach
Column, we have already made a *Table of Incentive Slots* i.e.if %Ach falls
between (>=95% to <=98%) he/she will be qualified for $6500 ...and so on
I need a forumla or way to easily calculate the incentive amount of each
The Layout of working sheet is below:
Name Title %Ach IncentiveAmount
Mr. xyz abc 98.7 ?? (this should be equal to 13000)
The layout of *Table of Incentive Slots* is below:
%AchMin %AchMa...Nested formular to give a word answer based on 2 cells
I have a s/s which I want to produce a statement based on 2 cells
G170 is a cell that contails a sum (if cells above are filled it it will add
I170 contains a cost
if G170 & i170 are blank then do nothing
If G170 is greater than 0 and I170 is greater than 0 then again do nothing
but if G170 is greater than 0 and I170 is = < 0 then "No Charge Made"
Can anyone help all I get is FALSE
I have tried =if(g170<=0, and (i170=<0,No Charge made",""))
=IF(AND(G170="",I170=""),"",IF(AND(G170>0,I170...Help with iif statement in query
I have a query which has the following iif statment:
Accrued Vacation: (([Consolidated]![Regular Day (REG)]/8)/24)
This tells me how many hours have been accrued for vacation in 2008
I have another iif statment in this query which is:
Expr1: IIf([Vacation_Balances]![Vacation]=" ",[Accrued
This statement is returning a blank cell for those employees who it should
be returning the result from the previous iif statment, however it is
returning a blank cell.
Can someone help me with this - please let me know what other information is
nec...if statement with dates #3
I would like to write a statement like:
where 1/1/2004 is interpreted as a date.
EXCEL is doing the arithemetic and giving me a decimal.
I could write 1/1/2004 as its numeric offset and write something like:
if(a1>0,37987), but that is rather awkward.
What else can I do?
>I would like to write a statement like:
>where 1/1/2004 is interpreted as a date.
>EXCEL is doing the arithemetic and giving me a decimal.
>I could wr...Sort in an IIf statement
I am using Access 2003.
I have a IIf statement in a query that looks like this:
Owner: (IIf([LLCName]="Smith Properties","SP#" &
The LLCNumber field is stored as a number.
The LLCName is stored as text.
It returns things like:
Right now, it is returning them with all of the 100s before the 90's as it
recognizes 1 is before 9, etc.
I would like to sort these by the LLCNumber. Is that possible?
Thanks in advance for your help!
At the bottom of the SQL sta...Possible conditional formatting?
Within conditional formatting, how do I go about filling
E1 in green if any or all of the A1:D1 range is populated
with "comp", keeping the narrative in E1 "ABCDE"
All help much appreciated
Under CF, choose Formula Is and use:
&...Conditional Popup message
I am putting together a spreadsheet that allows a client to enter
ineterview criteria that is weighted etc. Part of this is allowing them
to change the weights (pervcentages). I would like for a popup message
to appear if the percentage goes over 100.
I have roughly put this together
Function ValueCheck(ByVal Target As Range, Maximum As Integer)
If Sum(Target.Column) > Maximum Then
MsgBox "Caution, 100% has been exceeded!"
In the cell that shows total percentage I have =ValueCheck(B7:B28,100)
I would like to try make it prett...If statement please help
I need help, what would this statement need to look like if I want th
Column L must equal column N only if column K=TRUE if column K=FALS
then column L must equal 0.
Thanks in advance
Message posted from http://www.ExcelForum.com
(TRUE is coerced to 1 in math operations, FALSE is coerced to 0)
In article <firstname.lastname@example.org>,
drreamsurfer <<email@example.com>> wrote:
> I need help, what would this statement need to look like if I want t...two tables with same field names
I create an order status table for my division each week by running queries
on corporate's systems. The field names are always the same, and there lies
my problem. I need to measure the change in promise dates weekly to the order
line level. For example, I need to pull last monday's promise date minus this
monday's promise date to get the change in number of days.
Post your table structure so a query can be assembled.
Build a little - Test a little
> I create an order status table for my division each week by running queries
> o...Conditional validation :-)
I am using list validation in column A where user can choose 1-9 in each
cell. What I wish to create is:
if A1=1 then the validation list for B1 is 1.1 to 1.9
if A1=2 then the validation list for B1 is 2.1 to 2.9
and so on.... In effect, its conditional which validation list is being used.
How do I set this up??
Thanks in advance
Here's one way:
Create a table like this somewhere on your sheet:
1 1.1 2.1 3.1 ... 9.1
2 1.2 2.2 3.2 ... 9.2
3 1.3 2.3 3.3 ... 9.3
4 1.4 2.4 3.4 ... 9.4
9 1.9 2.9 3.9 ... 9.9
Assume the table is in the ran...Problem Updating Money 2000 to Broker Statement Persists
I have downloaded activity reported by my broker to Money
2000 and am on the page "Review Investment Activity". The
page says to click "Next" to update Money to the
statement, but when I do the page freezes for about 15
seconds and stays the same. Nothing happens. Why? What can
be done to update Money 2000 to the statement?
I was advised to close Money, delete the browser temporary
files and then open Money with Start, Run and "msmoney -s"
(omitting the parentheses), which I did. This did not
solve the problem.
Any help would be greatly appreciated.
This is a multi-part message in MIME format.
By using the following conditional formula, I am able to get the =
category needed based on the sales. The sales are mutually exclusive. In =
other words, if there is a value under CAD SALES then USD SALES & USD FX =
will be either zero or blank. And if the USD SALES is filled then CAD =
SALES will be either zero or blank. And if all three sales fields are =
empty then the category should be ...Accounts to match in downloaded statements
Does anyone know where the "data" is stored that indicates which account a
statement is to be matched with on import? I keep getting the wrong account
updated when a statement is imported and even when I delete the account
number and the bank name from the account information it still tries to match
In microsoft.public.money, satxfili wrote:
>Does anyone know where the "data" is stored that indicates which account a
>statement is to be matched with on import? I keep getting the wrong account
>updated when a statement is imported and even when I delete...Max Date + condition
In column 1, I have dates. In column 2, I have criterias. How can I ge
the max date matching criteria in column 2.
How to get max date of AA ? I should get 2004-08-02
Message posted from http://www.ExcelForum.com
try the array formula (entered with CTRL+sHIFT+ENTER):
>In column 1, I have dates. In column 2, I have criterias.
How can I get
>the max date matching criteria in co...Losing conditional formatting when saving
I have created quite an extensive excel file with a significant amount of
conditional formatting. I have reached the point where if I save now the
conditional formatting of the last few rows is not saved. Is there a way
around this or is there a limit to how much conditional formatting you can
I've never noticed xl dropping any CF, but I don't use it a whole bunch.
But if it drops it for you, maybe you could reapply it in the
workbook_open/auto_open code each time the workbook opens.
You may want to post some specifics.
How many rows. How you used Format|conditional forma...