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?
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?