A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

COUNTIF greater than one column and less than another



 
 
Thread Tools Display Modes
  #1  
Old May 7th 09, 10:08 PM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 216
Default COUNTIF greater than one column and less than another

I am attempting to write a COUNTIF statement with greater than and less than
functionality. I have 2 worksheets.

Sheet 1 contains the extracted data from a database – this cannot be
changed.
Row A is a sequential number of the items.
Row B contains the status (New, In Progress, Closed).
Row C contains the day and time the item was created.
Row D contains the day and time the item was closed.
Cell F2 contains: =MONTH(C2)&"-"&YEAR(C2)
Cell G2 contains: =IF(B2="Closed", MONTH(C2)&"-"&YEAR(C2),"")

Sheet 2 has the 6 month reporting period as shown below:
Cell B1 is for the user to enter the MM-YYYY of the first reporting month
Cell B2 contains the following formula: =DATE(YEAR(B1),MONTH(B1)+C2,DAY(B1))
to return the MM-YYYY 1 greater than the month in cell B1. This continues
through B6 so I have 6 months in a row.
Cell A16 says: =B1 to return the MM-YYYY of the first month for reporting.
Cell B16 says: =B2 and so on through cell F16
Row 17 is where I want the results of the COUNTIF formula to begin.
Cell A17 says: =COUNTIF(Sheet1!$F:$F,Sheet2!A16) to tell me how many were
opened in the month displayed in cell A16.
Cell A18 says: =COUNTIF(Sheet1!$G:$G,Sheet2!A16) to tell me how many were
closed during the month displayed in cell A16.
Now I have the number of items opened and the number of items closed in each
month.

What I need to know is how many OPEN items I had at the beginning of the
current month. Remember that the months in row 16 will changed based on what
the user enters in cell B2. (This workbook is to be used as a template so it
will constantly be changing.)

I've tried using the formula for COUNTIF in the help section:
=COUNTIF(Sheet1!$F:$F,"<Sheet2!A16")-COUNTIF(Sheet1!$G:$G,">Sheet2!B16") to
tell me how many were open at the beginning (12:01am) of the second month.
The result was 11 which is incorrect as only 1 item was created in the first
month and it is still open. So the result should have been 1.

Thanks for any help.

Ads
  #2  
Old May 7th 09, 10:53 PM posted to microsoft.public.excel.worksheet.functions
JBeaucaire[_90_]
external usenet poster
 
Posts: 222
Default COUNTIF greater than one column and less than another

I use SUMPRODUCT() to accomplish counting like you're describing. This
function is WAY more flexible than people realize at first. This link has a
wonderful tutorial and the really good stuff is down the sheet a ways past
the "Advantages of Sumproduct"

http://www.xldynamic.com/source/xld....T.html#classic

Read up on that. Then I imagine a formula for count of open items spanning
many months to be something like this:

=SUMPRODUCT(--(RangeOfOpeningDates>=OpeningDate),
--(RangeOfOpeningDates<CutoffOpenDate),
--(RangeOfClosingDates>CutoffOpenDate))

Now, just insert the correct ranges and cell references for those values.
With Sumproduct, you can't refer to whole columns like you can with COUNTIF
unless you're using Excel 2007
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Nadine" wrote:

> I am attempting to write a COUNTIF statement with greater than and less than
> functionality. I have 2 worksheets.
>
> Sheet 1 contains the extracted data from a database – this cannot be
> changed.
> Row A is a sequential number of the items.
> Row B contains the status (New, In Progress, Closed).
> Row C contains the day and time the item was created.
> Row D contains the day and time the item was closed.
> Cell F2 contains: =MONTH(C2)&"-"&YEAR(C2)
> Cell G2 contains: =IF(B2="Closed", MONTH(C2)&"-"&YEAR(C2),"")
>
> Sheet 2 has the 6 month reporting period as shown below:
> Cell B1 is for the user to enter the MM-YYYY of the first reporting month
> Cell B2 contains the following formula: =DATE(YEAR(B1),MONTH(B1)+C2,DAY(B1))
> to return the MM-YYYY 1 greater than the month in cell B1. This continues
> through B6 so I have 6 months in a row.
> Cell A16 says: =B1 to return the MM-YYYY of the first month for reporting.
> Cell B16 says: =B2 and so on through cell F16
> Row 17 is where I want the results of the COUNTIF formula to begin.
> Cell A17 says: =COUNTIF(Sheet1!$F:$F,Sheet2!A16) to tell me how many were
> opened in the month displayed in cell A16.
> Cell A18 says: =COUNTIF(Sheet1!$G:$G,Sheet2!A16) to tell me how many were
> closed during the month displayed in cell A16.
> Now I have the number of items opened and the number of items closed in each
> month.
>
> What I need to know is how many OPEN items I had at the beginning of the
> current month. Remember that the months in row 16 will changed based on what
> the user enters in cell B2. (This workbook is to be used as a template so it
> will constantly be changing.)
>
> I've tried using the formula for COUNTIF in the help section:
> =COUNTIF(Sheet1!$F:$F,"<Sheet2!A16")-COUNTIF(Sheet1!$G:$G,">Sheet2!B16") to
> tell me how many were open at the beginning (12:01am) of the second month.
> The result was 11 which is incorrect as only 1 item was created in the first
> month and it is still open. So the result should have been 1.
>
> Thanks for any help.
>

  #3  
Old May 8th 09, 12:24 AM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 216
Default COUNTIF greater than one column and less than another

I entered the formula
=SUMPRODUCT((Sheet1!F:F<Sheet2!C16)*(Sheet1!G:G>Sh eet2!C16)) and received
#NUM! as the result. Thanks for the link, though. I will keep it and
continue reading up on SUMPRODUCT to see if there's another way to write it
that will give me the result I need.
Nadine

"JBeaucaire" wrote:

> I use SUMPRODUCT() to accomplish counting like you're describing. This
> function is WAY more flexible than people realize at first. This link has a
> wonderful tutorial and the really good stuff is down the sheet a ways past
> the "Advantages of Sumproduct"
>
> http://www.xldynamic.com/source/xld....T.html#classic
>
> Read up on that. Then I imagine a formula for count of open items spanning
> many months to be something like this:
>
> =SUMPRODUCT(--(RangeOfOpeningDates>=OpeningDate),
> --(RangeOfOpeningDates<CutoffOpenDate),
> --(RangeOfClosingDates>CutoffOpenDate))
>
> Now, just insert the correct ranges and cell references for those values.
> With Sumproduct, you can't refer to whole columns like you can with COUNTIF
> unless you're using Excel 2007
> --
> "Actually, I *am* a rocket scientist." -- JB
>
> Your feedback is appreciated, click YES if this post helped you.
>
>
> "Nadine" wrote:
>
> > I am attempting to write a COUNTIF statement with greater than and less than
> > functionality. I have 2 worksheets.
> >
> > Sheet 1 contains the extracted data from a database – this cannot be
> > changed.
> > Row A is a sequential number of the items.
> > Row B contains the status (New, In Progress, Closed).
> > Row C contains the day and time the item was created.
> > Row D contains the day and time the item was closed.
> > Cell F2 contains: =MONTH(C2)&"-"&YEAR(C2)
> > Cell G2 contains: =IF(B2="Closed", MONTH(C2)&"-"&YEAR(C2),"")
> >
> > Sheet 2 has the 6 month reporting period as shown below:
> > Cell B1 is for the user to enter the MM-YYYY of the first reporting month
> > Cell B2 contains the following formula: =DATE(YEAR(B1),MONTH(B1)+C2,DAY(B1))
> > to return the MM-YYYY 1 greater than the month in cell B1. This continues
> > through B6 so I have 6 months in a row.
> > Cell A16 says: =B1 to return the MM-YYYY of the first month for reporting.
> > Cell B16 says: =B2 and so on through cell F16
> > Row 17 is where I want the results of the COUNTIF formula to begin.
> > Cell A17 says: =COUNTIF(Sheet1!$F:$F,Sheet2!A16) to tell me how many were
> > opened in the month displayed in cell A16.
> > Cell A18 says: =COUNTIF(Sheet1!$G:$G,Sheet2!A16) to tell me how many were
> > closed during the month displayed in cell A16.
> > Now I have the number of items opened and the number of items closed in each
> > month.
> >
> > What I need to know is how many OPEN items I had at the beginning of the
> > current month. Remember that the months in row 16 will changed based on what
> > the user enters in cell B2. (This workbook is to be used as a template so it
> > will constantly be changing.)
> >
> > I've tried using the formula for COUNTIF in the help section:
> > =COUNTIF(Sheet1!$F:$F,"<Sheet2!A16")-COUNTIF(Sheet1!$G:$G,">Sheet2!B16") to
> > tell me how many were open at the beginning (12:01am) of the second month.
> > The result was 11 which is incorrect as only 1 item was created in the first
> > month and it is still open. So the result should have been 1.
> >
> > Thanks for any help.
> >

  #4  
Old May 8th 09, 12:54 AM posted to microsoft.public.excel.worksheet.functions
JBeaucaire[_90_]
external usenet poster
 
Posts: 222
Default COUNTIF greater than one column and less than another

You need to read my original suggestion again. I mentioned that sumproduct
can't refer to an entire column. Simply use an actual range, sufficiently
large to cover your needs. Sumproduct is the function for this.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Nadine" wrote:

> I entered the formula
> =SUMPRODUCT((Sheet1!F:F<Sheet2!C16)*(Sheet1!G:G>Sh eet2!C16)) and received
> #NUM! as the result. Thanks for the link, though. I will keep it and
> continue reading up on SUMPRODUCT to see if there's another way to write it
> that will give me the result I need.
> Nadine
>
> "JBeaucaire" wrote:
>
> > I use SUMPRODUCT() to accomplish counting like you're describing. This
> > function is WAY more flexible than people realize at first. This link has a
> > wonderful tutorial and the really good stuff is down the sheet a ways past
> > the "Advantages of Sumproduct"
> >
> > http://www.xldynamic.com/source/xld....T.html#classic
> >
> > Read up on that. Then I imagine a formula for count of open items spanning
> > many months to be something like this:
> >
> > =SUMPRODUCT(--(RangeOfOpeningDates>=OpeningDate),
> > --(RangeOfOpeningDates<CutoffOpenDate),
> > --(RangeOfClosingDates>CutoffOpenDate))
> >
> > Now, just insert the correct ranges and cell references for those values.
> > With Sumproduct, you can't refer to whole columns like you can with COUNTIF
> > unless you're using Excel 2007
> > --
> > "Actually, I *am* a rocket scientist." -- JB
> >
> > Your feedback is appreciated, click YES if this post helped you.
> >
> >
> > "Nadine" wrote:
> >
> > > I am attempting to write a COUNTIF statement with greater than and less than
> > > functionality. I have 2 worksheets.
> > >
> > > Sheet 1 contains the extracted data from a database – this cannot be
> > > changed.
> > > Row A is a sequential number of the items.
> > > Row B contains the status (New, In Progress, Closed).
> > > Row C contains the day and time the item was created.
> > > Row D contains the day and time the item was closed.
> > > Cell F2 contains: =MONTH(C2)&"-"&YEAR(C2)
> > > Cell G2 contains: =IF(B2="Closed", MONTH(C2)&"-"&YEAR(C2),"")
> > >
> > > Sheet 2 has the 6 month reporting period as shown below:
> > > Cell B1 is for the user to enter the MM-YYYY of the first reporting month
> > > Cell B2 contains the following formula: =DATE(YEAR(B1),MONTH(B1)+C2,DAY(B1))
> > > to return the MM-YYYY 1 greater than the month in cell B1. This continues
> > > through B6 so I have 6 months in a row.
> > > Cell A16 says: =B1 to return the MM-YYYY of the first month for reporting.
> > > Cell B16 says: =B2 and so on through cell F16
> > > Row 17 is where I want the results of the COUNTIF formula to begin.
> > > Cell A17 says: =COUNTIF(Sheet1!$F:$F,Sheet2!A16) to tell me how many were
> > > opened in the month displayed in cell A16.
> > > Cell A18 says: =COUNTIF(Sheet1!$G:$G,Sheet2!A16) to tell me how many were
> > > closed during the month displayed in cell A16.
> > > Now I have the number of items opened and the number of items closed in each
> > > month.
> > >
> > > What I need to know is how many OPEN items I had at the beginning of the
> > > current month. Remember that the months in row 16 will changed based on what
> > > the user enters in cell B2. (This workbook is to be used as a template so it
> > > will constantly be changing.)
> > >
> > > I've tried using the formula for COUNTIF in the help section:
> > > =COUNTIF(Sheet1!$F:$F,"<Sheet2!A16")-COUNTIF(Sheet1!$G:$G,">Sheet2!B16") to
> > > tell me how many were open at the beginning (12:01am) of the second month.
> > > The result was 11 which is incorrect as only 1 item was created in the first
> > > month and it is still open. So the result should have been 1.
> > >
> > > Thanks for any help.
> > >

  #5  
Old May 8th 09, 01:05 AM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 216
Default COUNTIF greater than one column and less than another

Sorry. I missed that in your original response. Unfortunately I need the
whole column as it they data in the column will continually grow. With
limiting the end it will, at some point, no longer see all the data. I think
I've found a way around my problem with making a few changes in other cells
and formulas.

"JBeaucaire" wrote:

> You need to read my original suggestion again. I mentioned that sumproduct
> can't refer to an entire column. Simply use an actual range, sufficiently
> large to cover your needs. Sumproduct is the function for this.
>
> --
> "Actually, I *am* a rocket scientist." -- JB
>
> Your feedback is appreciated, click YES if this post helped you.
>
>
> "Nadine" wrote:
>
> > I entered the formula
> > =SUMPRODUCT((Sheet1!F:F<Sheet2!C16)*(Sheet1!G:G>Sh eet2!C16)) and received
> > #NUM! as the result. Thanks for the link, though. I will keep it and
> > continue reading up on SUMPRODUCT to see if there's another way to write it
> > that will give me the result I need.
> > Nadine
> >
> > "JBeaucaire" wrote:
> >
> > > I use SUMPRODUCT() to accomplish counting like you're describing. This
> > > function is WAY more flexible than people realize at first. This link has a
> > > wonderful tutorial and the really good stuff is down the sheet a ways past
> > > the "Advantages of Sumproduct"
> > >
> > > http://www.xldynamic.com/source/xld....T.html#classic
> > >
> > > Read up on that. Then I imagine a formula for count of open items spanning
> > > many months to be something like this:
> > >
> > > =SUMPRODUCT(--(RangeOfOpeningDates>=OpeningDate),
> > > --(RangeOfOpeningDates<CutoffOpenDate),
> > > --(RangeOfClosingDates>CutoffOpenDate))
> > >
> > > Now, just insert the correct ranges and cell references for those values.
> > > With Sumproduct, you can't refer to whole columns like you can with COUNTIF
> > > unless you're using Excel 2007
> > > --
> > > "Actually, I *am* a rocket scientist." -- JB
> > >
> > > Your feedback is appreciated, click YES if this post helped you.
> > >
> > >
> > > "Nadine" wrote:
> > >
> > > > I am attempting to write a COUNTIF statement with greater than and less than
> > > > functionality. I have 2 worksheets.
> > > >
> > > > Sheet 1 contains the extracted data from a database – this cannot be
> > > > changed.
> > > > Row A is a sequential number of the items.
> > > > Row B contains the status (New, In Progress, Closed).
> > > > Row C contains the day and time the item was created.
> > > > Row D contains the day and time the item was closed.
> > > > Cell F2 contains: =MONTH(C2)&"-"&YEAR(C2)
> > > > Cell G2 contains: =IF(B2="Closed", MONTH(C2)&"-"&YEAR(C2),"")
> > > >
> > > > Sheet 2 has the 6 month reporting period as shown below:
> > > > Cell B1 is for the user to enter the MM-YYYY of the first reporting month
> > > > Cell B2 contains the following formula: =DATE(YEAR(B1),MONTH(B1)+C2,DAY(B1))
> > > > to return the MM-YYYY 1 greater than the month in cell B1. This continues
> > > > through B6 so I have 6 months in a row.
> > > > Cell A16 says: =B1 to return the MM-YYYY of the first month for reporting.
> > > > Cell B16 says: =B2 and so on through cell F16
> > > > Row 17 is where I want the results of the COUNTIF formula to begin.
> > > > Cell A17 says: =COUNTIF(Sheet1!$F:$F,Sheet2!A16) to tell me how many were
> > > > opened in the month displayed in cell A16.
> > > > Cell A18 says: =COUNTIF(Sheet1!$G:$G,Sheet2!A16) to tell me how many were
> > > > closed during the month displayed in cell A16.
> > > > Now I have the number of items opened and the number of items closed in each
> > > > month.
> > > >
> > > > What I need to know is how many OPEN items I had at the beginning of the
> > > > current month. Remember that the months in row 16 will changed based on what
> > > > the user enters in cell B2. (This workbook is to be used as a template so it
> > > > will constantly be changing.)
> > > >
> > > > I've tried using the formula for COUNTIF in the help section:
> > > > =COUNTIF(Sheet1!$F:$F,"<Sheet2!A16")-COUNTIF(Sheet1!$G:$G,">Sheet2!B16") to
> > > > tell me how many were open at the beginning (12:01am) of the second month.
> > > > The result was 11 which is incorrect as only 1 item was created in the first
> > > > month and it is still open. So the result should have been 1.
> > > >
> > > > Thanks for any help.
> > > >

  #6  
Old May 8th 09, 07:10 PM posted to microsoft.public.excel.worksheet.functions
Justin Case
external usenet poster
 
Posts: 11
Default COUNTIF greater than one column and less than another

On May 7, 8:05*pm, Nadine > wrote:
> Sorry. *I missed that in your original response. *Unfortunately I need the
> whole column as it they data in the column will continually grow. *With
> limiting the end it will, at some point, no longer see all the data. *I think
> I've found a way around my problem with making a few changes in other cells
> and formulas.
>
>
>
> "JBeaucaire" wrote:
> > You need to read my original suggestion again. I mentioned that sumproduct
> > can't refer to an entire column. Simply use an actual range, sufficiently
> > large to cover your needs. *Sumproduct is the function for this.

>
> > --
> > "Actually, I *am* a rocket scientist." -- JB

>
> > Your feedback is appreciated, click YES if this post helped you.

>
> > "Nadine" wrote:

>
> > > I entered the formula
> > > =SUMPRODUCT((Sheet1!F:F<Sheet2!C16)*(Sheet1!G:G>Sh eet2!C16)) and received
> > > #NUM! as the result. *Thanks for the link, though. *I will keep it and
> > > continue reading up on SUMPRODUCT to see if there's another way to write it
> > > that will give me the result I need.
> > > Nadine

>
> > > "JBeaucaire" wrote:

>
> > > > I use SUMPRODUCT() to accomplish counting like you're describing. This
> > > > function is WAY more flexible than people realize at first. This link has a
> > > > wonderful tutorial and the really good stuff is down the sheet a ways past
> > > > the "Advantages of Sumproduct"

>
> > > >http://www.xldynamic.com/source/xld....T.html#classic

>
> > > > Read up on that. *Then I imagine a formula for count of open items spanning
> > > > many months to be something like this:

>
> > > > =SUMPRODUCT(--(RangeOfOpeningDates>=OpeningDate),
> > > > --(RangeOfOpeningDates<CutoffOpenDate),
> > > > --(RangeOfClosingDates>CutoffOpenDate))

>
> > > > Now, just insert the correct ranges and cell references for those values.
> > > > With Sumproduct, you can't refer to whole columns like you can with COUNTIF
> > > > unless you're using Excel 2007
> > > > --
> > > > "Actually, I *am* a rocket scientist." -- JB

>
> > > > Your feedback is appreciated, click YES if this post helped you.

>
> > > > "Nadine" wrote:

>
> > > > > I am attempting to write a COUNTIF statement with greater than and less than
> > > > > functionality. *I have 2 worksheets. *

>
> > > > > Sheet 1 contains the extracted data from a database this cannot be
> > > > > changed. *
> > > > > Row A is a sequential number of the items.
> > > > > Row B contains the status (New, In Progress, Closed).
> > > > > Row C contains the day and time the item was created.
> > > > > Row D contains the day and time the item was closed.
> > > > > Cell F2 contains: =MONTH(C2)&"-"&YEAR(C2)
> > > > > Cell G2 contains: =IF(B2="Closed", MONTH(C2)&"-"&YEAR(C2),"")

>
> > > > > Sheet 2 has the 6 month reporting period as shown below:
> > > > > Cell B1 is for the user to enter the MM-YYYY of the first reporting month
> > > > > Cell B2 contains the following formula: =DATE(YEAR(B1),MONTH(B1)+C2,DAY(B1))
> > > > > to return the MM-YYYY 1 greater than the month in cell B1. *This continues
> > > > > through B6 so I have 6 months in a row.
> > > > > Cell A16 says: =B1 to return the MM-YYYY of the first month for reporting.
> > > > > Cell B16 says: =B2 and so on through cell F16
> > > > > Row 17 is where I want the results of the COUNTIF formula to begin.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel to COUNTIF if number greater than one but less than another Rooster8 Excel Worksheet Functions 2 October 24th 05 10:35 PM
countif greater/less than argument nme#1 Excel Worksheet Functions 3 October 18th 05 07:36 AM
Countif cell greater than average Mary Ann Excel Worksheet Functions 4 August 10th 05 09:49 AM
Using COUNTIF to find numbers within a range greater than the mean Lowkey Excel Worksheet Functions 2 May 17th 05 06:34 PM
countif a date is greater than today Keith Excel Worksheet Functions 3 January 12th 05 03:37 AM


All times are GMT +1. The time now is 11:19 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.