Table not filtering % results

[Blake0920]

Trying to filter my results in my table but for some reason when I filter by month all of my % columns give me the total not the individual month total.

This is the formula for the TOTAL column. =AVERAGEIF(J4:J15,"<>0")

When I filter by month I just want to have the total of the month not the entire year. Any suggestions?

 

[Fluff]

How about adding a new column to your table with this formula

=SUBTOTAL(3,A2)

changing the A2 to a column that will always have data. you can then use

=AVERAGEIFS(J4:J15,J4:J15,"<>0",M4:M15,1)

changing M4:M15 to the column with the subtotal formula

 

[Blake0920]

Hi again, having a hard time working the formal into the chart as it would not be ideal to have an extra column.

Is there another way where I can keep the existing formula but have it filter properly?

 

[Fluff]

You could use

=SUMPRODUCT((J4:J15<>0)*(SUBTOTAL(3,OFFSET(J4,ROW(J4:J15)-ROW(J4),0))),J4:J15)/SUMPRODUCT((J4:J15<>0)*(SUBTOTAL(3,OFFSET(J4,ROW(J4:J15)-ROW(J4),0))))

but it's volatile so will re-calculate far more often than is needed.

 

[Blake0920]

You are a wizard..thank you very much!

 

[Fluff]

You're welcome & thanks for the feedback.