Office 2003
Here is the problem that I am running into. I work in an IT department and I track alot of our ticket status on a spread sheet. Recently my boss has asked me to track the ticket tier (ie. 1,2,3). What is happening is that I have the following array formula:

=SUM((B6:B72="Open")*(C6:C72="1"))

What its saying, I need to total number of tickets that are open and tier 1. With this particular formula it does a count, instead of an add, if used as an array. The funny thing is that I can copy a cell with the value of "1" from a completely different spreadsheet, same value, format, everything is the same and paste it. Then the array recognizes it and updates the value. If I type 1 in manually or copy from a cell on the current spreadsheet it does not update. This is with all of my array formulas. I have reset the cell format several times. Even started a new spreadsheet and still having same issue. I am entering the array with ctrl-shift-enter. I checked tools, options, and calculations are set on automatic. Also tried F9. The formulas do not even update with a save. I have read all I can read on this particular issue and have had no luck. Hence why I an posting on here. If anyone know of a simpler formula or knows why this is acting in this particular fashion. Please please let me know. I have been beating my head on this for 2 days.