CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
privateace
privateace

Reputation: 1377

MS Excel: Using AGGREGATE to add up all mileage in each month

I have the following data in a logbook format:

DATE           MILEAGE
02-Jul-13         15
05-Jul-13         12
09-Jul-13         156
10-Aug-13         20
11-Aug-13         20
12-Aug-13         232
12-Aug-13         20
13-Aug-13         265
15-Aug-13         20
18-Aug-13         20

I am looking to extract data from it.

I need to ignore errors and #N/A so I have been trying to use the AGGREGATE function. To no avail though.

I would like to present the following information:

Mileage this month - 

=AGGREGATE(9,7, IF(MONTH(IFERROR(LogBookTable[Date], 0)) = MONTH(TODAY()), LogBookTable[Total KM], 0))    - Does not work

Mileage in July - 

=AGGREGATE(9,7, IF(MONTH(IFERROR(LogBookTable[Date], 0)) = MONTH(7), LogBookTable[Total KM], 0))    - Does not work

Mileage in August - 

=AGGREGATE(9,7, IF(MONTH(IFERROR(LogBookTable[Date], 0)) = MONTH(8), LogBookTable[Total KM], 0))    - Does not work

Total Mileage - 

=AGGREGATE(9,7,LogBookTable[Total KM])  - This works

The monthly mileage and current month mileage all result in a "#VALUE!" being displayed.

Any assistance would be much appreciated.

Just in case anyone asks, the naming schemes are corrected, it's not the references that aren't working, it's the values.

Upvotes: 1

Views: 600

Answers (2)

Werner
Werner

Reputation: 15095

You can use SUMIF is you add a column representing the month of the date:

enter image description here

Column B contains =MONTH of Column A. All you have to do is update the DATE entry in A16 with a new date. B16 is accordingly updated with the appropriate month, creating a new monthly aggregate/sum.

Upvotes: 0

RowanC
RowanC

Reputation: 1649

You could use DSUM. If you had two criteria cells setup Say in D1:E2 as follows:

    Date    Date
>1/07/2013  <31/7/2013

and if your data was in A1:B11 (as per your example above), to return a sum for July ignoring errors, use the following formula:

=DSUM(A1:B11,2,D1:E2)

You could setup the criteria such that they were updated based on the current month, and that the month end is calculated using:

="<"& DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(1)-1)

and the month start calculated by:

=">"& DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(1))

Remember that with criteria, multiple criteria on the same row is an AND, and multiple rows on the same criteria is an OR.

Upvotes: 1

Related Questions