How to paste COUNTIF function from cell to cells ?

[Arnaud Penverne (france)]

Lets try to be clear...

Data is table 1:
A B
1 Bob 1900
2 Rod 3200
3 John 4000
4 Joe 1200

etc...

Now I want a distribution graph for values :

From 0 to 500 = 0%
From 0 to 1000 = 0%
from 0 to 1500 =25% (1 out of 4)
from 0 to 2000 = 50%
etc...

I created a table 2
A B
1 0
2 500 =countif(table1B1:B4;and(">$A$1";"<=A2"))
3 1000
4 1500
5 2000
If I copy paste the actual formula to B3 cell, cell ref A2 doesn't change
How can I paste that formula in B3 in order to get the right result ?

Any help is appreciated
Arnaud

 

[Peo Sjoblom]

Try

=SUMPRODUCT(--(Table1!$B$1:$B$4>$A$1),--(Table1!$B$1:$B$4<=A2))

then copy down will give you

0
0
1
2
etc

you can't use COUNTIF with AND, you can use this instead

=COUNTIF(Table1!$B$1:$B$4,">"&$A$1)-COUNTIF(Table1!$B$1:$B$4,">"&A2)

will yield the same result as the SUMPRODUCT formula



--
Regards,

Peo Sjoblom

(No private emails please)


"Arnaud Penverne (france)" <Arnaud Penverne
(france)@discussions.microsoft.com> wrote in message
news:[email protected]...