Help with SUMIF function and dates

[inkuku007]

Hi Everyone,

This is my first post to this forum, so please forgive any blunders.

I'm looking for some help sorting through some sales data for our farming business which runs into thousands of lines. I wanted to analyse the data as follows:

(A) Total Sales Per Month: easy to do manually, but I'm struggling with how to use date functions within SUMIF
(B) Total Sales By Product By Month: I've tried using SUMIF($D$45:$D$108,$A14,$H$45:$H$108) // but the range has to be updated manually for each month which cumbersome // I think I need to incorporate a date function, but you don't seem to be able to do nested functions in SUMIF
(C) Total Sales By Customer By Month: in March 2011 we start specifying the name of the customer as opposed to just writing "Cash Sales"

I would appreciate any help or suggestions - there are 30,000 lines, so it would take me years to do this manually!!!!

I've attached a file with a data extract.

Thanks
Andrew
Mar-10 Apr-10 May-10 Jun-10 Jul-10 Aug-10 Sep-10 Oct-10 Nov-10 Dec-10
(A) Total Sales per Month

(B) Total Sales by Product by Month
12 pce Braai Pack Chicken
Afhal mixed
Bacon
Basil and Avo Dressing
Biltong - Chicken chilli bite
Biscuits
Bones - Pork
Boschveld Cockerels
Braai Pack 12 pce
Breast Fillets
Breast Fillets SMOKED
Broilers Live Chickens
BULK Breast Fillets
BULK Burgers (10s)
BULK Drumsticks
BULK Leg Quarters
BULK Mince
BULK Thighs
Burgers Chicken
Burgers Chicken Budget 100g
Butter

We will need to define a unique list of all products - Is there a function to do this automatically from a data list on another tab - i.e. If we add a new months data and have new products, it updates the list in this report


(C) Total Sales By Customer By Month:
Ancel
Cash Sales
Clinton Muller
Louise Leak
Phinn
Sally

[Cheeky Charlie]

Hi, and welcome, try SUMIFS (rather than SUMIF)

[etaf]

I have created two very quick Pivot tables (takes a few minutes) and put onto new sheets - these can be changed and formatted very easily

product
Customer

I have then just formatted the Date in the pivot table to display as you required

Now with a pivot table you can do a lot of analysis , filter and if we change the data to be a list on 2003 or as a table in 2007/2010 - as you enter new data , the pivot table range will extend and all you need to do is refresh the pivot tables and it will add new products and new customers automatically

(the list function, I used on office 2003 - so it may be there is a better way to do this in later versions )

- what version do you have ?

if you dont know pivot tables - there are quite a few tutorials -
BUT please ask any questions here and i can do my best to answer

attached the new sheet -

I think using a pivot table will give you much more control over the report and analyse
you can select the items you want to show with just a few clicks of the filter

with your products and customers - I would create a table with the names in so that those can be used as a dropdown and validate on the data list that way - all your products and customer names will be entered with identical names and not run the risk of having

12 pce Braai Pack Chicken
12 piece Braai Pack Chicken
12 pce Braai Pk Chicken

in the data which, will be picked up as three separate items -

OR are you the only one using the spreadsheet and theres no chance of that type of issue

[:) Sixthsense :)]

Try Pivot Table, refer the attached file for details

[bodhi808]

Hi,

This would be far easier to do using a Pivot table to be honest, especially as the monthly update would be to simply refresh the pivot table.
However, looking at what you're trying to do.
I'd use SUMIFS rather than SUMIF to use multiple criteria and also add a column to the datalist with a formula to regulate the invoice date to the 1st day of each month.

See attached update of your doc to see what I mean.
RB Sales Data Extract 2 - bodhi808.xlsm

Chris

[inayat]

Please find attached the updated sheet.

[inkuku007]

Hi Guys, thanks for help. These are all great suggestions! You've saved me hours of work