Sum of deliveries by contract for each year

riggsd

Board Regular
Joined
Jan 29, 2003
Messages
143
I have a listing of multiple contracts with the date (mm/dd/yy) each time a delivery was made and I need to find the sum of how many deliveries were made each year for each contract.

I'm assuming I'd need to use a sumifs or sumproduct but not sure how to do so.

Contracts are in range A2:A3390, dates are in range B2:B3390. I have 19 contracts with dates ranging from 2013 to 2018 so need a summary of each contract for each year. I've tried using a pivot table but either can't get the fields to show up at all or can't get it to sum by year per contract.

For example:
Contract Year Sum
Contract1 2013 10
Contract1 2014 75
Contract1 2015 84
... etc.
Contract2 2013 120
Contract2 2014 221
Contract2 2015 192
... etc.
Contract3 2013 45
Contract3 2014 64
... etc.

Thanks in advance.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Pivot Table:
Set up the Pivot Table with Contract for first Row item
Set Date for Second Row Item
Set the Contract into the Sigma area to get a Count.

Set the Design Mode for the Pivot Table to "Show in Tabular Form"
For now, turn of Grand Totals and All Subtotals.

Right Click a date showing in the Pivot Table. Select "Group By", Select only the Years option.
Rename any Headers.
If you want Subtotals by Contract, Right Click any Contract number and then select Subtotal.
Use the design Tab to select a different Color and border scheme.

* On your original data, make sure the dates are dates and not text values. Very important. If text, use Text-to-Columns to change to date values.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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