Counting specific test in a column in another worksheet that fall between entered dates

Hi,

I'm trying to count the amount of times a txt string appears associated 
with a date on a different worksheet between two separately entered 
dates on the sheet were the calculation is being performed. Im using 
this formula but it only returns the 'value if false' even-though the 
logical test result is true i.e Derbyshire has been entered into the 
sheet on the 09/09/09 :

=IF(('Tests 1'!$D:$D="Derbyshire"),(COUNTIF('Tests 
1'!A:A,">="&$N$22)-COUNTIF('Tests 1'!A:A,">"&$O$22)),"")

Thanks for any input
-- 
Steve

If you don't find it in the index, look very carefully through the 
entire catalogue.
- Unknown
0
9/14/2009 1:14:31 PM
excel 39879 articles. 2 followers. Follow

4 Replies
549 Views

Similar Articles

[PageSpeed] 45

Try it this way:

=3DSUMPRODUCT(('Tests 1'!D1:D100=3D"Derbyshire")*('Tests 1'!A1:A100>=3D$N$2=
2)
*('Tests 1'!A1:A100<=3D$O$22))

Assumes that N22 holds the start date and O22 the end date. Also
assumes 100 rows of date - adjust to suit what you have.

Hope this helps.

Pete

On Sep 14, 2:14=A0pm, Steve <stevebak...@bigfoot.com> wrote:
> Hi,
>
> I'm trying to count the amount of times a txt string appears associated
> with a date on a different worksheet between two separately entered
> dates on the sheet were the calculation is being performed. Im using
> this formula but it only returns the 'value if false' even-though the
> logical test result is true i.e Derbyshire has been entered into the
> sheet on the 09/09/09 :
>
> =3DIF(('Tests 1'!$D:$D=3D"Derbyshire"),(COUNTIF('Tests
> 1'!A:A,">=3D"&$N$22)-COUNTIF('Tests 1'!A:A,">"&$O$22)),"")
>
> Thanks for any input
> --
> Steve
>
> If you don't find it in the index, look very carefully through the
> entire catalogue.
> - Unknown

0
pashurst (2576)
9/14/2009 1:31:34 PM
  Pete_UK penned:
> Try it this way:

> =SUMPRODUCT(('Tests 1'!D1:D100="Derbyshire")*('Tests 1'!A1:A100>=$N$22)
> *('Tests 1'!A1:A100<=$O$22))

> Assumes that N22 holds the start date and O22 the end date. Also
> assumes 100 rows of date - adjust to suit what you have.

> Hope this helps.

Hi Pete,

Thanks the formula:

=SUMPRODUCT(('Tests 1'!D:D="Derbyshire")*('Tests 1'!A:A>=N22)*('Tests 
1'!A:A<=O22))

works like a dream

-- 
Steve

A bank is a place that will lend you money if you can prove that you 
don't need it.
- Bob Hope
0
9/14/2009 2:37:09 PM
You're welcome, Steve - thanks for feeding back.

Note, though, that you can only use full-column references in XL2007.
If you need to apply this on earlier versions, you would have to have
ranges to suit your data.

Pete

On Sep 14, 3:37=A0pm, Steve <stevebak...@bigfoot.com> wrote:
> =A0 Pete_UK penned:
>
> > Try it this way:
> > =3DSUMPRODUCT(('Tests 1'!D1:D100=3D"Derbyshire")*('Tests 1'!A1:A100>=3D=
$N$22)
> > *('Tests 1'!A1:A100<=3D$O$22))
> > Assumes that N22 holds the start date and O22 the end date. Also
> > assumes 100 rows of date - adjust to suit what you have.
> > Hope this helps.
>
> Hi Pete,
>
> Thanks the formula:
>
> =3DSUMPRODUCT(('Tests 1'!D:D=3D"Derbyshire")*('Tests 1'!A:A>=3DN22)*('Tes=
ts
> 1'!A:A<=3DO22))
>
> works like a dream
>
> --
> Steve
>
> A bank is a place that will lend you money if you can prove that you
> don't need it.
> - Bob Hope

0
pashurst (2576)
9/14/2009 3:04:00 PM
  Pete_UK penned:

> Note, though, that you can only use full-column references in XL2007.
> If you need to apply this on earlier versions, you would have to have
> ranges to suit your data.

Thanks for reminding me a couple of the users are running XL2003

-- 
Steve

2 is not equal to 3, not even for large values of 2.
- Grabel's Law
0
9/14/2009 4:16:11 PM
Reply:

Similar Artilces:

A Combination Chart using a Column and Line
I have a large amount of data about protein values and I have grouped these using the Analysis-Pak Histogram wizard. The resulting frequencies have been charted using the 'Column' Chart Type. Using the NORMDIST function I then plotted the appropriate Normal Curve as a 'Line' Chart Type on the same chart but using a secondary y-axis. I was wondering if there is any sensible way of scaling the NORMDIST values so that both sets of data can be charted on the same axis. Any suggestions will be gratefully received. ...

How to run a program from another
Hi, I need to run another program from a main program, ie. assume that there are two programs, mainprogram.exe and subprogarm.exe. Now while running the mainprogram.exe, the program needs to start the program subprogram.exe. What instruction(s) do I need to add to the C++ code for mainprogram to accomplish this? Thanks in advance, Raj try to use CreateProcess and pass the filename in 2nd parameter! "Rajesh" <anonymous@discussions.microsoft.com> wrote in message news:2d18c01c39450$47cd0960$a601280a@phx.gbl... > Hi, > > I need to run another program from a ma...

Format a certain column
I would like to format a certain column, meaning for example the 5th column from the left to be seen. This should not automatically be the E-column!! If for example the B and C column are hidden, I want the format to be for the G-column. Does anybody have an idea how to do that?? Thanks, Teresa, By "format," you mean use a certain fill color, or something like that (the Excel meaning of "format")? If so, a macro will have to do it if columns are being hidden and unhidden, deleted and inserted, and the formatting must change dynamically. -- Earl Kiosterud www.smoke...

Date white-out in Outlook 2002
Hi again. I'm not sure what causes it, but when viewing my calendar and clicking on any date, that specific day's number show white causing me not to see it. Is there a way I can fix this? -- Regards, Deon H what windows theme are you using? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchang...

Replicating Production data in Test environment
our customer is looking to replicate there production data dynamics 3.0 into a Q/A we already have an existing Q/A environment set up but the data is outdated. We have an integration set up between 8 Great Plains companies & a propriety DB, using Scribe. What would be the best way to mirror the data? It would be simple to copy the Great Plains database; I think we could update the Scribe Keycross reference two way table. But the CRM data is not as transportable. Would using the CRM redeployment be an option? I have used this one before but only to move the database server within the...

line-column combination chart, with stacked columns
I am trying to make a combination chart with stacked colums and an overlayed line (or data point), just like it is possible to make a combination chart with "regular" bars and a line. Hi, Have you tried creating the combination chart yourself? Create a stacked columns chart, including the series you want to plot as a line. Then select this series and use Chart > Chart Type to change the series into a line chart of your choice. Cheers Andy annewcpss wrote: > I am trying to make a combination chart with stacked colums and an overlayed > line (or data point), just like ...

Drag a file(path) from an Access Form into another app?
I’ve read all about dragging files INTO Access forms, and dragging and dropping data between controls on Access forms. I need to do something different: MyApp has a subform with a list of files in a folder on the local HD. Currently I have buttons that opens the file using a Shell() command. The Users want to be able to drag files out of the list (on the subform in Access) and into other programs (like Notepad or Word), and have them open just like you dragged a file there out of windows explorer. I downloaded modules that “expose that Windows API” before, I’m assuming ...

Enter Network Password
I am repeatedly being prompted for a network password for my mail server. I followed the MS advice to install Office SP1 and SP2 and have checked the accounts set up to ensure there is only one account. I have also followed the advice to edit the registry and remove the Key as specified in the Knowledge base. I am still being prompted. Any ideas please. Clearly this isn't serious but it is annoying. I think it began about a month or so ago. All the best Dr Dodge There have been several recent issues with passwords not being retained with Outlook. If you're running Outlook ...

How do I calculate a persons age from birth dates
Wanting a formula to calculate a persons age from birth to today's date this will do age Put your birthday in cell A1 and test =DATEDIF(A1,TODAY(),"Y") & " Years old " this will do years months and days =DATEDIF(A1,TODAY(),"Y") & " Years, " & DATEDIF(A1,TODAY(),"YM") & " Months, " & DATEDIF(A1,TODAY(),"MD") & " Days" "Rod Bishop" wrote: > Wanting a formula to calculate a persons age from birth to today's date =DATEDIF(A1,TODAY(),"y") Regards...

How to enter a new Office 2004 serial number.
Hello All, Does anyone know how to use a new serial number? I had to buy another license for a machine that already had Office 2004 installed. "Bucky" <dh@plushstudios.com> wrote in message news:1103668044.322182.288040@c13g2000cwb.googlegroups.com... > Hello All, > Does anyone know how to use a new serial number? I had to buy another > license for a machine that already had Office 2004 installed. It was some time since I installed Office for Mac but I seem to remember that Office will ask for a serial number as soon as you start any program in the Office suite. ...

test post #3
I've posted a couple messages today that havn't shown up. Just testing my account. Frank ...

Linking/refreshing data across worksheets
This is a very basic question, but has me stumped nonetheless. I have one worksheet which contains a list of my "master data", while several other worksheets in the workbook reference this list of data using an absolute reference i.e. ='Food Master'!B20 Is there an easy way to keep the data synced across worksheets if I insert a row into my master worksheet? FYI, I do not have this data saved as a list. Is there a way to accomplish this with absolute references or do I need to use a pivot table, lookup table, etc.? I'm trying to keep this simple if at all pos...

Data from 1 sheet to another sheet in same worksheet but then sorted....
I'm importing data from other worksheets with the database query function, this does carry a possibility to sort but due to specific matters I can't use the sorting function. My idea is now to have the data thus important automatically listen on another sheet in the same worksheet but on another sheet but this time sorted according to certain criteria. I'm sure this is easy with lists etc and filters, but what I'm looking for is something simple where even I can work with. Your suggestions are very welcome, thanks, martin ...

Min value excluding 0 and another criteria
Hi I am trying to exclude zero plus add another criteria in calculating the min value of a column. I want the other criteria to be the text value of a different column. For instance column A has dog, cat, fish, etc (up to 16 category types) and column B has age. I am trying to find min age for each category and not include zero. I know this will find the min age of everything =MIN(IF($G$5:$G$40>0, $G$5:$G$40)) but i am at a lost for how to incoporate the other criteria any suggestions would greatly be appreciated On Apr 24, 10:41=A0pm, calebmich...@gmail.com wrote: > Hi > >...

Attachments to one address and not another?
Outlook 2007 - Is it possible to send an attachment to an email address (ex -in the 'To' field), but not send the attachment to another address (ex - in the 'Bcc' field) for the same message? No. TimBCC wrote: > Outlook 2007 - Is it possible to send an attachment to an email address (ex > -in the 'To' field), but not send the attachment to another address (ex - in > the 'Bcc' field) for the same message? Didn't think so, but thought I'd ask. Thanks. "Bob I" wrote: > No. > > TimBCC wrote: > &...

Column/Row Formating
I am using Office XP which inclued Excel 2002; my problem is my row/column formating will occasionally change without any input from me. I have several cells merged and do not have width set to adjust for the amount of text,just text wrap. Any ideas, I have to keep correcting my formats. Thanks, Gary C. Gary Merged cells will allow "wrap text" from Format>Cells>Alignment but.......... Excel has a problem auto-fitting merged cells. You can resize manually or via Macro. Jim Rech has written code for this. http://groups.google.com/groups?threadm=e1%241uzL1BHA.1784%40tkm...

In Excel, why sort function fails when applied to a list of date?
...

X axis on 0 on Line-Column on 2 Axes
Is there a way on a Line-Column on 2 Axes chart to have the 0 value on both the bar and line axes be straight across from each other even though the bar chart is in 1,000s and the line axes is in single digits and has negative numbers? Only way I can figure is to put negative numbers in the bar axis as well. Try this: http://peltiertech.com/Excel/Charts/AlignXon2Ys.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Supe" <Supe@discussions.microsoft.com> wrote in message news:F1174954-513D-...

Calculating Difference Between Start Date & Time And End Date & Ti
I have a table that needs to calculate how long a job has been open for: Start Date Start Time End Date End Time Time Call Open (H) The cells need to remain seperate. Can someone please tell me how I calculate the time in hours between the two. I am using excel 2000. I do not need to take anything special into consideration (ie weekends or holidays) it is just a straight calculation. I have tried many ways to do this, but I can't HEEEELLLLPPPP! You already have an answer from Nick Hodge, posted 08:13. Pete On Mon, 19 Dec 2005 02:55:03 -0800, Samwar <...

imported impromtu report date issue
I am saving a cognos imrpomtu report in excel but not formatted up by report writer. It is giving me trouble in date fileds maybe. I need to understand that excel is reading date right I think the filed is orginally a date and time but not sure. Do I use a formula to fix or secire this date or do I just format as date in excel. If I format date and time I get the time as 12am un all fields so i assume from this no time was exported wow- thanks Hi What does the date field show in Excel, when formatted as General? Andy. "Todd F." <ToddF@discussions.microsoft.com> wr...

Filter data from one collum by looking up from another column
Hi, I am stuck on the following problem: I want to exclude names (or give them another color) from 1 column if they are also in another column. To illustrate this, the result from the following data: A B 1 John Peter 2 Sue Floyd 3 Peter Dan 4 Mike Chris would have to be: John Sue Mike Or in this case I could use a conditional formatting to paint A3 red. How do I do this? Try something like this: Using your posted example: Select A1:A4, with A1 as the active cell From the Excel main menu: <format><conditional formatting> Formula is: =COUNTIF($A$1:$B1,A1)>1 Click the...

Enter the same value for a group cells.
How do I enter the same value for a group of selected cells at once? Thank You. Select the cells go to the formula bar and enter the value do Ctrl+Enter rather than just enter to finish. -- Regards, Tom Ogilvy "Raymond" <NotValidEmail@yahoo.com> wrote in message news:-LmdnbFhO-O_4WPdRVn-iQ@comcast.com... > How do I enter the same value for a group of selected cells at once? > > Thank You. > > ...

Excel.exe has generated error/Days calculation from date
An error message occure while opening specific file "Excel.exe has geneated Errors and will be closed by the windows you need to restart the program." How can we get the No. of days using two date calculation because days360() is not solving the problem. I would highly appreciate for help ...

merging worksheet by email
How does one execute merging a worksheet with someone by email? If the emailer has the exact same headers and emails the worksheet, how can I merge the list of names and addresses into my sheet alphabetically? Is this possible to do? TIA Bob Bob, Open both, copy the new data from the emailed worksheet, paste it at the bottom of your worksheet, then select the entire range and sort based on the appropriate key(s). HTH, Bernie "bb" <bb@earthlink.net> wrote in message news:3sklb.529$wc3.283@newsread3.news.pas.earthlink.net... > How does one execute merging a worksheet...

Conditional Formatting
Could anyone help with the following conditional formatting questions. Have a spreadsheet with a list of training dates, need to be able to apply the following conditions: 1) Colour the cell in Amber when the cell contains a date < one month ahead of today 2) Colour the cell in Green if the cell's date is > 1 month away 3) Colour the cell in Red if the cell's date is < today. Hope this makes sense, this would mean the following for the dates below: Cell A1: 7th September 2003 (AMBER) Cell A2: 10th October 2003 (GREEN) Cell A3: 1st August 2003 (RED). Appreciate you...