Average based on multiple criteria

[slamanager]

Can some one please show me a formula how i can get an Average based on a date range, sales person, sales type please.

Col A
dates dd/mm/yyyy
Col B
Sales person names
Col C
types "New" "Closed" "Open"

I know by using a sumproduct per month how many itmes there are but unable to compute an average.

Please some one help me.

 

[Aladin Akyurek]

Can some one please show me a formula how i can get an Average based on a date range, sales person, sales type please.

Col A
dates dd/mm/yyyy
Col B
Sales person names
Col C
types "New" "Closed" "Open"

I know by using a sumproduct per month how many itmes there are but unable to compute an average.

Please some one help me.

On all versions...

Control+shift+enter, not just enter:

=AVERAGE(
    IF($A$2:$A$400>=F2,
    IF($A$2:$A$400<=G2,
    IF($B$2:$B$400=H2,
    IF($C$2:$C$400=I2,
      $D$2:$D$400)))))

where F2:G2 houses dates specifying a range with F2 <= G2, H2 a sales person of interest, and I2 a sales type of interest. D2:D400 houses the values to average.

If you are on Excel 2007 or later...

Just enter:

=AVERAGEIFS(
    $D$2:$D$400,
    $A$2:$A$400,">="&F2,
    $A$2:$A$400,"<="&G2,
    $B$2:$B$400,H2,
    $C$2:$C$400,I2)

 

[slamanager]

will test and come back to you on this. many thnaks