Counting (and charting) values in date ranges by 2 level categories

[whites7]

Hi,


I have an application that exports a csv and I want to use Excel to create charts based in date criteria (either fixed by me or ideally via date range picker).


The numbers are associated with a main category and a sub-category:


Date Amount Category Subcategory

15/04/2012 -85 Financial Tax

16/04/2012 -2 Delivery Postage

16/04/2012 -3.7 Consumables Paper

16/04/2012 -3 Entertainment Pub

16/04/2012 22.5 Financial Invoice Payment

17/04/2012 -23 Delivery Air


So for example I'd like to report the total spent on delivery between January 2012 and March 2012. I would also like to breakdown the category into subcategory. So for example how much of the delivery costs were by postage or air.


The csv defined if it is an expense with a negative value and if it is a payment with a normal positive value.


Oh and if that wasn't enough I'd like to graph or pie chart it (though I can probably figure that out).


I have got as far as: =SUMIF(Date,">="&DATE(2012,2,15),Amount) by naming the columns but this doesn't define a date range or take into account categories so I'm stuck!


Thanks

 

[NARAYANK991]

Hi ,


The following formula will give you the total for :


a) Dates between April 16 , 2012 and April 17 , 2012 and

b) Category = "Delivery"


=SUMPRODUCT(((($A$10:$A$15>=DATE(2012,4,16))*($A$10:$A$15<=DATE(2012,4,17)))*($C$10:$C$15="Delivery"))*$B$10:$B$15)


If you wish to add the criteria of the sub-category , add it by using the following formula :


=SUMPRODUCT(((($A$10:$A$15>=DATE(2012,4,16))*($A$10:$A$15<=DATE(2012,4,17)))*($C$10:$C$15="Delivery")*($D$10:$D$15="Postage"))*$B$10:$B$15)


I am assuming your data is in the range A10 through D15.


Narayan