Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to paste COUNTIF function from cell to cells ?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to paste COUNTIF function from cell to cells ?
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 wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transfer Cell Formatting for linked cells | Excel Discussion (Misc queries) | |||
can you use countif function for noncontiguous cells | Excel Worksheet Functions | |||
How to insert a value in one cell into many cells | Excel Worksheet Functions | |||
Could the "Criteria" in COUNTIF function be a cell reference? | Excel Worksheet Functions | |||
excel - numbers as text | New Users to Excel |