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.

 

[MrPez]

Sorry, the original included a sum column that I had put in while trying to figure it out. That isn't going to be an option in the long term.

 

[steve the fish]

Ok so what needs to be greater than 0?

 

[MrPez]

That was me just trying to get it to ignore zeros!

 

[MrPez]

IEssentially I need the average of the sum of columns b and c where the condition in A is met and ignore NULL and 0

 

[steve the fish]

What i gave you will work if you change null to 0. That will be easier than trying to convert null to 0 in a formula.

 

[MrPez]

Thanks Steve, I appreciate your help, although I didn't manage to get it to work!
I did, however, come up with a very messy workaround in case anyone is in a similar situation:
=AVERAGE(IF(A1:A8=F2,IF(NOT(ISNUMBER(1/B1:B8)),IF(NOT(ISNUMBER(1/C1:C8)),"",C1:C8),IF(NOT(ISNUMBER(1/C1:C8)),B1:B8,B1:B8+C1:C8)),""))