Average within date range for a given sales person (Excel 2003)

[DPARDY]

Hello, I need to get the sum & average of a sales rep commission within a given date range.

A column dates, B column sales rep id, C column commission. I would enter the starting date in A1 and the ending date in B1, and the sales rep id in C1. And with Excel 2003

I know this is a easy one for someone, just not me right now.

Thanks.

 

[mrhstn]

Hi, assuming you have a recent version of office try

Book1
	A	B	C	D
1	11/12/2017	23/12/2017	2	5000
2
3
4
5	Date	Sales Rep	Cash Value
6	10/12/2017	2	1000
7	11/12/2017	2	6000
8	12/12/2017	2	6000
9	18/12/2017	2	4000
10	19/12/2017	3	6000
11	20/12/2017	3	7000
12	21/12/2017	2	2000
13	21/12/2017	3	7000
14	22/12/2017	2	7000
15	23/12/2017	3	9000
16	25/12/2017	3	4000
17	26/12/2017	3	3000
18	27/12/2017	1	7000
19	27/12/2017	2	2000
20	28/12/2017	1	1000
21	29/12/2017	1	1000
22	29/12/2017	1	8000
23	30/12/2017	1	6000
24	30/12/2017	1	6000
Sheet3

Cell Formulas
Range	Formula
D1	=AVERAGEIFS(C6:C24, A6:A24, ">="&A1, A6:A24, "<="&B1, B6:B24, C1)

 

[mrhstn]

Apologies, I missed your version in the title

Try

Book1
	A	B	C	D
1	11/12/2017	23/12/2017	2	5000
2
3
4
5	Date	Sales Rep	Cash Value
6	10/12/2017	2	1000
7	11/12/2017	2	6000
8	12/12/2017	2	6000
9	18/12/2017	2	4000
10	19/12/2017	3	6000
11	20/12/2017	3	7000
12	21/12/2017	2	2000
13	21/12/2017	3	7000
14	22/12/2017	2	7000
15	23/12/2017	3	9000
16	25/12/2017	3	4000
17	26/12/2017	3	3000
18	27/12/2017	1	7000
19	27/12/2017	2	2000
20	28/12/2017	1	1000
21	29/12/2017	1	1000
22	29/12/2017	1	8000
23	30/12/2017	1	6000
24	30/12/2017	1	6000
Sheet3

Cell Formulas
Range	Formula
D1	{=AVERAGE(IF((A6:A24>=A1)*(A6:A24<=B1)*(B6:B24=C1), C6:C24))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[DPARDY]

PERFECT! Dilly Dilly.