Averageifs based on row range(multiple dates) and matching column range.

[fusion5151]

Hi,

I'm looking to build a formula to average a specific date range(row 2) based on a specific matching criteria in the row column A.

In this case once there is an average, i'd like to create a sum so for apples this would be a total of 14.

Also note my date ranges will consciously change so i'd like to adjust based on this criteria.

See below for table.

Start Date

3-Jan​

1-Jan​

2-Jan​

3-Jan​

4-Jan​

5-Jan​

6-Jan​

7-Jan​

8-Jan​

9-Jan​

10-Jan​

Average

End Date

7-Jan​

Apples

5​

10​

5​

8​

5​

2​

3​

7​

8​

7​

5​

Apples

Apples

9​

4​

7​

7​

2​

5​

2​

8​

2​

7​

5​

Orange

Oranges

5​

9​

7​

5​

1​

9​

5​

6​

9​

4​

5​

Tomato

Oranges

5​

3​

1​

2​

5​

1​

2​

3​

4​

4​

2​

Apples

5​

6​

6​

1​

1​

9​

4​

3​

8​

3​

4​

Tomato

3​

1​

7​

6​

4​

10​

4​

8​

5​

4​

6​

Tomato

8​

5​

7​

6​

1​

7​

2​

1​

1​

8​

5​

Thanks for all your help.

Steven

 

[fusion5151]

Hi,

I'm looking to build a formula to average a specific date range(row 2) based on a specific matching criteria in the row column A.

In this case once there is an average, i'd like to create a sum so for apples this would be a total of 14.

Also note my date ranges will consciously change so i'd like to adjust based on this criteria.

See below for table.

Start Date

3-Jan​

1-Jan​

2-Jan​

3-Jan​

4-Jan​

5-Jan​

6-Jan​

7-Jan​

8-Jan​

9-Jan​

10-Jan​

Average

End Date

7-Jan​

Apples

5​

10​

5​

8​

5​

2​

3​

7​

8​

7​

5​

Apples

Apples

9​

4​

7​

7​

2​

5​

2​

8​

2​

7​

5​

Orange

Oranges

5​

9​

7​

5​

1​

9​

5​

6​

9​

4​

5​

Tomato

Oranges

5​

3​

1​

2​

5​

1​

2​

3​

4​

4​

2​

Apples

5​

6​

6​

1​

1​

9​

4​

3​

8​

3​

4​

Tomato

3​

1​

7​

6​

4​

10​

4​

8​

5​

4​

6​

Tomato

8​

5​

7​

6​

1​

7​

2​

1​

1​

8​

5​

Thanks for all your help.

Steven

just wanted to also add i've been trying to adjust this formula but to no luck.

=AVERAGEIFS($B$2:$K$8,$A$2:$A$8,O3,$B$1:$K$1,">="&DATE(2023,1,3),$B$1:$K$1,"<="&DATE(2023,1,7))

If anyone has information on this please let me know.

Thanks
Steven

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[fusion5151]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hi Fluff,

Thanks and noted. i updated my profile. i'm currently using Microsoft 365 MSO on windows platform 64 bit.

 

[Fluff]

Thanks for that
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1															Start Date	03/01/2023
2		01/01/2023	02/01/2023	03/01/2023	04/01/2023	05/01/2023	06/01/2023	07/01/2023	08/01/2023	09/01/2023	10/01/2023		Average		End Date	07/01/2023
3	Apples	5	10	5	8	5	2	3	7	8	7		5		Apples	4.47
4	Apples	9	4	7	7	2	5	2	8	2	7		5		Oranges	3.80
5	Oranges	5	9	7	5	1	9	5	6	9	4		5		Tomato	5.40
6	Oranges	5	3	1	2	5	1	2	3	4	4		2
7	Apples	5	6	6	1	1	9	4	3	8	3		4
8	Tomato	3	1	7	6	4	10	4	8	5	4		6
9	Tomato	8	5	7	6	1	7	2	1	1	8		5
Home

Cell Formulas
Range	Formula
P3:P5	P3	=AVERAGE(FILTER(DROP(TAKE($B$3:$K$9,,XMATCH($P$2,$B$2:$K$2)),,XMATCH($P$1,$B$2:$K$2)-1),$A$3:$A$9=O3))

 

[fusion5151]

Thanks for that
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1															Start Date	03/01/2023
2		01/01/2023	02/01/2023	03/01/2023	04/01/2023	05/01/2023	06/01/2023	07/01/2023	08/01/2023	09/01/2023	10/01/2023		Average		End Date	07/01/2023
3	Apples	5	10	5	8	5	2	3	7	8	7		5		Apples	4.47
4	Apples	9	4	7	7	2	5	2	8	2	7		5		Oranges	3.80
5	Oranges	5	9	7	5	1	9	5	6	9	4		5		Tomato	5.40
6	Oranges	5	3	1	2	5	1	2	3	4	4		2
7	Apples	5	6	6	1	1	9	4	3	8	3		4
8	Tomato	3	1	7	6	4	10	4	8	5	4		6
9	Tomato	8	5	7	6	1	7	2	1	1	8		5
Home

Cell Formulas
Range	Formula
P3:P5	P3	=AVERAGE(FILTER(DROP(TAKE($B$3:$K$9,,XMATCH($P$2,$B$2:$K$2)),,XMATCH($P$1,$B$2:$K$2)-1),$A$3:$A$9=O3))

thank you fluff! this is perfect. to take this a step further would i could be able to adjust the formula to sum up all the averages?

For example, for the apples i'd like it to return 14 ttl units.

 

[Fluff]

Do you want the averages the formula gives, or do you just want to sum col M for each fruit?

 

[fusion5151]

Do you want the averages the formula gives, or do you just want to sum col M for each fruit?

Apologies for the confusion. I'm looking to average that specific time period so the formula above works as such, but after the average. would i be able to sum up all of the categories?

So in this case i'd like to add the apples in row 3/4/7 to ttl 14(column M) units during this time period.

I'm trying to create a dynamic formula as i will need to report multiple dates. looking to create a formula just based on the start/end date and looking at the category for the avg.
From there if possible getting the sum for all of the averages by category.

Not sure if its possible but thanks for your help thus far, this has been super helpful!

Steven

 

[Fluff]

You can use sumifs for for that.

 

[fusion5151]

You can use sumifs for for that.

ok, this will be broken down by line for average and adding another formula for sumifs for the sum by category?

I was trying to see if possible if it could just be 1 formula to create this calculation and then sum for the entire category.