Could anyone help me simplify a very long and drawn out process?

Hello everyone - this will probably be a wall of text post, so please bear with me. I will try to make it as concise and easy to understand as possible.

I do a monthly sales analysis for our 3 locations, wherein I pull the invoices issued every day for that month. The book I have attached shows what the raw data looks like when it is first exported, on Tab 1. I need to find the following informatio for each day:

• Sum of Total
• Sum of Gross Profit
• Number of invoices per day
• Average transaction amount

What I have been foolishly doing is inserting a row after every day's data (which in and of itself takes time) and then going in and manually inserting sum formulas for each required number, as seen on Tab 2. This involves continually highlighting each day's data, continually typing in formulas, and is all in all incredibly time consuming.

I'm looking for a way to make these calculations almost autonomous; is there any way to use a series of IF functions or vlookups or macros to make this process quicker? I've only recently started to truly understand what Excel is capable of, and I've really been enjoying learning about what it can do.

If anyone has any knowledge and/or insight to share regarding my predicament, I would greatly appreciate it!! The thought of possibly not having to sludge through this process 3 times a month makes me so excited I could dance!

Many thanks in advance!

As long as your data is sorted in invoice date order (as it seems to be), then you can put these formulae in the cells stated on Tab_1:

F2: =IF(C2<>C3,COUNTIF(C:C,C2),"")

G2: =IF(F2="","",SUMIF(C:C,C2,D:D))

H2: =IF(F2="","",SUMIF(C:C,C2,E:E))

I2: =IF(F2="","",H2/F2)

These will initially return blanks, but when you copy them down to the bottom of your data you will see the effect.

Get your dancing shoes out !!

Hope this helps.

Pete

OH MY GOSH! Pete, thank you so, so much! I literally feel I could cry, I'm so happy!! This has saved me so much time, I cannot thank you enough for taking the time to look into this for me!! You have absolutely made my day. :D Thank you thank you THANK YOU!!!!

-Unikron

Glad to be able to help.

If you wanted to make it even more straightforward, you could have another sheet in which you have a list of dates for the month, and then with COUNTIF and SUMIF formulae already in place you can produce your subtotals in a nice summary table, and then just drop new data into Tab_1 for each each month and the table will repopulate automatically. You can generate the first date with this formula:

=MIN('Tab_1'!C:C)

and then subsequent dates can be obtained by just adding one onto the previous date.

Hope this helps.

Pete

October sales analysis.xlsx

Hi Pete!

I've attached the updated spreadsheet for you to look at - what I would love to be able to do is have the totals from each province's tab automatically populate the tables in the "tables" tab. I know I can't realistically expect all my work to be done autonomously, so if there is any formatting that I need to do beforehand, that's quite alright with me. Currently I see the fastest way to fill the tables being copying each individual value and pasting it into the appropriate cell within each table, and that does feel dreadfully inefficient. The way I was doing it before was even WORSE, and I won't bring myself to tell you of that monstrosity.

Again, any insight you can provide would be so, so wonderful.

A million thanks in advance,

U

I've put the necessary formulae in the month_summary sheet, and left it to you to complete the overall totals. To make this more flexible, I've added drop-downs in C1 and C2 (coloured yellow), so that you can select the year and month of interest - at the moment, you only have data for September and October 2012, but this may grow in future.

Now that you have this sheet, you could delete columns H, I, J and K in the other three sheets.

The way I envisage you using this, is to copy new data for a new month at the bottom of what you already have in the appropriate sheet (ab, sk or wp), and then in the summary sheet you only need to use the drop-downs to get your summaries automatically - this should save you a lot of time each month.

Hope this helps.

Pete

P.S. Please mark the thread as Solved if you consider it to be so (the FAQ describes how).

Also, you can pass on thanks more directly to a contributor by clicking the "star" icon in the bottom left corner of any post you have found to be helpful.

This is mind-numbingly amazing Pete, thank you so, so much... I've made a little mistake actually though - previously I was compiling this data for calendar months (which is where the tables came from, I copied from my previous analyses), but decided to change it to fiscal. Our fiscal months end on the 27th, which means that - in this particular example - I actually need the September data to be displayed with the October data. The tables should actually look this (attached). Is there anyway to modify the formulae to populate correctly? If not, worst case scenario I just have to copy and paste a few lines of data.

Also, I would really love to LEARN how to do this stuff - is there a book you recommend, or an online course? Did you become a guru with time and lots of experimentation, or did you actively seek instruction? As our company continues to grow, spreadsheets are going to become more and more essential; as much as I've loved having your help Pete, I can't count on you forever! I would love to be able to do what you do, and I'm eager to learn!

In answer to post #7, I've changed the formulae so that every month will now begin with 28th, and will finish on 27th of the following month, no matter how many days there are in each month (check out Feb 2012 and Feb 2013). You should move away from thinking about manual copy/paste solutions to problems which occur regularly. Choose the month and year using the drop-downs as before, but the month should be the one which starts the list.

Hope this helps.

Pete

Regarding post #8, I firmly believe that if you want to become good at something, then you have to DO it. I've been posting on-line to various Excel groups for 8 or 9 years, and I learn from every post that I read or contribute to - either to re-affirm and strengthen what I already know, or to learn something completely new. There are many books and many on-line resources (many free, like this forum), so there are lots of opportunities to learn more if you want to, and of course these forums will be around to answer your questions and help solve problems for a long time yet (even if I'm not available, there are other contributors who will help).

Study the formulae that I gave you originally, and then see how they compare with those in workbook ef263. Then compare those with the ones I have just included in workbook ef263a. In essence, I am using COUNTIF (which counts conditionally, i.e. only counts if some condition is met - the dates match, in other words) and SUMIF (which is a conditional sum - i.e. add things up if the dates match). Note that in column A of workbook ef263 I only had numbers, and so had to form the date within the SUMIF and COUNTIF functions, whereas in ef263a I formed the date in column A, so there is no need to in the other formulae. When you can understand those formulae and why they differ, then you will become able to apply them to other workbooks in other situations.

Hope this helps.

Pete

I'm trying to understand the formulae as best I can, but I'm definitely having difficulty. I'm confident I will get it though. Because I still don't quite understand how everything works, I'm unsure as to whether or not I can have the month drop-down display "October," even though the data starts in September, without screwing everything up. It's very easy for things to get muddled for me - I'm so new to all of this, it can be very disorienting. Is it possible to do such a thing?

Okay, there is just one cell you need to change to allow you to do that, and then copy the new formula to 2 other cells. This is the formula you have in cell A6:

=IF(OR($C$1="",$C$2=""),"",DATE($C$2,$C$1,28))

which basically says that if either the month or the year is blank, then return a blank, otherwise return the date made up from the 28th day of the month in C1 and the year in C2. You don't actually get to see the full date, as the cell is formatted so that it only shows the day part of the date. Now, if you want to be able to put 10 in C1 for October, but for that first date to start a month earlier, then all you really need to do is to subtract 1 from the value in C1 in that date formula, so the new formula in A6 would become:

=IF(OR($C$1="",$C$2=""),"",DATE($C$2,$C$1-1,28))

where I have highlighted the change that needs to be made in red. This formula can then be copied into G6 and into M6, so that the 3 columns of dates are generated in the same way. Note that the other dates in those columns are generated by adding 1 onto the previous date, so it is only the first one in each column that we need to change, and then the other dates will adjust automatically. It should be noted as well that Excel is clever enough to cope with this calculation even when you choose 1 (January) in C1. This would mean that we have a month of zero in the DATE function, but Excel copes with this and adjusts the year, so that we would start with 28th December 2011 if we selected 1 and 2012 in C1 and C2 respectively. If the DATE function did not do this, we would need to do this in A6:

=IF(OR($C$1="",$C$2=""),"",DATE(IF($C$1=1,$C$2-1,$C$2),IF($C$1=1,12,$C$1-1),28))

so it would be possible to cope even if Excel's DATE function did not. BUT, we don't need to do that, so just make the change as indicated in the second formula.

Hope this helps,

Pete

Absolutely brilliant. I was hoping that a -1 or +1 could be used somewhere, since that immediately occurred to me to be the simplest solution, but I didn't know if Excel would accept that. Thank you so, so very much. I have another project that I'm thinking about building a spreadsheet for (currently it uses a very simple vlookup up to pull the previous day's data, and it works well enough, but I have a feeling it could be much more efficient), so you may see another post from me soon.

Thanks again, times a million!!!

U

Glad to hear it worked for you - thanks for feeding back.

There are other things you might like to think about for this project - another summary sheet which shows the performance of the members of staff by month, for example. But, maybe that will be for another day as well.

Pete