AVERAGEIFS with SUM as AVERAGE_RANGE

[MrPez]

I'm trying to get a formula working that averages a bunch of numbers based on two conditions. The problem I'm having is that my Average_rng needs to be the sum of two other cells and it doesn't seem to work.

Can anyone help?

My formula is:

=AVERAGEIFS(SUM('Input Data'!K:L),'Input Data'!H:H,A4,'Input Data'!J:J,">0")

I'm trying to set this as an array formula but it won't accept.

If I have an other column that sums columns K and L it works but this isn't really feasable unfortunately.

 

[steve the fish]

See if these work:

=AVERAGE(IF('Input Data'!H1:H100=A4,IF('Input Data'!J1:J100>0,'Input Data'!K1:K100+'Input Data'!L1:L100)))

entered CTRL-SHIFT-ENTER (note the restricted ranges so change to suit) or:

=AVERAGEIFS('Input Data'!K:K,'Input Data'!H:H,A4,'Input Data'!J:J,">0")+AVERAGEIFS('Input Data'!L:L,'Input Data'!H:H,A4,'Input Data'!J:J,">0")

 

[MrPez]

Thanks for trying but sadly they don't work. First gives an error and second doesn't actually give the right answer.
Not to worry, I'll have to figure out how to have another column to do the calculations.

 

[steve the fish]

Give some sample data with expected result and ill take a look.

 

[vmjan02]

a suggestion in column M add or do the sum of column K and L and then apply =averageifs.

eg:- K L M
1 1 =sum(k2+l2)

 

[steve the fish]

a suggestion in column M add or do the sum of column K and L and then apply =averageifs.

eg:- K L M
1 1 =sum(k2+l2)

That wouldnt address the issue that the OP didnt want to use a helper column. The two formulas i provided would actually produce the same answer as a helper column produced in this way hence why i asked for sample data and expected results.

 

[MrPez]

Ok, so with the data below I'd like to know the average of the sum of columns B and C where column A is ACB. The result I'd expect is 334.6667.

[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64"]ACA[/TD]
[TD="width: 64, align: right"]512[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"][/TD]
[/TR]
[TR]
[TD]ACB[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]ACB[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]ACB[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]995[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]ACB[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]ACB[/TD]
[TD="align: right"]545[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]ACB[/TD]
[TD]NULL[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]ACC[/TD]
[TD="align: right"]566[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Thanks for your help!

 

[steve the fish]

What is the sum of NULL and 5?

 

[MrPez]

5. I need to be able to ignore zeros and Nulls.

 

[steve the fish]

Your original formula used 4 columns. H,J,K,L. The data you have given is 3?