Thetownend.com

Hello

I need to try and produce a sales volume report which shows sales by region.

In Column A I have a site location
In Column B I have the region so if it is in the north or south.
In Column C I have the sales volume for the current year
In Column D I have sales volume for prior year
In column E I have the current years budget.
I have about 20 lines in total.

I need to use SUMIF formula?  Can any clever sod help?

SUMIF what? Need some logic to start with...

Oh, wait. Sales by region.

=SUMIF(B:B,"South",C:C)

Repeat for North.

Or filter  :)

Yeah, or that.

Pivot Table

Or Pivot table

Or sumproduct if you have more than one criteria that you wish to sum on

Just out of interest I used the Match/Index functionality last week instead of the usual vlookup – very powerful and very useful should anyone need an alternative – especially using parts of text.

I will take a look at that, thanks.  I do feel sometimes as if I over-use SUMIF and VLOOKUP.  I have a natural dislike of pivot tables for some reason.

Vlookup is my most overused function. The majority of my reports are data dumps with vlookups/sumifs/sumproducts feeding off of them. Vlookups on data range names are a great way to confuse people particularly when built with nested ifs/iserrors. [/geek].

I am very wary of Pivot Tables too - they are very useful if the data ranges don't change but are relatively rigid in format.
 

It's not very easy to build a pivot table in to a wider model...that's why I'm not so keen.  Sometimes, they are the obvious solution to handling an array of data, but I find I can only use them on a stand alone basis.

Exactly, you can build in formula fields and 2007+ does give a little more flexibility in style but with today's PCs any formula seem to run quickly so the vlookups et al run well.

I learnt about TABLE functions this year - very powerful but still struggle to build them in to the day to day modelling.

This is always a good reference for me  http://chandoo.org/wp/advanced-excel-skills/

That's handy.  Thanks.

We need a Accountancy sub forum on here.  It wouldn't be such a huge departure from TEF norms.

Living the dream  ;D

I'd burn the server before that day.

 :D

I'm surprised the Cheese sub-forum has not yet materialised.

The accountancy sub forum can be bolted onto my forum, all questions, no interest  :)

It may well be worth having an excel thread. A lot of us use it regularly and its good to learn things.

As I say to my team - 90% of excel is knowing that you can do something, the other 10%  15% is figuring it out and making it work.

Excel's great. It's not just for accountants. My life is just one big spreadsheet.

Whilst I have the attention of the accountants, is anyone in practice and dealing with auto-enrolment or are you all just management accountants?

I'm responsible for auto enrolment at my co Sam.

In that case I may send you a PM.

OK, fire away

I'm a Business Analyst now. It's more fun because it's got the word anal in it.

This thread has just got me damn confused...

I did some work on auto enrolment in my last job (but unlikely to be involved in my next one).

Does that make you a bum? ;D

When I was an Analyst it used to be impossible to a) explain it and b) make it sound remotely interesting when chatting to girls in bars.  Oddly, after I reverted to explaining that I was Anal ist they become far more interested in continuing the conversation.  I would explain that porn stars sometimes needed a stunt double for the Anal shots, and that was where I came in (giving of course).  Quite how they ever brought it was beyond me, but they often did before someone in the group would clock the stupidity of this and figure out I was some sort of Analyst.

back on topic, mrexcel.com has always been good to me

Do you happen to be a therapist aswell?

(http://i111.photobucket.com/albums/n128/itchyeyeball/analrapist.jpg)

Aren't you a secretary, Dave?

*Trainee

I weeelll keeeel you all you county cunty devillls