calculate sum or weighted average of data found using Xlookup

[UndwaterExcelWeaver]

I have successfully extracted data from this table using the Xlookup function, but only when there is only one result per day. I need to know how to add to or alter this formula to correctly sum (lbs) or weighted average (fat/prot/solids) if there is more than one row per date. (rows 19 and 20 in the second table represent the instance of more than one per date).

The current extraction formulas are in the top table, with the bottom table being the source information.

Ched-Mozz Mass Balance 0224.xlsm
	C	D	E	F
23	50,000	0.36	-	-
20

Cell Formulas
Range	Formula
C23	C23	=IFERROR(XLOOKUP($D$1,UFRET!B:B,UFRET!J:J,,0,1),)
D23	D23	=IFERROR(AVERAGE(XLOOKUP($D$1,UFRET!$B:$B,UFRET!$Q:$Q,,1,1)),)
E23	E23	=IFERROR(XLOOKUP($D$1,UFRET!$B:$B,UFRET!R:R,,0,1),)
F23	F23	=IFERROR(XLOOKUP($D$1,UFRET!$B:$B,UFRET!P:P,,0,1),)

Ched-Mozz Mass Balance 0224.xlsm
	B								J						P	Q	R
1	Receiving date								Weight						Solids3	Fat4	True Protein (TP)5
2	1/0/1900								Lb.						%	%	%
3	1/27/2024								49,160						17.32	0.37	14.29
4	1/28/2024								48,380						18.55	0.35	14.79
5	1/29/2024								48,180						16.30	0.35	13.36
6	1/30/2024								47,960						17.03	0.36	14.07
7	1/31/2024								48,660						17.00	0.34	13.75
8	2/1/2024								47,740						18.01	0.34	14.15
9	2/2/2024								47,360						17.28	0.34	14.02
10	2/3/2024								48,520						17.39	0.35	14.11
11	2/4/2024								49,240						17.55	0.36	14.37
12	2/5/2024								49,100						17.34	0.35	14.07
13	2/6/2024								48,460						17.14	0.27	13.95
14	2/10/2024								50,100						17.44	0.35	14.18
15	2/11/2024								48,140						17.42	0.37	14.51
16	2/12/2024								48,540						17.12	0.36	14.26
17	2/13/2024								48,280						0.00	0.00	0.00
18	2/14/2024								49,440						17.40	0.34	13.99
19	2/15/2024								50,000						0.00	0.36	0.00
20	2/15/2024								55,000						0.00	0.38	0.00
21	1/0/1900								0						0.00	0.00	0.00
22	1/0/1900								0						0.00	0.00	0.00
23	1/0/1900								0						0.00	0.00	0.00
24	1/0/1900								0						0.00	0.00	0.00
25	1/0/1900								0						0.00	0.00	0.00
26	1/0/1900								0						0.00	0.00	0.00
27	1/0/1900								0						0.00	0.00	0.00
28	1/0/1900								0						0.00	0.00	0.00
29	1/0/1900								0						0.00	0.00	0.00
30	YTD								882,260						17.35	0.35	14.12
UFRET

Cell Formulas
Range	Formula
B2:B30,J2:J30,P2:R30	B2	='[UF retentate received 2024.xlsx]MAIN'!B5

 

[awoohaw]

I'm not going to create separate workbook. But, I think you can figure out what to do with this.
Two options, both with Excel 365.

Book1
	B								J						P	Q	R	S						Y	Z
1	Receiving Date								Weight						Solids3	Fat4	True Protien
2
3	2024-01-27								49,102						17.40%	0.38%
4	2024-01-28								48,321						16.70%	0.37%
5	2024-01-29								48,851						17.10%	0.38%
6	2024-01-30								48,936						17.00%	0.38%
7	2024-01-31								49,190						16.70%	0.36%
8	2024-02-01								48,832						17.40%	0.37%
9	2024-02-02								49,437						17.60%	0.37%
10	2024-02-03								48,708						17.10%	0.36%
11	2024-02-04								48,685						17.60%	0.36%
12	2024-02-05								48,675						17.70%	0.38%
13	2024-02-06								48,939						16.70%	0.37%
14	2024-02-07								48,060						17.60%	0.38%
15	2024-02-08								48,531						17.10%	0.38%
16	2024-02-09								48,444						17.00%	0.37%
17	2024-02-10								48,755						16.90%	0.36%
18	2024-02-11								49,547						17.90%	0.36%
19	2024-02-12								49,670						17.30%	0.38%
20	2024-02-13								48,169						17.80%	0.38%
21	2024-02-14								49,712						16.60%	0.37%
22
23
24									Excel 365 Filter Function									INDEX XMATCH
25	2024-01-27								49,102						17.40%	0.38%		49,102						17.40%	0.38%
26	2024-01-30								48,936						17.00%	0.38%		48,936						17.00%	0.38%
27	2024-02-04								48,685						17.60%	0.36%		48,685						17.60%	0.36%
28	2024-02-05								48,675						17.70%	0.38%		48,675						17.70%	0.38%
29	2024-02-06								48,939						16.70%	0.37%		48,939						16.70%	0.37%
30	2024-02-12								49,670						17.30%	0.38%		49,670						17.30%	0.38%
31	2024-02-13								48,169						17.80%	0.38%		48,169						17.80%	0.38%
32	2024-02-14								49,712						16.60%	0.37%		49,712						16.60%	0.37%
33
Sheet4

Cell Formulas
Range	Formula
J25:Q32	J25	=FILTER($J$3:$Q$21,$B$3:$B$21=B25,"")
S25:Z32	S25	=INDEX($J$3:$Q$21,XMATCH(B25,$B$3:$B$21,0,1),0)
Dynamic array formulas.

 

[UndwaterExcelWeaver]

I'm not going to create separate workbook. But, I think you can figure out what to do with this.
Two options, both with Excel 365.

Book1
	B								J						P	Q	R	S						Y	Z
1	Receiving Date								Weight						Solids3	Fat4	True Protien
2
3	2024-01-27								49,102						17.40%	0.38%
4	2024-01-28								48,321						16.70%	0.37%
5	2024-01-29								48,851						17.10%	0.38%
6	2024-01-30								48,936						17.00%	0.38%
7	2024-01-31								49,190						16.70%	0.36%
8	2024-02-01								48,832						17.40%	0.37%
9	2024-02-02								49,437						17.60%	0.37%
10	2024-02-03								48,708						17.10%	0.36%
11	2024-02-04								48,685						17.60%	0.36%
12	2024-02-05								48,675						17.70%	0.38%
13	2024-02-06								48,939						16.70%	0.37%
14	2024-02-07								48,060						17.60%	0.38%
15	2024-02-08								48,531						17.10%	0.38%
16	2024-02-09								48,444						17.00%	0.37%
17	2024-02-10								48,755						16.90%	0.36%
18	2024-02-11								49,547						17.90%	0.36%
19	2024-02-12								49,670						17.30%	0.38%
20	2024-02-13								48,169						17.80%	0.38%
21	2024-02-14								49,712						16.60%	0.37%
22
23
24									Excel 365 Filter Function									INDEX XMATCH
25	2024-01-27								49,102						17.40%	0.38%		49,102						17.40%	0.38%
26	2024-01-30								48,936						17.00%	0.38%		48,936						17.00%	0.38%
27	2024-02-04								48,685						17.60%	0.36%		48,685						17.60%	0.36%
28	2024-02-05								48,675						17.70%	0.38%		48,675						17.70%	0.38%
29	2024-02-06								48,939						16.70%	0.37%		48,939						16.70%	0.37%
30	2024-02-12								49,670						17.30%	0.38%		49,670						17.30%	0.38%
31	2024-02-13								48,169						17.80%	0.38%		48,169						17.80%	0.38%
32	2024-02-14								49,712						16.60%	0.37%		49,712						16.60%	0.37%
33
Sheet4

Cell Formulas
Range	Formula
J25:Q32	J25	=FILTER($J$3:$Q$21,$B$3:$B$21=B25,"")
S25:Z32	S25	=INDEX($J$3:$Q$21,XMATCH(B25,$B$3:$B$21,0,1),0)
Dynamic array formulas.

I am confused as to how these address the need sum total lbs per date or average out the test results. I got these results to populate but it just does the first row of data it encounters and accomplishes the same thing that the XLookup already did.

 

[awoohaw]

I am confused as to how these address the need sum total lbs per date or average out the test results. I got these results to populate but it just does the first row of data it encounters and accomplishes the same thing that the XLookup already did.

You're right. The xmatch won't work.
The filter will, but will be a few minutes.

Have you tried Power Query? Linking the source data in the Excel Data Model with Power Query you can then create Pivot Tables from the Data Model.
There are some great youtube video on PQ. Here is playlist by one:

https://www.youtube.com/playlist?list=PLmd91OWgLVSKnVrL0YxdOH61MAiqlFHac

 

[UndwaterExcelWeaver]

You're right. The xmatch won't work.
The filter will, but will be a few minutes.

Have you tried Power Query? Linking the source data in the Excel Data Model with Power Query you can then create Pivot Tables from the Data Model.
There are some great youtube video on PQ. Here is playlist by one:

https://www.youtube.com/playlist?list=PLmd91OWgLVSKnVrL0YxdOH61MAiqlFHac

I'm heading home for the day but I'll check back with your results in the morning. Thanks for the assistance.

 

[awoohaw]

Here is something you may be able to work with:
Although, if the data you have is a roll up of disparate counts, the averages are only averages of days and not averages of amounts of individual measurments(if that is what they are). You'd need to perform weighted average calculations.

Book1
	A								I						O	P	Q	R	S	T	U	V	W
1	Receiving Date								Weight						Solids3	Fat4	True Protien				Weight	Solids3	Fat4
2																					Avg	Avg
3	2024-01-27								49,102						17.40%	0.38%				2024-01-27	49,269.50	17.50%	0.3750%
4	2024-01-27								49,437						17.60%	0.37%				2024-01-28	48,321.00	16.70%	0.3700%
5	2024-01-28								48,321						16.70%	0.37%				2024-01-29	48,851.00	17.10%	0.3800%
6	2024-01-29								48,851						17.10%	0.38%				2024-01-30	49,063.67	17.33%	0.3667%
7	2024-01-30								48,936						17.00%	0.38%				2024-01-31	49,190.00	16.70%	0.3600%
8	2024-01-30								48,708						17.10%	0.36%				2024-02-01	48,832.00	17.40%	0.3700%
9	2024-01-30								49,547						17.90%	0.36%				2024-02-04	49,177.50	17.45%	0.3700%
10	2024-01-31								49,190						16.70%	0.36%				2024-02-05	48,422.00	17.75%	0.3800%
11	2024-02-01								48,832						17.40%	0.37%				2024-02-06	49,325.50	16.65%	0.3700%
12	2024-02-04								48,685						17.60%	0.36%				2024-02-10	48,755.00	16.90%	0.3600%
13	2024-02-04								49,670						17.30%	0.38%				2024-02-12	48,060.00	17.60%	0.3800%
14	2024-02-05								48,675						17.70%	0.38%				2024-02-13	48,531.00	17.10%	0.3800%
15	2024-02-05								48,169						17.80%	0.38%				2024-02-14	48,444.00	17.00%	0.3700%
16	2024-02-06								48,939						16.70%	0.37%
17	2024-02-06								49,712						16.60%	0.37%
18	2024-02-10								48,755						16.90%	0.36%
19	2024-02-12								48,060						17.60%	0.38%
20	2024-02-13								48,531						17.10%	0.38%
21	2024-02-14								48,444						17.00%	0.37%
22
Sheet1

Cell Formulas
Range	Formula
T3:T15	T3	=SORT(UNIQUE(A3:A21,FALSE,FALSE),1,1)
U3:U15	U3	=LET(d,T3,dates,$A$3:$A$21,weights,$I$3:$I$21,AVERAGEIFS(weights,dates,d))
V3:V15	V3	=LET(d,T3,dates,$A$3:$A$21,solids,$O$3:$O$21,AVERAGEIFS(solids,dates,d))
W3:W15	W3	=LET(d,T3,dates,$A$3:$A$21,fats,$P$3:$P$21,AVERAGEIFS(fats,dates,d))
Dynamic array formulas.