Combining Average if and sumproduct formulas

[Kmitchell]

I have two formulas that work independently but I need to combine and having some difficulty.

Column E sits account names, if it says "FTEs or Headcount" I want it to do an average if formula, otherwise I want it to do a sumproduct formula.

Below is the average if array which works on its own.
AVERAGE(IF($F$4:$Q$4>=$S$2,IF(($F$4:$Q$4<=$S$3),$F566:$Q566))))

And below is the sumproduct which also works on it's own
SUMPRODUCT(--($F$4:$Q$4>=$S$2),--($F$4:$Q$4<=$S$3),$F2416:$Q2416)

I need to be able to combine the two to say if A2 = $E$1 (headcount), or A2 = $E$2 (FTEs) in the cell than do the Average(if formula, if not then do the sumproduct formula.

Any suggestions?

 

[Kmitchell]

In your case averageifs and sumifs would be the better option if you are calculating 20000 rows. They are fairly easy to understand formulae presuming you are on 2007 or later.

I do use sumifs and averageifs but couldn't figure out how to get them to work if the data sat in several columns such as this last example. Yes on 2010.