Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting data in columns with linked cells
I have a spreadsheet with numerous columns of data containing dates linked
from other spreadsheets. I'm using the "counta" to count the rows containing data in each column, but my problem is every row is being counted whether it contains data or is blank. Appreciate anyone's assistance in solving this. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting data in columns with linked cells
Hi Bernie
Because you have a formula in the cell which pulls data from another sheet, Counta is counting this. As the results of the formula, when true, will be dates which will be serial numbers, try =COUNTIF(A:A,"0") -- Regards Roger Govier "Bernie R." <Bernie wrote in message ... I have a spreadsheet with numerous columns of data containing dates linked from other spreadsheets. I'm using the "counta" to count the rows containing data in each column, but my problem is every row is being counted whether it contains data or is blank. Appreciate anyone's assistance in solving this. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting data in columns with linked cells
Are the dates numbers so that when linked to an empty cell it will return a
zero if so you can use =COUNTIF(A1:A20,"0") if text change the link formula to (adapt to fit you own references) =IF(A2="","",A2) as an example, if A2 is blank it will return a blank and not zero =SUMPRODUCT(--(A1:A20<"")) -- Regards, Peo Sjoblom "Bernie R." <Bernie wrote in message ... I have a spreadsheet with numerous columns of data containing dates linked from other spreadsheets. I'm using the "counta" to count the rows containing data in each column, but my problem is every row is being counted whether it contains data or is blank. Appreciate anyone's assistance in solving this. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting data in columns with linked cells
That worked; however, I got a curve ball thrown at me. If there are "NA" in
the cell(s), I don't want to count them or the blanks; and then take the "NA" out of the demoninator when caluculating percentages for that column. "Roger Govier" wrote: Hi Bernie Because you have a formula in the cell which pulls data from another sheet, Counta is counting this. As the results of the formula, when true, will be dates which will be serial numbers, try =COUNTIF(A:A,"0") -- Regards Roger Govier "Bernie R." <Bernie wrote in message ... I have a spreadsheet with numerous columns of data containing dates linked from other spreadsheets. I'm using the "counta" to count the rows containing data in each column, but my problem is every row is being counted whether it contains data or is blank. Appreciate anyone's assistance in solving this. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting data in columns with linked cells
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting dates in columns in a linked spreadsheet | Excel Worksheet Functions | |||
Counting # of cells with that meet criteria in two columns | Excel Worksheet Functions | |||
Counting Data in Columns | Excel Discussion (Misc queries) | |||
counting cells in two columns that have the same results | Excel Worksheet Functions | |||
Counting data in columns | Excel Discussion (Misc queries) |