how to sum total by day in monthly file

steve400243

Active Member
Joined
Sep 15, 2016
Messages
429
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
BCDEFGHIJR
1$ 3.26$ 0.63$ 1.37
2Sales OrderTrackingShip DatePurchase orderShipped ToStateOrder ChargeCartons OutInner PicksTotal
353120226578311179220Jun.02.202253120226PHYSICIANS EYE SURGERY CTR.WA11$3.89
453120227578311179210Jun.02.202253120227ST. MARY S EYE SURGERY CENTERNJ11$3.89
552320221578311179275Jun.02.202252320221EYECARE PHYSICIANS AND SURGEONSOR15$6.41
660120225578311179231Jun.02.202260120225EYE PHYSICIANS OF LANCASTERPA11$3.89
760120223578311179242Jun.02.202260120223RETINA SPECIALISTS OF IDAHOID11$3.89
8121020216/0531A578311179253Jun.02.2022121020216/0531AFICHTE ENDL ELMER EYECARENY11$3.89
960120221578311179264Jun.02.202260120221VISION INSTITUTE OF MICHIGANMI11$3.89
1060220221578311179323Jun.02.202260220221TLC VISION CENTERS LLC-419CO110$16.96
1160220224578311179334Jun.03.202260220224EC LASER SURGERY INST. OF WIWI12$4.52
12578311179356Jun.03.2022052520223/0602BBOSTON VISION LLCMA12$4.52
1352520222Jun.03.202252520222INSIGHT SURGICALNSW, AU13$5.15
1460320225578311179404Jun.03.202260320225EYE SPECIALISTS OF INDIANAIN13$5.15
15122110$66.05
DATA
Cell Formulas
RangeFormula
H4:H14H4=H3
R3:R14R3=[@[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
H15H15=SUBTOTAL(109,[Order Charge])
I15I15=SUBTOTAL(109,[Cartons Out])
J15J15=SUBTOTAL(109,[Inner Picks])
R15R15=SUBTOTAL(109,[Total])
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Try

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

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

You need to change the date inside the formula

Excel Formula:
=SUMIF(C3:C14,6/2/2022,R3:R14)
 
Upvote 0
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?
 
Upvote 0
I would bring the table into power query and then do a group by on the date and sum the data in Column R.
 
Upvote 0
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.
 
Upvote 0
Solution
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!
 
Upvote 0

Forum statistics

Threads
1,223,957
Messages
6,175,623
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top