different formatting, same cell
Say the date that a certain event occurs (2/17/07) is in cell A1.
I would like to have cell B1 display: Completed 2/17/07
If I use the formula ="Completed "&A1, it displays: Completed 39130
Is there another way to do this?
To Excel, dates are just numbers, so you need to tell it to convert the
number (date) to text how to display that text.
Try something like this:
Does that help?
> Say the date that a certain event occurs (2/17/07) is in cell A1....List of different values in data area
Hi excel specialists,
How Can I automatically get the list of different values from the dat
area and to find out their frequence?
INDIVIDUALLY MEASURED VALUES:
3,5 4 3,5 4 3 3,5 4 4 3
3,5 4 3 3,5 3 3 3,5 4 4
Thanks for your help in advance. Balcovja
balcovja's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2623
View this thread: http://www.excelforum.com/showthread.php?threadid=39572
Take a look at FREQUENCY in Hep
(remove nothere from the email address if m...CountIf Statement
Can anybody help?
I am creating a formula in a cell of a spreadsheet which will
Count all in stances of "distribution centre" within a column
This is achieved using the
=COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP")
I was wondering whether it was possible in excel to use a statemen
=COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") and (nex
Message posted from http://www.ExcelForum.com
You can use sumproduct to count with more than one condition multiple ranges
=SUMPRODUCT(--(Range1="GROUP&...Counting rows that fit within a range
I've got what seems to be a simple problem to do by hand (if it weren't for
the fact that the table in question isn't exactly small). So I was hoping I
could do it (somehow) in an Excel spreadsheet.
What I have, is a column of numbers (to one decimal point), assumed to be
sorted in descending order.
I want to have another column (calculated by Excel) that will count how many
rows have a larger value (in the first column) than it, but only up to a
certain range (by adding a constant to the value of that row).
For example, here's some data, with the first column supplied by ...Summary of Difference between dates in years, months, days
I need to calculate the difference between 2 dates and then total them.
Here's what I have so far:
From To Length of
01/09/2003 31/01/2010 6y 4m 30d
01/06/2000 30/11/2002 2y 5m 29d
Total of Service: ??????????
I've used the following formula to calculate the total days worked:
=DATEDIF(A4,B4,"Y")&"y "&DATEDIF(A4,B4,"ym")&"m "&DATEDIF(A4,B4,"md&...IF, COUNTIF
Can someone explain why these two expressions differ referring to the value 5?
IF(A2:A10<5 etc. ) but
COUNTIF(A2:A10,"<5") requires a comma and quotes
Wouldn't it make sense for them to be the same?
For us it would seem to make sense, but for the computer not so much :-)
My understanding is that in the IF statement the <5 is a Comparison
Operation which is a part of the 1st Function Argument whereas in the
COUNTIF it is a Criteria Reference supplied as the 2nd independent Function
Argument. If it stood alone as simply <5 it would ...Countif ...
I have a column with phone numbers. I want to count how many phone numbers
have the area code "214" and "972".
Thanks, the first formula worked.
"Jason Morin" wrote:
> The formula depends on how your phone numbers are formatted and whether they
> are text or actual 10 digit numbers. For example, if they are text and you
> simply need the first 3 numbers in the cell, try:
> If the area codes are enclosed in parentheses, you could use:
> =SUM(COUNTIF(A1:A10...Count consecutive values down a column
I have many tick records from live market data, in many separate files
(one for each stock symbol).
I am trying to make a new file of 2 fields; date & a count of the
number of ticks (or records in the original file) for each date. The
records in the original files show records (ticks) for any one date as
ranging from 1 to well over 1000. The VB I've attempted to write has
struck maybe it's biggest error where there is only one recortd
Otherwise I've managed to account for importing the original text via
macro and exporting the same (with thanks to Chip Pearson), ans a...Database size and Mailbox size differences
I just did an off-line defragmentation. The priv1.edb is now 38.7 GB, and
the priv1.stm is 14.9 GB. The total backup of the information store and the
site replication service is about 56 GB. However, the total mailboxes size
from Exchange System Manager is about 33 GB. What is the .stm file, and how
do I reduce the backup to the size of the total mailbox?
The totals in the GUI don't include retained deleted items.
Celebrating a decade of Exchange peer support
"Anonymous" <Anonymous@discussions.microsoft.com> wrote in message
I created a calendar @ work with a higher version of Publisher. I am trying
to access the file @ home, but I have 2000 and it says it can't open it.
What can I do?
You will have to save the calendar at work as a Publisher 2000 document.
"jabrjawz" <firstname.lastname@example.org> wrote in message
>I created a calendar @ work with a higher version of Publisher. I am trying
> to access the file @ home, but I have 2000 and it says it can't open it.
> What...Colour code from different servers
-- I collect mail from 2 different servers and I was wondering if it is
possible to have them show in the IN BOX in different colours. I know I can
select different senders by colour but I want to show all senders that came
from each server. Can anybody help
"Pete A" <PeteA@discussions.microsoft.com> wrote in message
> -- I collect mail from 2 different servers and I was wondering if it is
> possible to have them show in the IN BOX in different colours. I know I
> select different se...Criteria/CountIf and Pivot Table
My data looks like this
ID Date CWA
1 08/12/2006 0
2 08/13/2006 1
3 08/14/2006 1
4 08/01/2006 0
Based on the above data, I want to create a pivot, by month (I know I
only have Aug here)
to show the following:
Sum of CWA
Count of CWA
Percent of cases that were submitted with CWA.
The CWA is an indicator field that looks at another field (not listed
here) to determine if there is cash in a...Same .pst Files Looks Different on other PC
I have a weird problem. I have a small network at home with 1 desktop and 1
(wireless) laptop. I have installed MS Office XP on both computer including
all the servicepacks and updates (both Outlook version numbers 10.4712.4219
SP-2). I share my document folder which is on the desktop, so I can access
it through the laptop. When I open the .pst file on my laptop, Outlook
handles the full-day appointments differently. It moves them up one hour,
i.e. makes the appointments start at 23.00 hours the night before (and then
they last till 23.00 hours on the right day). When I double-click th...Countif
I am trying to solve the following problem
i) cells A1 to A10 have either M or F (male or female)
ii) cells B1 to B10 have either (grades) A B or C
iii) I want (eg cell B13) to state the number of female students who scored
A... and cell B14; how many female students who gained B etc
I have tried various things including countif, sumif etc. It seems like a
straight forward problem but the solution evades me!
in Cell B13 and filled down to Cell B15.
> I am trying to solve ...Counting the number of entries in a column
I have area numbers in column A and the number of produkts ordered in column B
I want to count the number of entries in a particular area bearing in mind I
just want to see the number of entries rather than sum them up like SUMIF. Is
there a convenient formula which would allow me to do this?
Thanks for any help
I assume your product is in column B and the product name is XX
Change range and product name to fit your needs
you can have as well the product you want to count entered in a cell let's
say C1 in that case you can...IF two different conditions are met #3
The array isn't working eithe
AnnaV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=961
View this thread: http://www.excelforum.com/showthread.php?threadid=27065
Have you tried Jason's suggestion?
"AnnaV" <AnnaV.email@example.com> wrote in message
> The array isn't working either
> -----------------------------------------------------------------...CountIf with And
Novice Excel user on Excel 2003. I have a yearly data input sheet to track
codes. I use the date (fomatted as 1/01/2009) and a code. I want to track
the codes in a mothly chart for ease in summarization and improvment
tracking. The codes are used to define a department and an error in that
department (code 1a means - AR department and error a). I need to pull and
total the amount for each code for each month. After searching for a while
it seems that using CountIf with And does not work. Any suggestions would be
=SUMPRODUCT(--(MONTH(date_range)=4),...Access 97 to 2003
I'm converting a legacy db (not my code!)
I have a form with a linked subform (master/child) with code in the subform load event
The subform code tests the value of a field in the subform and reacts accordingly
Sometimes the child subform has data, sometimes not, depending on the record in the main form
There seem to be two differences
(1) In Access 97 the load event seems to only fire if there is data to show in the subform - if no records, then no event
In Access 2003, it seems the event fires every time
(2) In Access 2003, in cases where there IS data for the subform, I get an error wh...Counting text characters in a row
This problem is likely very simple however I'm new to speadsheets.
I've created a spresdsheet to record attendace at work. in each row
have an "H" or "S" to denote holiday or sickness respectively, agains
How do I count the number of "H's" in a row to tot up the number o
days holiday taken.
Sorry if this is very basic, but so is my spreadsheet knowledge.
Message posted from http://www.ExcelForum.com
> *This problem is likely very simple however I'm new to speadsheets.
> I've created a spresdsheet...Excel countif and
is there a "and" condition if you use countif or sumif?
2 < countif(b1:b4,"=c")and(a1:a4,"=1")
"KEN" <KEN@discussions.microsoft.com> wrote in message
> is there a "and" condition if you use countif or sumif?
> 1 c
> 1 c
> 2 < countif(b1:b4,"=c")and(a1:a4,"=1")
I have a column in Excel, D7:D200, filled of real numbers. Also the cell F3
includes a number (variable cell).
I would like to count how many numbers of the column D7:D200 are >F3, but in
successive ranges of D column.
(That means how many numbers in: D7:D8 > F3, D7:D8 > F3, D7:D9 > F3…………….and
finally D7:D200 > F3).
To do that I have applied in E7 the formula:
Countif(D7:$D$7 ; “>F3”) and expand down to E200.
Unfortunately the above formula returns zero in all applied cells.
If I change the F3 in the formula using a stable number (2,3,4 etc) the
formula works, but...counting number of particular items in a list
I am making a monthly file of all vehicles sold during the month on
day to day basis. I want to have a count of particular type of
i. e. s number of saabs, and how many 9-2 or 9-3's.
Or chevroletss and how many of that trail balzers etc.
Thanks much for the help.
|Filename: pr_monthly_report_forum.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3559 |
+------------------------------------------------------------...counting amount of visitors
I made a website and it is working fine. Now I would like to insert a tool
with which I can see, how many visitors have been visiting my side.
How to do this?
Do a google search for "website counters" and you'll get hundreds of
"May your shadow be found in happy places." (Native North American)
"Smeer" <Smeer@discussions.microsoft.com> wrote in message
> I made a website and it is working fine. Now I would like to insert a tool
> with which...Count(*) with different period
The query I try to make is really difficult for me. I try to don't
make it with a personnal function but I'm not sure that is possible.
I have a Table with the following data (nb: format date dd/mm/yyyy)
Contract Number Start End
00001 01/01/2005 02/05/2008
00002 01/03/2004 21/08/2007
00003 01/03/2003 21/10/2007
00004 07/05/2003 21/11/2007
00005 01/12/2007 21/10/2010
I want to count how many contracts are activ for each month of 2007
So the res...Reply goes from different acct than received
One of my users is having this rather strange problem. She has 3 email
accounts configured in Outlook with different email addresses (from 2
domains). Sometimes (not all the time) when she replies to a message that
was sent to 'account1' (her default acct as well) the message will show it
will be sent from 'account2'. She has to (when remembered - we don't
remember all the time) change the account before sending. It only does it
some of the time. I'm certain it's doing it because I checked the email
header - it shows being sent to account1.