Excel: Combining Average if and sumproduct formulas

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?

This question generated 11 answers. To proceed to the answers, click here.

This thread is current as of October 27, 2014.

For more resources for Microsoft Excel