Sumproduct Formula

[ryansm05]

Hi,

I'm not sure if a Sumproduct is the best approach, but I'm needing to achieve the below:

In cell N4 I have returned the value of £10,030.41 which is an average of all the basic pay (row 3) and actuals (row 1). If this was looking at the 'Forecast' cells the average would be a lot lower as there are zeros.
In cell O4 the same methodology as N4 has been applied but on bonus instead.

​

Actual (a) Actual (B) Actual (c) Actual (d) Actual (e) Actual (f) Actual (g) Actual (h) Forecast Forecast Forecast (K) Forecast (L) (M) Actual (N) Actual (O) P01 P01 P02 P02 P03 P03 P04 P04 P05 P05 P06 P06 Average Average Basic Pay Bonus Basic Pay Bonus Basic Pay Bonus Basic Pay Bonus Basic Pay Bonus Basic Pay Bonus Basic Pay Bonus 10,000.00 1,000.00 10,020.00 1,002.00 10,040.04 1,004.00 10,060.12 1,006.01 0.00 0.00 0.00 0.00 10030.04​ 1003.004​

If anyone can help I'd be very grateful!

Thanks
Ryan

 

[KOKOSEK]

try into N4:

=AVERAGE(IF(($A$3:$L$3=N3)*($A$4:$L$4>0),$A$4:$L$4))

this is array formula, so you have to accept it with Ctrl+Shift+Enter not just Enter (as usual). Curly brackets {} should appear around formula (check formula bar).
Then drag it right into O4.

 

[ryansm05]

try into N4:

=AVERAGE(IF(($A$3:$L$3=N3)*($A$4:$L$4>0),$A$4:$L$4))

this is array formula, so you have to accept it with Ctrl+Shift+Enter not just Enter (as usual). Curly brackets {} should appear around formula (check formula bar).
Then drag it right into O4.

Legend! This is absolutely perfect.

Thanks you very much.

Ryan

 

[KOKOSEK]

Happy to help