count between dates

I have dates in column A and names in column B. I need to count how many 
names there are say between 1 Jan & 8 Jan. Help before my brain explodes....
0
Utf
1/22/2010 7:18:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

8 Replies
820 Views

Similar Articles

[PageSpeed] 34

Here's how I'd do it, Vicki:  Create a helper column, say in N, 
"=AND(A2>=DATE(2010,1,1),A2<=DATE(2010,1,8))".  Then use =COUNTIF(N:N,TRUE) 
and it'll count how many rows have dates in that range.

Do you need to eliminate duplicate names?

--- "Vicki Leibowitz" wrote:
> I have dates in column A and names in column B. I need to count how many 
> names there are say between 1 Jan & 8 Jan. Help before my brain explodes....
0
Utf
1/22/2010 8:21:01 AM
Hi Bob.
Basically I have named the date range as "Date" and the name range as 
"Names". I have used another sheet for my formulas and have 2010/01/01 in 
cell B2 and 2010/01/08 in cell B3 (which I had to use for sumproducts).
I tried your formula but it comes back as FALSE?
Surely if I can sumproduct between dates there must be a way to 
countproduct? I don't need to eliminate any duplicates.
I am a new excel learner.
Your assistance is much appreciated.
Thanks,
Vicki

"Bob Bridges" wrote:

> Here's how I'd do it, Vicki:  Create a helper column, say in N, 
> "=AND(A2>=DATE(2010,1,1),A2<=DATE(2010,1,8))".  Then use =COUNTIF(N:N,TRUE) 
> and it'll count how many rows have dates in that range.
> 
> Do you need to eliminate duplicate names?
> 
> --- "Vicki Leibowitz" wrote:
> > I have dates in column A and names in column B. I need to count how many 
> > names there are say between 1 Jan & 8 Jan. Help before my brain explodes....
0
Utf
1/22/2010 8:38:04 AM
Hi,

=SUMPRODUCT((A1:A30>=J7)*(A1:A30<=K7)*(B1:B30<>""))

Where J1 and k1 are you first and last dates
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Vicki Leibowitz" wrote:

> Hi Bob.
> Basically I have named the date range as "Date" and the name range as 
> "Names". I have used another sheet for my formulas and have 2010/01/01 in 
> cell B2 and 2010/01/08 in cell B3 (which I had to use for sumproducts).
> I tried your formula but it comes back as FALSE?
> Surely if I can sumproduct between dates there must be a way to 
> countproduct? I don't need to eliminate any duplicates.
> I am a new excel learner.
> Your assistance is much appreciated.
> Thanks,
> Vicki
> 
> "Bob Bridges" wrote:
> 
> > Here's how I'd do it, Vicki:  Create a helper column, say in N, 
> > "=AND(A2>=DATE(2010,1,1),A2<=DATE(2010,1,8))".  Then use =COUNTIF(N:N,TRUE) 
> > and it'll count how many rows have dates in that range.
> > 
> > Do you need to eliminate duplicate names?
> > 
> > --- "Vicki Leibowitz" wrote:
> > > I have dates in column A and names in column B. I need to count how many 
> > > names there are say between 1 Jan & 8 Jan. Help before my brain explodes....
0
Utf
1/22/2010 9:19:01 AM
Hi,
I've tried everything. =SUMPRODUCT((A1:A30>=J7)*(A1:A30<=K7)*(B1:B30<>"")) 
gives me #NA.
Am I just being a complete idiot? I'm sure feeling like one.

"Mike H" wrote:

> Hi,
> 
> =SUMPRODUCT((A1:A30>=J7)*(A1:A30<=K7)*(B1:B30<>""))
> 
> Where J1 and k1 are you first and last dates
> -- 
> Mike
> 
> When competing hypotheses are otherwise equal, adopt the hypothesis that 
> introduces the fewest assumptions while still sufficiently answering the 
> question.
> 
> 
> "Vicki Leibowitz" wrote:
> 
> > Hi Bob.
> > Basically I have named the date range as "Date" and the name range as 
> > "Names". I have used another sheet for my formulas and have 2010/01/01 in 
> > cell B2 and 2010/01/08 in cell B3 (which I had to use for sumproducts).
> > I tried your formula but it comes back as FALSE?
> > Surely if I can sumproduct between dates there must be a way to 
> > countproduct? I don't need to eliminate any duplicates.
> > I am a new excel learner.
> > Your assistance is much appreciated.
> > Thanks,
> > Vicki
> > 
> > "Bob Bridges" wrote:
> > 
> > > Here's how I'd do it, Vicki:  Create a helper column, say in N, 
> > > "=AND(A2>=DATE(2010,1,1),A2<=DATE(2010,1,8))".  Then use =COUNTIF(N:N,TRUE) 
> > > and it'll count how many rows have dates in that range.
> > > 
> > > Do you need to eliminate duplicate names?
> > > 
> > > --- "Vicki Leibowitz" wrote:
> > > > I have dates in column A and names in column B. I need to count how many 
> > > > names there are say between 1 Jan & 8 Jan. Help before my brain explodes....
0
Utf
1/22/2010 10:01:01 AM
That suggests that you've got the #N/A error in at least one of the cells 
which are feeding into the formula.

Mike did, of course, mean "Where J7 and K7 are you first and last dates", 
rather than J1 and K1 (or he meant to change the J7 and K7 references in the 
formula to J1 and K1), but that would not give an #N/A error so you need to 
look at your input data values.
--
David Biddulph

"Vicki Leibowitz" <VickiLeibowitz@discussions.microsoft.com> wrote in 
message news:8E51E044-4DEB-4BF4-9FFE-975A81A64869@microsoft.com...
> Hi,
> I've tried everything. =SUMPRODUCT((A1:A30>=J7)*(A1:A30<=K7)*(B1:B30<>""))
> gives me #NA.
> Am I just being a complete idiot? I'm sure feeling like one.
>
> "Mike H" wrote:
>
>> Hi,
>>
>> =SUMPRODUCT((A1:A30>=J7)*(A1:A30<=K7)*(B1:B30<>""))
>>
>> Where J1 and k1 are you first and last dates
>> -- 
>> Mike
>>
>> When competing hypotheses are otherwise equal, adopt the hypothesis that
>> introduces the fewest assumptions while still sufficiently answering the
>> question.
>>
>>
>> "Vicki Leibowitz" wrote:
>>
>> > Hi Bob.
>> > Basically I have named the date range as "Date" and the name range as
>> > "Names". I have used another sheet for my formulas and have 2010/01/01 
>> > in
>> > cell B2 and 2010/01/08 in cell B3 (which I had to use for sumproducts).
>> > I tried your formula but it comes back as FALSE?
>> > Surely if I can sumproduct between dates there must be a way to
>> > countproduct? I don't need to eliminate any duplicates.
>> > I am a new excel learner.
>> > Your assistance is much appreciated.
>> > Thanks,
>> > Vicki
>> >
>> > "Bob Bridges" wrote:
>> >
>> > > Here's how I'd do it, Vicki:  Create a helper column, say in N,
>> > > "=AND(A2>=DATE(2010,1,1),A2<=DATE(2010,1,8))".  Then use 
>> > > =COUNTIF(N:N,TRUE)
>> > > and it'll count how many rows have dates in that range.
>> > >
>> > > Do you need to eliminate duplicate names?
>> > >
>> > > --- "Vicki Leibowitz" wrote:
>> > > > I have dates in column A and names in column B. I need to count how 
>> > > > many
>> > > > names there are say between 1 Jan & 8 Jan. Help before my brain 
>> > > > explodes.... 


0
David
1/22/2010 10:10:52 AM
Hi all,
Thanks for assistance. I started from scratch and managed to get the answers 
using the sumproduct formula:
=SUMPRODUCT((Date>=B2)*(Date<=B3))
So basic I could kick myself!
Thanks again.

"Vicki Leibowitz" wrote:

> Hi,
> I've tried everything. =SUMPRODUCT((A1:A30>=J7)*(A1:A30<=K7)*(B1:B30<>"")) 
> gives me #NA.
> Am I just being a complete idiot? I'm sure feeling like one.
> 
> "Mike H" wrote:
> 
> > Hi,
> > 
> > =SUMPRODUCT((A1:A30>=J7)*(A1:A30<=K7)*(B1:B30<>""))
> > 
> > Where J1 and k1 are you first and last dates
> > -- 
> > Mike
> > 
> > When competing hypotheses are otherwise equal, adopt the hypothesis that 
> > introduces the fewest assumptions while still sufficiently answering the 
> > question.
> > 
> > 
> > "Vicki Leibowitz" wrote:
> > 
> > > Hi Bob.
> > > Basically I have named the date range as "Date" and the name range as 
> > > "Names". I have used another sheet for my formulas and have 2010/01/01 in 
> > > cell B2 and 2010/01/08 in cell B3 (which I had to use for sumproducts).
> > > I tried your formula but it comes back as FALSE?
> > > Surely if I can sumproduct between dates there must be a way to 
> > > countproduct? I don't need to eliminate any duplicates.
> > > I am a new excel learner.
> > > Your assistance is much appreciated.
> > > Thanks,
> > > Vicki
> > > 
> > > "Bob Bridges" wrote:
> > > 
> > > > Here's how I'd do it, Vicki:  Create a helper column, say in N, 
> > > > "=AND(A2>=DATE(2010,1,1),A2<=DATE(2010,1,8))".  Then use =COUNTIF(N:N,TRUE) 
> > > > and it'll count how many rows have dates in that range.
> > > > 
> > > > Do you need to eliminate duplicate names?
> > > > 
> > > > --- "Vicki Leibowitz" wrote:
> > > > > I have dates in column A and names in column B. I need to count how many 
> > > > > names there are say between 1 Jan & 8 Jan. Help before my brain explodes....
0
Utf
1/22/2010 10:40:01 AM
I gathered thanks.
I figured it out - the formula turned out to be so basic!
I should start learning what the different error messages mean.
Thank you.

"David Biddulph" wrote:

> That suggests that you've got the #N/A error in at least one of the cells 
> which are feeding into the formula.
> 
> Mike did, of course, mean "Where J7 and K7 are you first and last dates", 
> rather than J1 and K1 (or he meant to change the J7 and K7 references in the 
> formula to J1 and K1), but that would not give an #N/A error so you need to 
> look at your input data values.
> --
> David Biddulph
> 
> "Vicki Leibowitz" <VickiLeibowitz@discussions.microsoft.com> wrote in 
> message news:8E51E044-4DEB-4BF4-9FFE-975A81A64869@microsoft.com...
> > Hi,
> > I've tried everything. =SUMPRODUCT((A1:A30>=J7)*(A1:A30<=K7)*(B1:B30<>""))
> > gives me #NA.
> > Am I just being a complete idiot? I'm sure feeling like one.
> >
> > "Mike H" wrote:
> >
> >> Hi,
> >>
> >> =SUMPRODUCT((A1:A30>=J7)*(A1:A30<=K7)*(B1:B30<>""))
> >>
> >> Where J1 and k1 are you first and last dates
> >> -- 
> >> Mike
> >>
> >> When competing hypotheses are otherwise equal, adopt the hypothesis that
> >> introduces the fewest assumptions while still sufficiently answering the
> >> question.
> >>
> >>
> >> "Vicki Leibowitz" wrote:
> >>
> >> > Hi Bob.
> >> > Basically I have named the date range as "Date" and the name range as
> >> > "Names". I have used another sheet for my formulas and have 2010/01/01 
> >> > in
> >> > cell B2 and 2010/01/08 in cell B3 (which I had to use for sumproducts).
> >> > I tried your formula but it comes back as FALSE?
> >> > Surely if I can sumproduct between dates there must be a way to
> >> > countproduct? I don't need to eliminate any duplicates.
> >> > I am a new excel learner.
> >> > Your assistance is much appreciated.
> >> > Thanks,
> >> > Vicki
> >> >
> >> > "Bob Bridges" wrote:
> >> >
> >> > > Here's how I'd do it, Vicki:  Create a helper column, say in N,
> >> > > "=AND(A2>=DATE(2010,1,1),A2<=DATE(2010,1,8))".  Then use 
> >> > > =COUNTIF(N:N,TRUE)
> >> > > and it'll count how many rows have dates in that range.
> >> > >
> >> > > Do you need to eliminate duplicate names?
> >> > >
> >> > > --- "Vicki Leibowitz" wrote:
> >> > > > I have dates in column A and names in column B. I need to count how 
> >> > > > many
> >> > > > names there are say between 1 Jan & 8 Jan. Help before my brain 
> >> > > > explodes.... 
> 
> 
> .
> 
0
Utf
1/22/2010 10:53:02 AM
Thanks for spotting my error David.

Vixter,

The formula =SUMPRODUCT((Date>=B2)*(Date<=B3))
will corectly count dates in a range but based upon the original post it 
doesn't answer the question.

>I have dates in column A and names in column B.

What if column B has blanks in?
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Vixter" wrote:

> I gathered thanks.
> I figured it out - the formula turned out to be so basic!
> I should start learning what the different error messages mean.
> Thank you.
> 
> "David Biddulph" wrote:
> 
> > That suggests that you've got the #N/A error in at least one of the cells 
> > which are feeding into the formula.
> > 
> > Mike did, of course, mean "Where J7 and K7 are you first and last dates", 
> > rather than J1 and K1 (or he meant to change the J7 and K7 references in the 
> > formula to J1 and K1), but that would not give an #N/A error so you need to 
> > look at your input data values.
> > --
> > David Biddulph
> > 
> > "Vicki Leibowitz" <VickiLeibowitz@discussions.microsoft.com> wrote in 
> > message news:8E51E044-4DEB-4BF4-9FFE-975A81A64869@microsoft.com...
> > > Hi,
> > > I've tried everything. =SUMPRODUCT((A1:A30>=J7)*(A1:A30<=K7)*(B1:B30<>""))
> > > gives me #NA.
> > > Am I just being a complete idiot? I'm sure feeling like one.
> > >
> > > "Mike H" wrote:
> > >
> > >> Hi,
> > >>
> > >> =SUMPRODUCT((A1:A30>=J7)*(A1:A30<=K7)*(B1:B30<>""))
> > >>
> > >> Where J1 and k1 are you first and last dates
> > >> -- 
> > >> Mike
> > >>
> > >> When competing hypotheses are otherwise equal, adopt the hypothesis that
> > >> introduces the fewest assumptions while still sufficiently answering the
> > >> question.
> > >>
> > >>
> > >> "Vicki Leibowitz" wrote:
> > >>
> > >> > Hi Bob.
> > >> > Basically I have named the date range as "Date" and the name range as
> > >> > "Names". I have used another sheet for my formulas and have 2010/01/01 
> > >> > in
> > >> > cell B2 and 2010/01/08 in cell B3 (which I had to use for sumproducts).
> > >> > I tried your formula but it comes back as FALSE?
> > >> > Surely if I can sumproduct between dates there must be a way to
> > >> > countproduct? I don't need to eliminate any duplicates.
> > >> > I am a new excel learner.
> > >> > Your assistance is much appreciated.
> > >> > Thanks,
> > >> > Vicki
> > >> >
> > >> > "Bob Bridges" wrote:
> > >> >
> > >> > > Here's how I'd do it, Vicki:  Create a helper column, say in N,
> > >> > > "=AND(A2>=DATE(2010,1,1),A2<=DATE(2010,1,8))".  Then use 
> > >> > > =COUNTIF(N:N,TRUE)
> > >> > > and it'll count how many rows have dates in that range.
> > >> > >
> > >> > > Do you need to eliminate duplicate names?
> > >> > >
> > >> > > --- "Vicki Leibowitz" wrote:
> > >> > > > I have dates in column A and names in column B. I need to count how 
> > >> > > > many
> > >> > > > names there are say between 1 Jan & 8 Jan. Help before my brain 
> > >> > > > explodes.... 
> > 
> > 
> > .
> > 
0
Utf
1/22/2010 12:36:01 PM
Reply:

Similar Artilces:

calculating hours into date format
hi I would like a formula that will allow a user to input a figure (hours and minutes) into a cell that will calculate the end date and time based on a start date and time. For example, Enter 30 hours, static start time is 01/01/04 00:00hrs, the formula would then calculate that the end figure is 02/01/04 06:00hrs. Essentially it is calculating the number of hours & mins added onto the start date with the end result a date and time later than the hours & mins entered Hope this makes sense! Hi in A1 put your date in B1 enter your hours in the format hh:mm C1: =A1+B1 and forma...

Date Navigator
I use Outlook 2002 ... this just started happening & I do not know what to do to resolve. Any assistance would be appreciated. In Date Navigator portion of Calendar, January 4, 2007 is bolded even though I have no appointments on that day? How can I get this date unbolded? Thank you - EJS ...

Insert date en time with onchange function
Hi, I have a ntext field in a service indenity . When it is edited i want automaticly insert date en time. Can anyone help? Kind regards Maarten Kievit You may review this website on using Javascript to get time. http://www.tizag.com/javascriptT/javascriptdate.php In your onChange event for that field, you may add var currentTime =3D new Date() var month =3D currentTime.getMonth() + 1 var day =3D currentTime.getDate() var year =3D currentTime.getFullYear() crmForm.all.[Your Field].DataValue =3D month + "/" + day + "/" + year; hope this helps. Darren Liu, Microso...

add a date without holidays
Hi friends, I have a problem, in a macro I need to calculate a date adding working days only. This add not include saturday / sundays and holidays. For example: 21/5 + 6 days = 31/5 How i can to do ? Thanks in advance and sorry by my english, Christian For adding days, use the DateAdd function. It won't return an invalid date like 31/5. The Weekday function returns the number of the day, for instance this Weekday("22.05.2010" ,vbUseSystemDayOfWeek) returns 6 for Saturday, tomorrow it will return 7 for Sunday. For holidays I can't help...

Lookup for oldest date among different dates
Hi all, I badly need your help in the below formula Example: Structure : Account includes Sub-accounts and each sub-account includes dials Formula : Account activation date = Oldest Sub-account activation date where there is more than one account and hence different activation date for each account and in turn the sub-accounts that belong to each account Below the example A B C D Account # Sub-Account # Account Sub-account activation activation ...

date is in 20010129 format, how can I get it in m/d/y format
-- jsanders Data>text to column, go to step three and select date and YMD and for future posts it's considered rude just using the subject line Regards, Peo Sjoblom With the value 20010129 in cell A1, enter this formula in B1: =DATEVALUE(MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4)) and the format the cell as a date. The various functions parse the 20010129 string into its components, and the DATEVALUE converts that string to a datevalue that Excel can recognize. ...

How to create a calendar from date information in XL sheet.
I have an XL spreadsheet that contains multiple columns, one of which contains data in date & time format. Is there a way to export this data and associated information from the record to another application, for example Word, and create a calendar? Any suggestions how to manipulate this XL data are welcomed. Thanks! ...

cell value goes up by one count
,Hello Again Could i get some help with this. How does one get an activecell , that has a value of 1, that is copied from sheet1 to sheet2 so that the value changes from 1 to a 2 and so on depending on how many times I copy a sheet. I hope this makes sense Thanks in Advance Allan =sheet1!(a1). I think you are asking for this formula. It will change the value of current cell (doesn't matter on which sheet you are currently working) to value of Sheet 1's A1 cell. Means if Sheet 1 A1 = 100 and you are currently working on Sheet 2 F1, then this formula will show the F1 value ...

Current Date 01-20-10
I would like to filter on todays date. What is the field name for the current date. Thanks. Harry Try "Current Date." - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > I would like to filter on todays date. What is the field name for the > current date. Thanks. > > Harry > ...

counting dates #2
in cells D & E i have a series of dates. I want to put a formular E - D = calculated number of days and i also dont want it calculating weekend? the purpose of this is excel to work out how many days it takes to serve a document is this possible and if so what is the formular please =NETWORKDAYS(Start_date,End_date,holidays) Holidays would be a range containing the dates of public holidays you wish to de excluded from your calculation. for example in your case, if you entered the range of holiday dates in cells H1:H10 =NETWORKDAYS(D1,E1,H1:H10) -- Regards Roger Govier "jenn...

Query Count if Function QQQQssss
I have a range where Attendees of a convention either pre-register, Compted, Pre-register Only, or Register on site. For the on-site regitered they can register on Friday Saturday or Sunday. (that is a Different Range) I want to count "Registered" In the registered Range; From each Day EG Friday, Saturday, Sunday (Date Range) I can countif Dates and Registered but how do I make it count on two different Cryteria Hi Mark! You can use SUMPRODUCT as one approach to counting on more than one criteria: =SUMPRODUCT((WEEKDAY($A$1:$A$24)=1)*($B$1:$B$24="Registered")) -- -- R...

Hide Alternate Labels on Date Axis
Hi, I'm having trouble trying to hide every other label along a horizontal date axis. I start on the 01-Jan up to 10-Jan and through using the 'Format Axis'->'Scale'->'Major Unit' and setting this to 2 I can hide the labels: '02-Jan'; '04-Jan'; etc. However, I want to hide the odd dates: '01-Jan'; '03-Jan'; etc. I have tried the various combinations with the dialog box but can't get anything to work. Should I be using a custom formula or something? Any help is much appreciated. Paul. In article <1168177092.892944...

Subtracting minutes from a date
Is there a way to subtract 15000 minutes from todays date and show the results in date format. A1 = todays date b1= #minutes c1= A1-b1 (the date that many minutes ago) =A1-(B1/(24*60)) Format as Date -- Kind Regards, Niek Otten Microsoft MVP - Excel "J. T. SYLVESTER SR." <sylvester1501@peoplepc.com> wrote in message news:663e99ad.0410011045.29cd532b@posting.google.com... > Is there a way to subtract 15000 minutes from todays date and show the > results in date format. > A1 = todays date > b1= #minutes > c1= A1-b1 (the date that many minutes ago) Su...

Multiple date validation in a single cell
I am trying to do a rather complicated validation (I think it i anyway!) I want to allow ONLY dates of a particular format to be entered int the one cell - i.e. YYYY OR mm/YYYY OR dd/mm/YYYY For example, the same cell can have either one of these format entered, but MUST match one of them to be accepted. 1970 is OK and is captured in the cell as 1970 (not 1/01/1970) 70 is assumed to mean 1970 on entry 05/1970 is OK, captured in the cell as 05/1970 (not 1/05/1970) 5/70 or May-70 is assumed to mean 05/0970 on entry 05/06/2004 is OK 5/6/04 or 5 June 04 is assumed to mean 05/06/2004 on entr...

Another question regarding Dates..
First, thanks to the replies to my previous post. How can I work out the day & month when I only know the Year and Day Number? Ie Year = 2004 day number = 302 The answer I'm looking for is 28/10/2004 Again, taking into consideration the leap years Thanks again in advance =DATE(2004,1,302) Regards, Peo Sjoblom "Anthony Slater" wrote: > First, thanks to the replies to my previous post. > > How can I work out the day & month when I only know the Year and Day Number? > > Ie > > Year = 2004 > day number = 302 > > The answer I'...

Formula for counting cells with value 'x' provided Col U = 'y'
Hi guys, Need some help with a formula please. Have tried myself and know that the answer is straightforward but going through a mental block! Col Q Col R A01 58 A01 62 P02 62 Both columns data starts in cell 14 and runs down to cell 10000. I need to count the entries in Column Q that = A01 ONLY IF Col R = 62. Then do the same for Q = A01 ONLY IF R = 58, and so on. The worksheet is a year-to-date file and will be updated weekly with many entries in both columns with varying values. Many thanks. Hi, =SUMPRODUCT((Q1:Q10000="A01")*(R1:R1000...

Count Number of Subgroups in Group Header
I have a report with two groups and a detail section. The groups are Location and Person and the detail lists services. I already have a count of each service per person and each service per location. What I need is a count of each person per location. I've done this in the Location footer with no problem, but I need to show it in the Location header instead. I have a text box with a control of =1 that sums over the group in the Person group header. I just refer to that text box in my location footer and all is good. But when I try to refer to that same text box from m...

Date an time on WinXP.
Does anyone know where windows Xp gets the time and date it displays from? My system date and time often fails, resulting in the time beiing 5-5mn delayed compared to what it shoul have been . I wonder what can cause that???? ...

how do I stop Excel taking decimal fractions as dates?
sometimes when I enter decimal fractions Excel thinks this is a date. How do I stop this? Hi Insert a single quote before the fraction '3/4 The quote will not show up in the cell, but it will tell Excel to treat the cell as Text. Alternatively, Format>Cells>Number>Fractions 3/4 will display as 3/4 but will be stored as 0.75 and can be used in further calculations. -- Regards Roger Govier "aa" <A@aa.com> wrote in message news:efV2MhV9GHA.4376@TK2MSFTNGP03.phx.gbl... > sometimes when I enter decimal fractions Excel thinks this is a date. > How do ...

Count formulas
I am trying to do a set of 3 counts between 1-100. Under 80 Between 80-90 and 90+. I have 2 formulas that are working and can't seem to figure the 3rd, could anyone help? 1st =COUNTIF(G4:K22,">89.99") 2nd =COUNTIF (G4:K22,"<79.99") It is the middle one that I am not able to figure out. count all -those two or =sumproduct((g4:k22>=80)*(f4:k22<90)) -- Don Guillett SalesAid Software donaldb@281.com "Barb" <anonymous@discussions.microsoft.com> wrote in message news:045801c3d85f$f193bdb0$a401280a@phx.gbl... > I am trying to do a set of...

i need monthly ledger that is catagorized by payment due dates
"j.marie" <j.marie@discussions.microsoft.com> wrote in message news:9C223E6B-0C7C-46AA-BEAE-2B49F97D4949@microsoft.com... > Nothing. PLEASE write your question in the body of the post, not in the subject line. This is not a chatroom. Thankyou. In answer to your question, you would be better advised to buy an inexpensive dedicated accounting package. That will do what you want "out of the box". ...

Counting Three Different Words In One Column
H ithere, Can someone please help me? I have a column which can only have option (due to validation), these are:- New Overdue Updated Closed I would like to count the number of times each one has been entered. Each count would be in a different cell on a different worksheet. have tried searching this forum and found answers similar to what i' looking for but i could not get any of them to work for some reason? Thanks very much for your time. Swmasso -- Message posted from http://www.ExcelForum.com Hi you need to use the countif function for this so if your range is ...

date time problem
I would like to have a date cell that can have an input mask of __/__/__ so that a user need only enter the numerics of a given date; i.e 052405. I I tried setting the cell format to custom mm/dd/yy and I get a return date of 10/1/43 !!!!!???? Why has excel lost the correct date? Regions global show the correct year range, and Excel is set to 1900 time? What do I need to do to correct this? Thanks XL's parse engine and display engine are separate - the displayed format has nothing to do with how the entry is parsed (other than Text entries are not parsed at all). For a workaro...

Date format 03-03-08
How do I get a date to print as mm-dd-yy in a field? On Sun, 02 Mar 2008 23:43:02 -0500, BC wrote: > How do I get a date to print as mm-dd-yy in a field? Set the format property of the Date control to: mm-dd-yy -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail ...

Appending or Up-dating a formula
I am using an =Ave function in a formula to average quite a few cell values together. I add new information all the time; sometimes 5 or 6 new cells at a time. How can I append or up-date my formula to include these new cells with out have to type the new cells into the formula? When I make the cell that has the formula in it active/edit, all of the cells that are referenced in the formula have a highlight around them. Is there a key or key combination I can press while clicking on the cells I want to add to the formula? Thanks, john Hi see your post in Excel.misc -- Regards Frank ...