average based on criteria

[arnab0711]

Hi,
I have this table from c7:h34,the general formula of the table are given in e3,f3,g3,h3(in red),but I want for example output for e7 cell as average of subtractions of range p4:p10 and q4:q10 where month = Jan and Broad category
= "PDH Antenna"

[Bob Phillips]

Try this array formula

=AVERAGE(IF((MONTH($P$4:$P$10)=MONTH($C7))*(YEAR($P$4:$P$10)=YEAR($C7))*($M $4:$M$10=$D7),$Q$4:$Q$10-$P$4:$P$10))

[arnab0711]

Hi Xld,
Thanks,the formula is working fine,only one query can we have a moving avg of this.

[raji2678]

Try using pivots and macros

[arnab0711]

Hi Xld,
When I repeat the same material in AB9 or AB11 its giving error message in E12 or E13

[Bob Phillips]

Try this adjustment

=IFERROR(AVERAGE(IF((MONTH($AE$4:$AE$10)=MONTH($C7))*(YEAR($AE$4:$AE$10)=YE AR($C7))*($AB$4:$AB$10=$D7),$AF$4:$AF$10-$AE$4:$AE$10)),NA())

[arnab0711]

No its throwing an error as #NA

[Bob Phillips]

I know, the formula throws out an #N/A for no data so that it doesn't chart.