counting cells that meet certain criteria

Hi,


I have a table on which i need to work out how many times a certain type of deal from a certain buyer/seller has been profitable and create that into a table format...
type beingcolumn C, house D, £ E.

ie . TYPE HOUSE £
News DRES +£20
Eco ML +£30
Earnings J +£40

There are negative amounts which i do not want to be included in my chart below so i can figure the amounty of winning deals.

the chart looking as such (imagine the cells are there)

DRES ML J
News [P5] [Q5] [R5]
Eco
Earnings

the array/formula i assumed would do it if it was entered in the cell P5:


=SUM((C2:C300="News")*(D2:D300="DRES")*(E2:E300>1))

but no luck i get 0 in P5 when on the table there are 2 that deals that are above 1.. hope i have made sense..

Is the problem that there are only 30 entries and that i am searching columns down to number300

Any help please Thanks

Here are some idéas:

First, I presume you use Ctrl+Shift+Enter together with =SUM(..array..). Otherwise =SUMPRODUCT(..array..) will also work.

Second, I would try the usual Error seeking methods:
- reduce the size of the range - as a test - to see where the error occurs or when it starts working.
- and then apply F9 to the reduced formula range - to see the true values - if you still get 0 (http://techrepublic.com.com/5100-6270-1033815.html#)

Hope it can be to some help

Ola Sandström