Excel formulas

OK

I have 2 colums (in A and B) such as

live 20
dead 20
live 30
dead 50
live 40

I can calculate how many are alive by the formula ={SUM(IF(A1:A6="live",B1:B6,0))} - this is not the problem.

My problem is that I have anther set of data which I want to count. My data (for example is as follows (In column A and B).

1 2
2 3
3 4
4 5
5 6

I want to use an aggregated formula to count how many records are above or equal to 2 in column A and less than or equal to 5 in column B - the answer is 3. However, my formala doesn't work. I've tried ={COUNT(IF(AND(A1:A6>=2,B1:B6<=5),B1:B6,0))} but to no success.

Can anyone help?

Thanks

Bob.

Just for the record... you are not writting the brackets... right...? You are entering that formula using Ctrl + Shift + Enter :rolleyes:?

Yes I am - I clues where I'm going wrong?

Sorry, I mean't to put 'any' clues where I am going wrong?

Bob,

I believe You should use a different approach since the AND-function does not work in array-formulas.


=SUMPRODUCT((A1:A6>=2)*(B1:B6<=5))