Sort table with only formulas...and not pivot table

**agweyre** · 07-31-2012, 08:08 PM

I have a sort problem that needs using with formulas...attaches is a doc that I'm doing for school...and I'm stuck...can anyone help me?work1.xlsx

**Pete_UK** · 07-31-2012, 08:16 PM

It's not really a sorting problem - the assignment asks you to summarise the data by region and by month.

So, set up a small table with the regions going down a column and the months going across a row, and then you can use a formula based around SUMPRODUCT or SUMIFS to get those values - as this is homework, it's not really in your interests if I give you a complete formula, so look up those functions in XL Help to find out how to use them.

Hope this helps.

Pete

**agweyre** · 07-31-2012, 08:23 PM

I'm still a bit confused by the table since i'm new to excel...is there a way you can provide me an example?

**Pete_UK** · 07-31-2012, 08:33 PM

Look at your data - you have two regions East and West, so put those in two cells somewhere, e.g. in G11 and G12. You also have data that covers 1st Jan to 31st March 2011, so in H10 you can put 1/1/11, and similarly for I10 and J10, and then format those cells so they only show the month. That is the basis for your table.

You then need a formula which will go into cell H11 that will add together the sales column when the date is the month shown in H10 and the region is as shown in G11, and then you can copy this across and down into the other cells.

Then you can pretty up the look of the table (formatting) by adding borders, lines, emboldening, colours etc. Get it working first, and then you can think about how it looks.

Hope this helps.

Pete

**agweyre** · 07-31-2012, 08:45 PM

Ah I'm so sorry to bother you pete...I'm still having a hard time creating this table...is there anyway you can provide me a mini example?

**Pete_UK** · 07-31-2012, 09:13 PM

What is difficult about typing the word East into cell G11, or the other instructions that I gave you?

I don't feel I can help you any more with the formula, as I will end up doing it for you and so you won't learn anything. Besides, it's gone 2:00am here, so time for bed for me.

Pete

**Cutter** · 07-31-2012, 09:19 PM

Further to what Pete has said, from the FAQ section of this forum:

In this forum we cannot allow direct help with homework or assignment questions, if we tell you how to do it you will never learn how to do it! We can answer specific questions and point you in the right direction, if you really don't understand your work/assignment then don't be afraid to contact your tutor as he/she will be pleased you came forward for help rather than struggle. One other thing, your tutor(s) may also frequent this and many other forums like it, either to help the community or sourcing training aids for their pupils so be mindful of this when seeking help!