Bug in Excel 2003 or am I missing something here!?

[amycclark]

I have found something strange that is screwing up my spreadsheet and I just don't get it.... I am beginning to think it is a bug but perhpas not. Anyone know what it up here?!

I have copied this formula: =COUNTIF(IF(($A$2:$A$9>=$D$1)*($A$2:$A$9<=$D$2),$B$2:$B$9),"<=50") in to numerous cells (just to test it) and it only works in the cells that are in the same row as the data.

To simplify it, I have two columns of data with 8 data points (located in rows 2 through 9):

Date DaystoProcess
5/11/11 48
6/13/11 16
5/19/11 50
5/20/11 52
5/23/11 49
6/2/11 40
5/23/11 52
5/26/11 70


Then I have a start date and end date in two other cells (D1 and D2).
All the cells are referenced with dollar sign ("$") so there shouldn't be any issues with referencing the wrong cells.

If I copy this formula into any cell located in rows 2 through 9 it works. If it is referenced in any other cells then it results in a #VALUE!.


Very puzzling!!!
Or am I missing about something???
~Amy

[Fotis1991]

Hi Amy

Your formula is lock($), for rows 2:9 ($A$2:$A$9<=$D$2),$B$2:$B$9)

First try to put out the $.

And if you still have problem, upload a sample workbook.

[shg]

Unlike some other functions you would use that construct with, COUNTIF requires a range (not an array) as the first argument; if either of the conditions fails, the value is False, which is not a range.

Since you don't have COUNTIFS in Excel 2003, you need to use SUMPRODUCT.

=SUMPRODUCT(($A$2:$A$9>=$D$1) * ($A$2:$A$9<=$D$2) * ($B$2:$B$9 <= 50))

[amycclark]

Thanks to both of you.

Fotis1991, taking out the $ didn't do anything differnt but I hard a hard time concentrating after seeing your Summer in Greece photo. ; )

shg - I switched over to the sumproduct and that works in all cells so big thanks!!!

Not necessary to discuss, but I am not sure why COUNTIF would work as written in some cells but not others. Strange to me still but I am on board with using sumproduct!

THANKS!
~Amy