Excel averageifs-

[Ira Hopkins]

Hi hoping someone can help- looking for an average ifs but want to include "normal" or "Duplicates" between 2 dates in the average but not "first" -

Code	Item	Name	Date	Type	Average	Charge
132​	Bread	Pete	22/02/2021	duplicate	3.00%	£146.42
132​	butter	lorraine	07/12/2022	duplicate	4.00%	£104.80
132​	Bread	john	22/02/2021	duplicate	13.00%	£472.75
132​	butter	david	07/12/2022	duplicate	6.69%	£700.00
132​	Bread	colin	22/02/2021	duplicate	30.10%	£35.20
176​	butter	Pete	07/12/2022	normal	3.00%	£26.60
176​	Bread	lorraine	22/02/2021	normal	17.00%	£382.90
176​	butter	john	07/12/2022	First	1.00%	£36.20
176​	Bread	david	22/02/2021	First	0.00%	£0.00
176​	butter	colin	07/12/2022	First	0.00%	£0.00
176​	Bread	mark	08/04/2014	normal	0.00%	£8.20
176​	butter	lorraine	23/04/2015	normal	0.00%	£0.00
176​	Bread	john	25/04/2016	normal	23.00%	£933.35
176​	butter	david	24/04/2017	duplicate	15.33%	£1,678.50
176​	Bread	colin	03/12/2018	normal	6.80%	£228.20
176​	butter	david	09/01/2020	duplicate	30.67%	£486.70
241​	Bread	colin	05/06/2009	normal	3.00%	£157.00
241​	butter	mark	15/05/2012	normal	4.00%	£111.10
241​	Bread	lorraine	05/05/2015	normal	7.00%	£358.30
241​	butter	Pete	07/08/2008	normal	0.00%	£0.00
241​	Bread	lorraine	10/01/2012	normal	1.00%	£8.00
241​	butter	john	04/02/2015	normal	0.00%	£0.00
241​	Bread	david	22/11/2018	normal	7.77%	£203.40
241​	butter	colin	08/01/2020	duplicate	6.52%	£117.30
243​	Bread	Pete	13/12/2011	normal	0.00%	£0.00
243​	butter	david	11/11/2014	normal	0.00%	£0.00
243​	Bread	colin	04/04/2017	normal	7.00%	£175.70
243​	butter	mark	01/05/2018	duplicate	0.00%	£0.00
244​	Bread	lorraine	15/04/2011	normal	1.00%	£36.20
244​	butter	Pete	26/09/2014	normal	1.00%	£82.40
244​	Bread	lorraine	28/07/2017	normal	0.00%	£0.00
252​	butter	john	26/07/2010	normal	5.00%	£254.50
252​	Bread	Pete	19/07/2013	normal	0.00%	£0.00
252​	butter	lorraine	10/10/2016	normal	2.00%	£68.60
252​	Bread	john	01/03/2020	normal	2.91%	£56.00
270​	butter	david	29/06/2015	normal	1.00%	£40.00
270​	Bread	colin	10/07/2018	normal	0.97%	£40.40
149​	butter	Pete	05/10/2020	normal	0.00%	£0.00

 

[Fluff]

Which column are you trying to get the average of?

 

[Ira Hopkins]

from the average column- sorry not very good at this so it adds the average in the average column together and divides by the number of times either Duplicate or first appears but between 2 dates ( eg 1/4/20 - 31/3/21)

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Code	Item	Name	Date	Type	Average	Charge
2	132	Bread	Pete	22/02/2021	duplicate	3.00%	£146.42			01/04/2020	0.1262
3	132	butter	lorraine	07/12/2022	duplicate	4.00%	£104.80			31/03/2021
4	132	Bread	john	22/02/2021	duplicate	13.00%	£472.75
5	132	butter	david	07/12/2022	duplicate	6.69%	£700.00
6	132	Bread	colin	22/02/2021	duplicate	30.10%	£35.20
7	176	butter	Pete	07/12/2022	normal	3.00%	£26.60
8	176	Bread	lorraine	22/02/2021	normal	17.00%	£382.90
9	176	butter	john	07/12/2022	First	1.00%	£36.20
10	176	Bread	david	22/02/2021	First	0.00%	£0.00
11	176	butter	colin	07/12/2022	First	0.00%	£0.00
12	176	Bread	mark	08/04/2014	normal	0.00%	£8.20
13	176	butter	lorraine	23/04/2015	normal	0.00%	£0.00
14	176	Bread	john	25/04/2016	normal	23.00%	£933.35
15	176	butter	david	24/04/2017	duplicate	15.33%	£1,678.50
16	176	Bread	colin	03/12/2018	normal	6.80%	£228.20
17	176	butter	david	09/01/2020	duplicate	30.67%	£486.70
18	241	Bread	colin	05/06/2009	normal	3.00%	£157.00
19	241	butter	mark	15/05/2012	normal	4.00%	£111.10
20	241	Bread	lorraine	05/05/2015	normal	7.00%	£358.30
21	241	butter	Pete	07/08/2008	normal	0.00%	£0.00
22	241	Bread	lorraine	10/01/2012	normal	1.00%	£8.00
23	241	butter	john	04/02/2015	normal	0.00%	£0.00
24	241	Bread	david	22/11/2018	normal	7.77%	£203.40
25	241	butter	colin	08/01/2020	duplicate	6.52%	£117.30
26	243	Bread	Pete	13/12/2011	normal	0.00%	£0.00
27	243	butter	david	11/11/2014	normal	0.00%	£0.00
28	243	Bread	colin	04/04/2017	normal	7.00%	£175.70
29	243	butter	mark	01/05/2018	duplicate	0.00%	£0.00
30	244	Bread	lorraine	15/04/2011	normal	1.00%	£36.20
31	244	butter	Pete	26/09/2014	normal	1.00%	£82.40
32	244	Bread	lorraine	28/07/2017	normal	0.00%	£0.00
33	252	butter	john	26/07/2010	normal	5.00%	£254.50
34	252	Bread	Pete	19/07/2013	normal	0.00%	£0.00
35	252	butter	lorraine	10/10/2016	normal	2.00%	£68.60
36	252	Bread	john	01/03/2020	normal	2.91%	£56.00
37	270	butter	david	29/06/2015	normal	1.00%	£40.00
38	270	Bread	colin	10/07/2018	normal	0.97%	£40.40
39	149	butter	Pete	05/10/2020	normal	0.00%	£0.00
Data

Cell Formulas
Range	Formula
K2	K2	=AVERAGEIFS(F:F,D:D,">="&J2,D:D,"<="&J3,E:E,"<>First")

 

[Ira Hopkins]

Thanks Fluff but instead of the J2 & J3 is it possible to add up all the cell averages that are between those dates not containing a "first"? A lot will be using the sheet so if possible would like to pick up the dates from column D without having to add a J column- This is so confusing for me so really appreaciatte your help

 

[Fluff]

It is picking up the dates in col D, J2 & J3 are just to make things easier so you don't have to change the formula if the date range changes.

 

[Ira Hopkins]

I think its working !!!!!!!! - Thanks so much Fluff for all your help You are an Angel!

 

[Fluff]

You're welcome & thanks for the feedback.