how to sum total by day in monthly file

[steve400243]

Hello, On the attached sheet. I am looking for the best way to sum the total by day. The day is noted in column "D", and the total column is "R". Please let me know your thoughts. The totals can be on another sheet if neded.

new Test.xlsm
	B	C	D	E	F	G	H	I	J								R
1							$ 3.26	$ 0.63	$ 1.37
2	Sales Order	Tracking	Ship Date	Purchase order	Shipped To	State	Order Charge	Cartons Out	Inner Picks								Total
3	53120226	578311179220	Jun.02.2022	53120226	PHYSICIANS EYE SURGERY CTR.	WA	1	1									$3.89
4	53120227	578311179210	Jun.02.2022	53120227	ST. MARY S EYE SURGERY CENTER	NJ	1	1									$3.89
5	52320221	578311179275	Jun.02.2022	52320221	EYECARE PHYSICIANS AND SURGEONS	OR	1	5									$6.41
6	60120225	578311179231	Jun.02.2022	60120225	EYE PHYSICIANS OF LANCASTER	PA	1	1									$3.89
7	60120223	578311179242	Jun.02.2022	60120223	RETINA SPECIALISTS OF IDAHO	ID	1	1									$3.89
8	121020216/0531A	578311179253	Jun.02.2022	121020216/0531A	FICHTE ENDL ELMER EYECARE	NY	1	1									$3.89
9	60120221	578311179264	Jun.02.2022	60120221	VISION INSTITUTE OF MICHIGAN	MI	1	1									$3.89
10	60220221	578311179323	Jun.02.2022	60220221	TLC VISION CENTERS LLC-419	CO	1		10								$16.96
11	60220224	578311179334	Jun.03.2022	60220224	EC LASER SURGERY INST. OF WI	WI	1	2									$4.52
12		578311179356	Jun.03.2022	052520223/0602B	BOSTON VISION LLC	MA	1	2									$4.52
13	52520222		Jun.03.2022	52520222	INSIGHT SURGICAL	NSW, AU	1	3									$5.15
14	60320225	578311179404	Jun.03.2022	60320225	EYE SPECIALISTS OF INDIANA	IN	1	3									$5.15
15							12	21	10								$66.05
DATA

Cell Formulas
Range	Formula
H4:H14	H4	=H3
R3:R14	R3	=[@[Order Charge]]*$H$1+[@[Cartons Out]]*$I$1+[@[Inner Picks]]*$J$1+[@[Same Day Order]]*$K$1+[@[Pallets Out]]*$L$1+[@[Handling In - Pallet $75.00min]]*$M$1+[@[Handling In - Carton $25.00min]]*$N$1+[@[Rec Storage, 1st - 15th = $0.15 per cuft $75.00min]]*$O$1+[@[Rec Storage, 16th - EOM = $0.15 per cuft $37.50min]]*$P$1+[@[BOL Generated]]*$Q$1
H15	H15	=SUBTOTAL(109,[Order Charge])
I15	I15	=SUBTOTAL(109,[Cartons Out])
J15	J15	=SUBTOTAL(109,[Inner Picks])
R15	R15	=SUBTOTAL(109,[Total])

 

[Sufiyan97]

Try

You will need to use date in c14 for which you want to get the total,

=SUMIF(C3:C14,C16,R3:R14)

You need to change the date inside the formula

=SUMIF(C3:C14,6/2/2022,R3:R14)

 

[steve400243]

Hello, Thanks for looking. Don't think I explained what I'm looking to get to very well. This will be for the entire month, just trimmed down here. I need to get the totals by day so that on any day that orders are shipped, I need that total noted somewhere to then sum the monthly total. Does that make sense?

 

[alansidman]

I would bring the table into power query and then do a group by on the date and sum the data in Column R.

 

[steve400243]

I would bring the table into power query and then do a group by on the date and sum the data in Column R.

Thanks for looking Alansidman, Never used power Query. Would I select Data, then From Table?

 

[alansidman]

From table/range
Then in the PQ Editor, select Group By. Group by the Date. Create a new column, Select Sum and then select the appropriate column. Close and Load to a worksheet.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[steve400243]

From table/range
Then in the PQ Editor, select Group By. Group by the Date. Create a new column, Select Sum and then select the appropriate column. Close and Load to a worksheet.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Thank you for this helpful information. I will look into this. Have a great week!