How to Sum/Average by specific cells

[jgaschler]

I am a novice in Excel and I am sure I am not using the correct nomenclature for my question. I apologize in advance for this and thank you for your time/questions in trying to help me!

Context:
I am keeping track of daily output for a produce shed that sorts and packages onions. We have hundreds of different 'Lot' numbers in total and run about 5-7 lots a day. We can use the same lot on different days.

Problem:
What I am trying to accomplish is a way to track the total bins and hours of a unique lot and the average output of each unique lot. This is notated by by the "?' in the cells of the screenshot below. What I am hoping to do is on the right side area of the Excel doc I can enter in the 'Lot' number and the following cells will calculate the data in the "?" cells from the data on the left hand side of the sheet. I manually enter in the data on the left hand side which has some basic functions in it.

I hope this makes sense but feel free to ask questions if I left out some crucial information.

 

[awoohaw]

What version of Excel are you using? Are your daily data in tables?

But it can be accomplished with AverageIFS. If you can get the xl2bb add in and send a mini workbook folks can help you easier?

 

[James006]

Hi,

In order to make your life much much easier ....
1. you should get rid of all you Daily Totals
2. you should delete all empty rows
3. you should fill-in Column A with all the dates

This would create, for you, a flat database, which will allow you to perform all the possible analyses and reports you can think of ....
First example would be Inserting a Pivot Table ...

Hope this will help

 

[jgaschler]

@awoohaw I am using 2019 Excel and the daily data is in tables, or at least my version of a table being a novice. I'll post the minisheet from xl2bb. Thanks!

@James006 Thanks for your advice. I am not too familiar with pivot tables or tables in general so I may have set up my data incorrectly it appears.
2. If I delete the empty rows would this mess up my data? My concern is we vary on a daily basis from on how many lots we run, usually 3-5 lots a day but can range up to 7.
3. By fill-in Column A with all the dates, are you meaning all the dates that refer to 1/3/2023 should have that date in it?

I'll educate myself on Pivot Tables as this may answer my questions for me. Thank you!

 

[jgaschler]

Here is my current worksheet:

Shed Packout Log for MrExcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	DATE	LOT	BINS	HOURS	SUPER	COLO	JBO	MED	OTH	TOT. BAG	BAGS/BIN
2
3	1/3/2023	MR1	228	6.67	349	707	4500	3821	0	9377	41.13		Daily Bag Output Avg.		10573.33
4		DA1	12	0.42	0	0	401	255	0	656	54.67
5		UD1	90	1.92	0	97	1680	0	0	1777	19.74
6
7
8
9													LOT	TOTAL BINS	TOTAL HOURS	AVERAGE SUPER	AVERAGE COLO	AVERAGE JBO	AVERAGE MED	AVERAGE OTH	AVERAGE TOT. BAG	AVERAGE BAGS/BIN
10	Daily Total		330	9.01	349	804	6581	4076	0	11810	35.79
11	1/4/2023	OTI	28	1	0	0	608	812	0	1420	50.71		MR1	?	?	?	?	?	?	?	?	?
12		MR1	6	0.67	0	0	0	0	487	487	81.17		DA1
13		JA7	20	0.83	0	0	321	198	0	519	25.95		UD1
14		MR1	210	6.5	342	669	4080	675	0	5766	27.46		OTI
15		UD1	52	1.17	0	54	1000	0	0	1054	20.27		JA7
16													HT3
17													UD2
18	Daily Total		316	10.17	342	723	6009	1685	487	9246	29.26
19	1/5/2023	HT3	102	3.17	264	398	1795	362	0	2819	27.64
20		DA1	66	2.58	0	0	2009	1554	0	3563	53.98
21		UD1	16	0.75	0	2	360	0	0	362	22.63
22		UD2	116	2.92	0	312	2400	0	0	2712	23.38
23		HT3	44	1.33	160	208	729	111	0	1208	27.45
24
25
26	Daily Total		344	10.75	424	920	7293	2027	0	10664	31.00
JAN

Cell Formulas
Range	Formula
J19:J23,J11:J15,J3:J5	J3	=SUM(E3:I3)
K26,K18:K23,K10:K15,K3:K5	K3	=IFERROR(J3/C3,"")
O3	O3	=AVERAGE(J10,J18,J26)
C10:J10	C10	=SUM(C3:C5)
C26:J26,C18:J18	C18	=SUM(C11:C15)

 

[Fluff]

I am using 2019 Excel

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’)

 

[kevin9999]

Avoid merged cells like the plague - there's hardly ever a reason you would need them, and they can cause all sorts of problems. I've removed the merged cells in the suggestion below. See if it gives you what you want. With reference to @awoohaw 's question - your data is in ranges, not Tables.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	DATE	LOT	BINS	HOURS	SUPER	COLO	JBO	MED	OTH	TOT. BAG	BAGS/BIN
2
3	1/03/2023	MR1	228	6.67	349	707	4500	3821	0	9377	41.127193			Daily Bag Output Avg.	10573.33
4		DA1	12	0.42	0	0	401	255	0	656	54.666667
5		UD1	90	1.92	0	97	1680	0	0	1777	19.744444
6
7
8
9													LOT	TOTAL BINS	TOTAL HOURS	AVERAGE SUPER	AVERAGE COLO	AVERAGE JBO	AVERAGE MED	AVERAGE OTH	AVERAGE TOT. BAG	AVERAGE BAGS/BIN
10		Daily Total	330	9.01	349	804	6581	4076	0	11810	35.787879
11	1/4/2023	OTI	28	1	0	0	608	812	0	1420	50.714286		MR1	444	13.84	230.33	458.67	2860.00	1498.67	162.33	5210.00	49.92
12		MR1	6	0.67	0	0	0	0	487	487	81.166667		DA1	78	3	0.00	0.00	1205.00	904.50	0.00	2109.50	54.33
13		JA7	20	0.83	0	0	321	198	0	519	25.95		UD1	158	3.84	0.00	51.00	1013.33	0.00	0.00	1064.33	20.88
14		MR1	210	6.5	342	669	4080	675	0	5766	27.457143		OTI	28	1	0.00	0.00	608.00	812.00	0.00	1420.00	50.71
15		UD1	52	1.17	0	54	1000	0	0	1054	20.269231		JA7	20	0.83	0.00	0.00	321.00	198.00	0.00	519.00	25.95
16													HT3	146	4.5	212.00	303.00	1262.00	236.50	0.00	2013.50	27.55
17													UD2	116	2.92	0.00	312.00	2400.00	0.00	0.00	2712.00	23.38
18		Daily Total	316	10.17	342	723	6009	1685	487	9246	29.259494
19	1/05/2023	HT3	102	3.17	264	398	1795	362	0	2819	27.637255
20		DA1	66	2.58	0	0	2009	1554	0	3563	53.984848
21		UD1	16	0.75	0	2	360	0	0	362	22.625
22		UD2	116	2.92	0	312	2400	0	0	2712	23.37931
23		HT3	44	1.33	160	208	729	111	0	1208	27.454545
24
25
26		Daily Total	344	10.75	424	920	7293	2027	0	10664	31
Sheet1

Cell Formulas
Range	Formula
J3:J5,J19:J23,J11:J15	J3	=SUM(E3:I3)
K3:K5,K26,K18:K23,K10:K15	K3	=IFERROR(J3/C3,"")
O3	O3	=AVERAGEIF($B:$B,"Daily Total",$J:$J)
C10:J10	C10	=SUM(C3:C5)
N11:O17	N11	=SUMIF($B:$B,$M11,C:C)
P11:V17	P11	=AVERAGEIF($B:$B,$M11,E:E)
C26:J26,C18:J18	C18	=SUM(C11:C15)

 

[jgaschler]

@kevin9999 Thank you for your advice! I believe these added formulas will give me what I am looking for. Thank you all for your efforts!

 

[kevin9999]

@kevin9999 Thank you for your advice! I believe these added formulas will give me what I am looking for. Thank you all for your efforts!

Happy to help, and thanks for the feedback