Excel formula problems

[VAPOKERPRO]

Hello All,

I am new to Mac and am having problems with a formula in Excel 2008 for Mac. I originally created the formula in Windows and it worked fine but now when I am editing the document with my Mac the formula causes an error.

Purpose of Formula: I am using the COUNTIFS formula to compare and tally date ranges for when certain paperwork was received as opposed to when it should have been received and the totals. I have one column with the "Appointment" which is when we SHOULD have received the ppwk and a column with "Rec'd" which is when we acutally got it. Both of these columns have dates, i.e 1/29/09 and formatted as such. Then I have two seperate columns that I use the COUNTIFS formula in to tally the Appointments and the received.

The problem as I see it is that Excel is not letting me dictate the range beyond a certain point. Here is the Appointments formula that I am using...
=COUNTIFS($C$2:$C$96,">=1/1/2009",$C$2:$C$96,"<2/1/2009"). If I try to make the range larger so that I don't have to change the formula each time I add more data like $C$2:$C$1000, it returns a #VALUE error and I can't figure out how to fix it.

Does anyone have any ideas why it is not letting me use a larger range? As I said before, the formula worked fine on a Windows machine with the larger range.

Thanks.

 

[reclusivemonkey]

Try

$C:$C

if you want the whole column.

 

[MooneyFlyer]

Try

$C:$C

if you want the whole column.

Actually, I would name the column to make it easier.

I was able to get both:
=COUNTIFS(H100:H200,">=1/1/08",H100:H200,"<3/1/09")
and
=COUNTIFS(H:H,">=1/1/08",H:H,"<3/1/09")

I don't think that the VALUE error you are getting is Mac specific. I'd be happy to take a look at it though if you post a sample or email one.

to work.

 

[VAPOKERPRO]

I have attached and example of what I am working on. As a point of reference , just in case, I also attached a print screen of what I see, in case you open it and there are no problems with it.

Thanks all for the help.

 

[MooneyFlyer]

Ok - got the file. Can you tell me the exact formula you want in K1-13 and L1-13? I didn't fully understand your description above of what you are trying to count. We should be able to get this to work today.

If I just create the formula you've specified above:

=COUNTIFS(C:C,$K$1,C:C,$L$1)
(where K1 is ">=1/1/09" and L1 is "<2/1/09")
I get "12"...

 

[VAPOKERPRO]

Ok. Here is what I am trying to count.

I have paperwork that gets sent out with specific dates of return, the Appointments column. Column K counts the total amount of paperwork to be returned in the given month, January, February, etc... by checking the date range.{=countifs($c:$c,">=1/1/2009",$c:$c,"<2/1/2009") formula for January}
I have another column that tracks when I actually receive the paperwork back, the Rec'd column. Column L counts these by first checking to see if the date in Appointments is within the specified month, then it looks at the Rec'd date and confirms that it is no more than 30 days late and if TRUE should count it as a part of the given Month.{=countifs($c:$c,">=1/1/2009",$c:$c,"<2/1/2009",$h:$h,">=1/1/2009",$h:$h,"<3/1/2009") formula for January}

I created these formulas so that I could keep all of the data on a single sheet. I hope my description makes sense.

 

[MooneyFlyer]

Ok - I see what you are after now. I was confused by the location of JAN-DEC and thought it was part of the rows to the right. Back in a bit...

 

[MooneyFlyer]

Ok, see if this does what you want.. I added some more test data to make sure. The problem with this arrangement is that it doesn't test for "greater than 30 days late" -- it tests to see if the received is more than a month after. That means that if the last day that something should show up is 3/1 it isn't late until 4/1 hits...

Maybe I missed the mark -- but I've got some time to fool with this during the week.