View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Justin Case Justin Case is offline
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:GSh 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),
--(RangeOfClosingDatesCutoffOpenDate))


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.