Hi

I have four columns of data A,B,C and D. I would like to get the average
from the results in column D. But only when it matches certain criteria in
A,B and C.

I put
{=AVERAGE(IF(($A$1:$A$100="R1")*($B$1:$B$100="Mon" )*($C$1:$C$100=1),$D$1:$D$100))}

This works ok but sometimes when i have this data in there for info in other
columns such as E,F. The D will be blank but this will affect the average.
Can i add something to ignore the blanks cells.

Many thanks

Try:

=AVERAGE(IF(($A$1:$A$100="R1")*($B$1:$B$100="Mon") *($C$1:$C$100=1),ISNUMBER($D$1:$D$100)),$D$1:$D$10 0)

"Neil" wrote:

Hi

I have four columns of data A,B,C and D. I would like to get the average
from the results in column D. But only when it matches certain criteria in
A,B and C.

I put
{=AVERAGE(IF(($A$1:$A$100="R1")*($B$1:$B$100="Mon" )*($C$1:$C$100=1),$D$1:$D$100))}

This works ok but sometimes when i have this data in there for info in other
columns such as E,F. The D will be blank but this will affect the average.
Can i add something to ignore the blanks cells.

Many thanks

Maybe just adding more criteria would be ok:

=AVERAGE(IF(($A$1:$A$100="R1")
*($B$1:$B$100="Mon")
*($C$1:$C$100=1)
*ISNUMBER($D$1:$D$100), $D$1:$D$100))

Neil wrote:

Hi

I have four columns of data A,B,C and D. I would like to get the average
from the results in column D. But only when it matches certain criteria in
A,B and C.

I put
{=AVERAGE(IF(($A$1:$A$100="R1")*($B$1:$B$100="Mon" )*($C$1:$C$100=1),$D$1:$D$100))}

This works ok but sometimes when i have this data in there for info in other
columns such as E,F. The D will be blank but this will affect the average.
Can i add something to ignore the blanks cells.

Many thanks

--

Dave Peterson

You can use this formula:

=SUMPRODUCT(--($B$25:$B$28="r1"), --($C$25:$C$28="mon"),
--($D$25:$D$28=1)*($E$25:$E$28))/(COUNTIF($B$25:$E$28,($B$25:$B$28="r1")*($C$25:$C$ 28="Mon")*($D$25:$D$28=1))-IF(($B$25:$B$28="r1")*($C$25:$C$28="mon")*($D$25:$ D$28=1),COUNTBLANK($E$25:$E$28),0))

tell us if it works

"Dave Peterson" wrote:

Maybe just adding more criteria would be ok:

=AVERAGE(IF(($A$1:$A$100="R1")
*($B$1:$B$100="Mon")
*($C$1:$C$100=1)
*ISNUMBER($D$1:$D$100), $D$1:$D$100))

Neil wrote:

Hi

I have four columns of data A,B,C and D. I would like to get the average
from the results in column D. But only when it matches certain criteria in
A,B and C.

I put
{=AVERAGE(IF(($A$1:$A$100="R1")*($B$1:$B$100="Mon" )*($C$1:$C$100=1),$D$1:$D$100))}

This works ok but sometimes when i have this data in there for info in other
columns such as E,F. The D will be blank but this will affect the average.
Can i add something to ignore the blanks cells.

Many thanks

--

Dave Peterson

you can use this formula:

=SUMPRODUCT(--($B$25:$B$28="r1"), --($C$25:$C$28="mon"),
--($D$25:$D$28=1)*($E$25:$E$28))/(COUNTIF($B$25:$E$28,($B$25:$B$28="r1")*($C$25:$C$ 28="Mon")*($D$25:$D$28=1))-IF(($B$25:$B$28="r1")*($C$25:$C$28="mon")*($D$25:$ D$28=1),COUNTBLANK($E$25:$E$28),0))

tell us if it works.

"Neil" wrote:

Hi

I have four columns of data A,B,C and D. I would like to get the average
from the results in column D. But only when it matches certain criteria in
A,B and C.

I put
{=AVERAGE(IF(($A$1:$A$100="R1")*($B$1:$B$100="Mon" )*($C$1:$C$100=1),$D$1:$D$100))}

This works ok but sometimes when i have this data in there for info in other
columns such as E,F. The D will be blank but this will affect the average.
Can i add something to ignore the blanks cells.

Many thanks

Many thanks

They worked fine. It was a big help!

"Excel_Learner" wrote:

you can use this formula:

=SUMPRODUCT(--($B$25:$B$28="r1"), --($C$25:$C$28="mon"),
--($D$25:$D$28=1)*($E$25:$E$28))/(COUNTIF($B$25:$E$28,($B$25:$B$28="r1")*($C$25:$C$ 28="Mon")*($D$25:$D$28=1))-IF(($B$25:$B$28="r1")*($C$25:$C$28="mon")*($D$25:$ D$28=1),COUNTBLANK($E$25:$E$28),0))

tell us if it works.

"Neil" wrote:

Hi

I have four columns of data A,B,C and D. I would like to get the average
from the results in column D. But only when it matches certain criteria in
A,B and C.

I put
{=AVERAGE(IF(($A$1:$A$100="R1")*($B$1:$B$100="Mon" )*($C$1:$C$100=1),$D$1:$D$100))}

This works ok but sometimes when i have this data in there for info in other
columns such as E,F. The D will be blank but this will affect the average.
Can i add something to ignore the blanks cells.

Many thanks

You can use a small form of this formula also..
=SUMPRODUCT(--($B$25:$B$28="r1"), --($C$25:$C$28="mon"),
--($D$25:$D$28=1)*($E$25:$E$28))/SUMPRODUCT(--($B$25:$B$28="r1"),
--($C$25:$C$28="mon"), --($D$25:$D$28=1)*($E$25:$E$280))

"Neil" wrote:

Many thanks

They worked fine. It was a big help!

"Excel_Learner" wrote:

you can use this formula:

=SUMPRODUCT(--($B$25:$B$28="r1"), --($C$25:$C$28="mon"),
--($D$25:$D$28=1)*($E$25:$E$28))/(COUNTIF($B$25:$E$28,($B$25:$B$28="r1")*($C$25:$C$ 28="Mon")*($D$25:$D$28=1))-IF(($B$25:$B$28="r1")*($C$25:$C$28="mon")*($D$25:$ D$28=1),COUNTBLANK($E$25:$E$28),0))

tell us if it works.

"Neil" wrote:

Hi

I have four columns of data A,B,C and D. I would like to get the average
from the results in column D. But only when it matches certain criteria in
A,B and C.

I put
{=AVERAGE(IF(($A$1:$A$100="R1")*($B$1:$B$100="Mon" )*($C$1:$C$100=1),$D$1:$D$100))}

This works ok but sometimes when i have this data in there for info in other
columns such as E,F. The D will be blank but this will affect the average.
Can i add something to ignore the blanks cells.

Many thanks