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?
--
PaulineC

[[email protected]]

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 D4:D3710. 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))

[Dave R.]

change that /(countif part to:

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


"paulinec" <[email protected]> wrote in message
news:[email protected]...
> 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]

Hi Pauline

I'm not sure I completely understand your question, but to return an average
of cells D4:D3710 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(D4:D3710<>"",D4:D3710))}

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

[email protected]

"paulinec" <[email protected]> wrote in message
news:[email protected]...
| 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

[email protected]

"William" <[email protected]> wrote in message
news:[email protected]...
| Hi Pauline
|
| I'm not sure I completely understand your question, but to return an
average
| of cells D4:D3710 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(D4:D3710<>"",D4:D3710))}
|
| 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
|
| [email protected]
|
| "paulinec" <[email protected]> wrote in message
| news:[email protected]...
| | 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
|
|
|