Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting non-blank cells
I have a column with cells that contain the results of a formula:
=IF(B8=B9,"",B9) In some cases the value is blank (""), in others the value is filled. When I try to count the values using COUNTA, the resulting count includes the cells where the formula result is "". How do I count only the cells that have a value other than ""? Thanks, Joe M. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting non-blank cells
One way
=SUMPRODUCT(--(LEN(A1:A10)0)) -- Regards, Peo Sjoblom "Joe M." wrote in message ... I have a column with cells that contain the results of a formula: =IF(B8=B9,"",B9) In some cases the value is blank (""), in others the value is filled. When I try to count the values using COUNTA, the resulting count includes the cells where the formula result is "". How do I count only the cells that have a value other than ""? Thanks, Joe M. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting non-blank cells
Peo Sjoblom" wrote in message
... One way =SUMPRODUCT(--(LEN(A1:A10)0)) Doesn't that return 0 for cells with "" in them? I was going to post: =COUNTBLANK(A1:A10)-COUNTIF(A1:A10,"*") or =COUNTIF(A1:A10,"=") or the array entered: =SUM(--ISBLANK(A1:A10)) But I was trying to work out if Harlan would come along and point out where they would fail as well. -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting non-blank cells
I have a column with cells that contain the results of a formula:
=IF(B8=B9,"",B9) In some cases the value is blank (""), in others the value is filled. When I try to count the values using COUNTA, the resulting count includes the cells where the formula result is "". How do I count only the cells that have a value other than ""? Maybe COUNTBLANK() will help. It counts empty cells, including those with a formula that returns empty text. Subtracting COUNTBLANK() from the total number of cells would count the cells that have a value other than "". |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting non-blank cells
I assumed that's what the OP wanted. I read his question as to count cells
that are not blank, whether it is from the cell being empty or from it having a formula returning a blank Peo "Sandy Mann" wrote in message ... Peo Sjoblom" wrote in message ... One way =SUMPRODUCT(--(LEN(A1:A10)0)) Doesn't that return 0 for cells with "" in them? I was going to post: =COUNTBLANK(A1:A10)-COUNTIF(A1:A10,"*") or =COUNTIF(A1:A10,"=") or the array entered: =SUM(--ISBLANK(A1:A10)) But I was trying to work out if Harlan would come along and point out where they would fail as well. -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting non-blank cells
"Sandy Mann" wrote...
Peo Sjoblom" wrote in message .... =SUMPRODUCT(--(LEN(A1:A10)0)) Why bother using LEN? =SUMPRODUCT(--(A1:A10<"")) Doesn't that return 0 for cells with "" in them? Yup. All about specifyine what's intended by 'non-blank', which could be as above, or =SUMPRODUCT(--(TRIM(A1:A10)<"")) or =SUMPRODUCT(--(TRIM(SUBSTITUTE(A1:A10,CHAR(160),""))<"")) I was going to post: =COUNTBLANK(A1:A10)-COUNTIF(A1:A10,"*") This can produce negative numbers. or =COUNTIF(A1:A10,"=") or the array entered: =SUM(--ISBLANK(A1:A10)) .... These count truly blank cells. OP wanted to count nonblank cells. OP should use Peo's formula, or one of my adaptations of it. There's even =COUNT(A1:A10)+COUNTIF(A1:A10,"?*") but this doesn't include error or boolean values. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting non-blank cells
Joe M.
your question is quite tricky... =IF(B8=B9,"",B9) if your data range along column B (eg) B8:B18 maybe u can modify the quick count somekinda this way... =SUMPRODUCT(--(LEN(B9:B18)0)) *exclude the first cell on the range which is B* in this example... <g. -- regards, driller ***** - dive with Jonathan Seagull "Joe M." wrote: I have a column with cells that contain the results of a formula: =IF(B8=B9,"",B9) In some cases the value is blank (""), in others the value is filled. When I try to count the values using COUNTA, the resulting count includes the cells where the formula result is "". How do I count only the cells that have a value other than ""? Thanks, Joe M. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting blank cells | Excel Discussion (Misc queries) | |||
counting blank cells | Excel Discussion (Misc queries) | |||
Non-Blank Cells: Conditional Counting | Excel Worksheet Functions | |||
sumproduct--counting--zero--blank cells | Excel Discussion (Misc queries) | |||
histograms not counting blank cells | Excel Discussion (Misc queries) |