MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Data summary


Posted by Joann B. on August 15, 2001 7:01 AM

I have a spreadsheet with multiple columns of data which represents the costs for different types of shipping errors. One of the columns is the date on which the error occurred, another is the cost for the error and the rest are broken down by the different types of errors. Right now I am charting the dollars lost for the different types of errors, and I would like to create a chart with a trend line for total dollars lost each month.

I would like to know if there is a formula that would seek out and tally the cost of the errors during a given month, even though the dates are not necessarily entered on the spreadsheet in chronological order (ie. a June date might be after a July date).

Thanks for your help.


Posted by Aladin Akyurek on August 15, 2001 7:35 AM

> I would like to know if there is a formula that would seek out and tally the cost of the errors during a given month, even though the dates are not necessarily entered on the spreadsheet in chronological order (ie. a June date might be after a July date).

Yes, but it would have helped a lot to have how your data are organized.
Lacking that, I'll use an example.

Lets assume the dates to be in A, Error X in B where dollar amounts are recorded.

Lets also assume that you have listed month numbers in E1:E12.

In F1 enter: =SUMPRODUCT((MONTH($A$1:$A$100)=$E1)*($B$1:$B$100)

Copy down this up to E12. You'll get monthly totals (lost dollars) as results.

Aladin