Function countif with conditions
I have a list of numbers (2000 lines). I need to count the numbers of
the values in different ranges. eg <= $10.00, $10.01 to $100.00,
$100.01 to $500.00, etc.
The formula =COUNTIF(H2:H2001,"<=10") works. It gives me 257.
But for the next range, formula
=COUNTIF(H2:H2001,AND(">10","<=100")) doesn't work.
What's the problem? How can I get this to work?
Try the formula =COUNTIF(H2:H2001,">=10")-COUNTIF(B2:B5,">100")
> I have a list of numbers (2000 lines). I need to count...Count Unique with condition
I am looking for a formula to count unique occurances of column (A) where
column (B) equals a certain value. Any help would be appreciated.
I have the formula
which gives me a count of the unique values in 'data'!A, now I would like to
break it down over a value in 'data'!B
the result would look like this:
smith, john, blue
smith, john, red
jones, james, yellow
jones, james, green
williams, john, orange
williams, john, teal
Uniq...Leopard Upgrade -> File Format Not Valid -> every file is corrupted??? = I am up the creek
Hoping someone has an idea on this one.
I recently upgraded a clients small network (4 comps) to OS X 10.5
The machines were a mix of 10.3 and 10.2 machines. I cloned all
machines to ext hard drives and did an erase and install of Leopard on
While there were certainly a few issues around the upgrade, mostly
everything went fine...then the client tried to open his excel files,
consisting of client databases and other essential information. No
The excel files were stripped of their extensions at some point and
finder calls them "unix executable files." Excel will not...Conditional Formatting not recalculating
I have an excel file that has selection boxes (boxes set up as lists using
the data validation feature). I have set conditional formatting to hide
certain selection boxes based on cell values. The problem is that the
conditional formatting does not automatically recalculate as needed when cell
Any thoughts on how to fix this?
...Format cells as date
I am working on a worksheet witch draws data from another application. All
drawn data appears fine on the worksheet but the date columns. They are left
aligned (instead of the deafult right) and if I try to format them in a
different way by selecting them right-clicking and choosing Format Cells but
nothing happens. Even if I try to format them in any other option given
nothing happens. The cells remain in the dd/mm/yyyy format that they are (and
right aligned of course). What can I do? I am at a dead end here.
I need them to show up as dates so I can use a sumproduct function on t...IF OR AND in same conditional formula
I'm setting up a conditional formula to use in a conditional format, where
depending on a couple of different conditions, the cell with the date entered
will be color-coded either red, yellow or green.
In this particular formula, I'm using the following variables:
- cell C7 = a percentage (70%)
- cell CI7 = the number of days calculated from today (19)
I've constructed the formula as such (makes sense to me, but Excel doesn't
Essentially, I'm asking Excel to evaluate two conditi...Conditional Formatting #45
Is there a way to conditionally format a cell based on the
value in another cell? i.e.:
A1 formats to green or any other chosen format.
Thanks of any suggestions!
Sure. Select A1. Format > Conditional Formatting.
Select "Formula Is" on the drop-down and put:
Click the "Format" radio button and format as desired.
>Is there a way to conditionally format a cell based on
>value in another cell? i.e.:
>A1 formats to green or any other chosen forma...How do I get CD-RW to be revisable?
I don't understand your question. Does this have anything to do
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"Angie" <Angie@discussions.microsoft.com> wrote in message
...Formatting Cells in Excel 97
Sorry if I sound real stupid but is there anyway that we
can control the column formatting in Excel 97 like let's
say column A = GENERAL(6), column B = GENERAL(4), column C
= TEXT(18) etc?
Appreciate any form of advice, thanks!!
I replied in the programming group. Are you seeking a programming
answer? It's usually best to only post to one group, and include the
remark "Please tell me if I should ask this in another group."
On Thu, 28 Aug 2003 00:56:47 -0700, "Daryl" <firstname.lastname@example.org> wrote:
>Sorry if I sound real stupid b...Conditional addition of cells
I want to add the sales (column B) of the people in column A.
....and get this result:
Only the result in column D needs to be calculated.
Any help much appreciated.
Since you state that:
"Only the result in column D needs to be calculated"
I'll assume that you have already entered the list of unique names in Column
So, in D1 enter this:
And copy down.
---------------------------------...Variable/Adaptive Numeric Format
in Access 2007
I some report fields that are to display quantities and amounts
the range of these numbers could be large (100,000 or $345,999) or small
(0.375 or $0.0625)
is there a way to have the field formatted to adapt to the range of the
value being displayed
for example if the value has no significant decimals then display the value
without decimal places (512.00 becomes 512) or if the value has many decimals
limit the display to only 2 decimals (0.63712938 becomes 0.64)
How do you propose to explain to Access how to tell that a number "has no
What I have is a column with 6 characters that can either be all alpha or 1
alpha with 5 numeric characters. What I need to do is examine cell and if
the last five characters are numberic compute the cost. How would I make a
condition to do that.
part # QTY ID Rate Extension
23456 23 JJones .34
39485 20 J23494 .59 11.80
96874 30 J23494 1.19 35.70
16349 50 DDavis .59
in your extension column enter
> Wh...Formatting .. is it possible to ....
format a range of cells like c6 through h6 with a fill color of green if
the value in cell k6 is positive and with a fill color of red if it is
How would that be done?
Select C6:H6, with C6 active. Choose Format/Conditional Formatting...
Set the dropdowns and inputboxes to read:
CF1: Formula is =$K6>0
CF2: Formula is =$K6<0
In article <BVKFh.65924$1H5.email@example.com>,
az-willie <firstname.lastname@example.org> wrote:
> format a range of cells like c6 t...Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains
formula, but it didn't work.
"If cell value is equal to 0 then font - white" This doesn't work,
If i use this condition on a cell without formula it works just fine.
sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262
View this thread: http://www.excelforum.com/showthread.php?threadid=26784
are you sure your formula returns an exact zero?. Could you post the
formul...how do you format a cell to be in degrees
I want to find the cosine of an angle, but the computer doesn't know the
value I am referencing is in the form of a degree.
can anyone help me!!
you have to change it to radians in your formula
> I want to find the cosine of an angle, but the computer doesn't know the
> value I am referencing is in the form of a degree.
> can anyone help me!!
This may help you:
Insert a degree symbol Help
You can easily insert a degree symbol in your Office documents. Most fonts
that Microsoft Windows® users have installed inclu...count
I try to use:
and more combinations for example with sumproduct (of course I try to
hit CTRL+SHIFT+ENTER combination), but it doesn't work (I only get error
or 0 which is incorrect value).
I have two columns - one with time (for example: 2010-09-01) and second
I want to count cells using two criterias.
Unfortunately I can't create pivot table (workbook is shared) to solve
my problem, so please help me.
Adjust the ...More Conditional Formatting #3
Hi I need to use one condition to judge whether or not a cell is 90-95% of
target or 100-105% of target. The other two conditions will be used, so i
need to get this into one condition.
Any help would be great.
Use "Formula is."
=OR(AND(A2>=Target*90%, A2 <= Target*95%),AND(A2>=Target*100%,
mvpearl omitthisword at verizon period net
"KCi" <KCi@discussions.microsoft.com> wrote in message
> Hi I...OOF formatting is lost
When my users edit their Out of office reply through OWA the formatting is
lost. I.e they use the font arial with size 12, after they save the settings
and then go back to the same page all the formatting is lost.
Anyone have any ideas?
...Combo box formatting
Is there a way to make certain entries in a combo box bold or change the
background color of those items?
"QB" <QB@discussions.microsoft.com> wrote in message
> Is there a way to make certain entries in a combo box bold or change the
> background color of those items?
You mean, in the list, when you drop it down? No, an Access combo box can't
do that. You can highlight items, sort of, by basing the combo box on a
query that builds the display text and includes asterisks o...Pivot Tables lose some formatting when Updated
A customer asked me this question about Pivot Tables and I
want to make sure I give him the right info.
1)When my customer updates pivot tables the formatting
always changes even though he has selected the check box
stating to keep formatting.
2) I tried this out and it seems to keep most of the
formatting like color, bolding and dollar signs but it did
not keep the bordering.
3) Is there a way to have it keep all formatting for a
report and have it maintain the integrity of the
formatting even though he updates the data?
Any help would be greatly appreciated !
Set t...Conditional printing on a report
I am designing a report where 3 or 4 text paragraphs only need to appear on
the very first page. My idea is to create a page footer and to put a formula
of macro to do so... Can anyone share idea /code with me to have this
conditional formatting in place?
Thanks a bunch for your help
I'm familiar with subtracting start time from finish time to get
elapsed time, and formatting the elapsed time as a time function, to
include d:h:mm for elapsed times of more than 24 hours. But I don't
seem to be able to work out a formating that will, e.g., report 30
hours for the elapsed time for a number 1.25 [days].
Will formatting do it, or do I have to write my own parsing function?
Format it as [hh]:mm, this handles more than 24 hours.
(remove nothere from the email address if mailing direct)
"Fred Holmes" <email@example.com> wr...How do I format text in an outlook Task ?
I have an Outlook task form with some script in it to put a date stamp
etc into the body of the task.
The problem is that I want to make the date stamp bold.
Trolling thru the net, I found references to Item.HTMLBody - but I
cant seem to get this to work for a TASK - only for an email item.
i.e. Item.HTMLBody = "<HTML><b/>Date Stamp.</b><BODY> Other
works for an email but NOT for a task.
Once my task is created I can however enter text manually and make it
Any help is much appreciated.
...Conditioning Formatting Question
Could anyone help me with the following question:
I have a spreadsheet with a column of cells that are coloured (colored
in purple whilst a project is incomplete but which are then coloure
white again when the project is finished. I have another column i
which the completion date is written (once it is finished). Is there
way I can use conditional formatting to have the first column becom
uncoloured conditional on the other cells? Thus, I want cell B1 to b
white IFF cell D1 is not empty.
I am using Excel 97 (for my sins).
Any help very gratefully received!
Have just recently upgraded customer from v7.5 to V9.
They have specialised inventory item numbers that include dimensions but
when they print their SOP sales invoices - they only require the first 6
digits printed. We achieved this a while ago by putting a format on the
Since the upgrade one user has experienced a printing problem as the full
item number appears - we have now establised that it is only if the user uses
a smartlist to select the invoice and drills back to the invoice to print. If
they use the inquiry screen to select the invoice - all is ok.
Anyone any i...