COUNT ONLY CELLS THAT AREN'T BLANK

[paulinec]

This is the present function I have to get an average of $D$4:$D$3710:
=(SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710))/(COUNTIF($A$4:$A$3710,A3742)
The problem with this is that it is counting all cells in $A$4:$A$3710 that
equal A3742.
To get a correct average I need to only count cells that are not blank in
$D$4:$D$3710 based on $A$4:$A$3710 equalling A3742.
Can anyone help me with this?

 

[Dave R.]

change that /(countif part to:

SUMPRODUCT(($A$4:$A$3710=A3742)*($D$4:$D$3710<>""))

 

[hrlngrv]

paulinec wrote...

This is the present function I have to get an average of $D$4:$D$3710:
=(SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710))/(COUNTIF($A$4:$A$3710,A3742)
The problem with this is that it is counting all cells in $A$4:$A$3710 that
equal A3742.
To get a correct average I need to only count cells that are not blank in
$D$4:$D$3710 based on $A$4:$A$3710 equalling A3742.

....

You may be better off using the array formula

=AVERAGE(IF($A$4:$A$3710=A3742,$D$4:$D$3710))

which will skip nonnumeric cells in D43710. If you can't use array
formulas, then you need to use a 2-criteria denominator.

=SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710)
/SUMPRODUCT(--($A$4:$A$3710=A3742),--ISNUMBER($D$4:$D$3710))

 

[William]

Hi Pauline

I'm not sure I completely understand your question, but to return an average
of cells D43710 that are not blank use this array formula which you should
enter by pressing Ctrl|Shift|Enter at the same time rather than just "Enter"

{=AVERAGE(IF(D43710<>"",D43710))}

I dont see the relevance of cell A3742 - if you want an average of those
cells that equal cell A3742, then cell A3742 would be the average - what am
I missing?

--
XL2002
Regards

William

(e-mail address removed)

| This is the present function I have to get an average of $D$4:$D$3710:
| =(SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710))/(COUNTIF($A$4:$A$3710,A3742)
| The problem with this is that it is counting all cells in $A$4:$A$3710
that
| equal A3742.
| To get a correct average I need to only count cells that are not blank in
| $D$4:$D$3710 based on $A$4:$A$3710 equalling A3742.
| Can anyone help me with this?
| --
| PaulineC

 

[William]

Bad day - ignore the last part of my post :-(

--
XL2002
Regards

William

(e-mail address removed)

| Hi Pauline
|
| I'm not sure I completely understand your question, but to return an
average
| of cells D43710 that are not blank use this array formula which you
should
| enter by pressing Ctrl|Shift|Enter at the same time rather than just
"Enter"
|
| {=AVERAGE(IF(D43710<>"",D43710))}
|
| I dont see the relevance of cell A3742 - if you want an average of those
| cells that equal cell A3742, then cell A3742 would be the average - what
am
| I missing?
|
| --
| XL2002
| Regards
|
| William
|
| (e-mail address removed)
|
| | | This is the present function I have to get an average of $D$4:$D$3710:
| | =(SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710))/(COUNTIF($A$4:$A$3710,A3742)
| | The problem with this is that it is counting all cells in $A$4:$A$3710
| that
| | equal A3742.
| | To get a correct average I need to only count cells that are not blank
in
| | $D$4:$D$3710 based on $A$4:$A$3710 equalling A3742.
| | Can anyone help me with this?
| | --
| | PaulineC
|
|
|

 

[paulinec]

To explain a bit further Row A1:3710 contains a farm number, Row B1:B3710
contains paddock nos, Row D13710 contains a row length for each paddock. As
some paddocks are not planted there are some blank cells in the row length
column.

In row 4742 I am trying to get an average row lengh for each farm. To be
accurate it has to be an average of the cells that contain row lengths.

At present it is averaging on the total number of paddocks in each farm.

 

[William]

Hi Pauline
Try...
=AVERAGE(IF(D43710<>"",IF(A4:A3710=F4742,D43710)))
where the farm number you are trying to average is in cell F4742

--
XL2002
Regards

William

(e-mail address removed)

| To explain a bit further Row A1:3710 contains a farm number, Row B1:B3710
| contains paddock nos, Row D13710 contains a row length for each paddock.
As
| some paddocks are not planted there are some blank cells in the row length
| column.
|
| In row 4742 I am trying to get an average row lengh for each farm. To be
| accurate it has to be an average of the cells that contain row lengths.
|
| At present it is averaging on the total number of paddocks in each farm.
|
| "William" wrote:
|
| > Bad day - ignore the last part of my post :-(
| >
| > --
| > XL2002
| > Regards
| >
| > William
| >
| > (e-mail address removed)
| >
| > | > | Hi Pauline
| > |
| > | I'm not sure I completely understand your question, but to return an
| > average
| > | of cells D43710 that are not blank use this array formula which you
| > should
| > | enter by pressing Ctrl|Shift|Enter at the same time rather than just
| > "Enter"
| > |
| > | {=AVERAGE(IF(D43710<>"",D43710))}
| > |
| > | I dont see the relevance of cell A3742 - if you want an average of
those
| > | cells that equal cell A3742, then cell A3742 would be the average -
what
| > am
| > | I missing?
| > |
| > | --
| > | XL2002
| > | Regards
| > |
| > | William
| > |
| > | (e-mail address removed)
| > |
| > | | > | | This is the present function I have to get an average of
$D$4:$D$3710:
| > | |
=(SUMIF($A$4:$A$3710,A3742,$D$4:$D$3710))/(COUNTIF($A$4:$A$3710,A3742)
| > | | The problem with this is that it is counting all cells in
$A$4:$A$3710
| > | that
| > | | equal A3742.
| > | | To get a correct average I need to only count cells that are not
blank
| > in
| > | | $D$4:$D$3710 based on $A$4:$A$3710 equalling A3742.
| > | | Can anyone help me with this?
| > | | --
| > | | PaulineC
| > |
| > |
| > |
| >
| >
| >

 

[paulinec]

Yes, that worked, thank you so much for that. Just one more question. What
is the reason for the two -- in the formula?

 

[tjtjjtjt]

It's used to convert the True and False to 1 and 0.
The first - converts them to -1 and 0.
The second - gets them to 1 and 0.
These are then used in the SUMPRODUCT Function.
For more on SUMPRODUCT in general:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

tj