Another Excel Thread

[fittons_coaching_badge]

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?

[Samdy Gray]

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

[Samdy Gray]

Oh, wait. Sales by region.

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

Repeat for North.

[4D]

Or filter 

[Samdy Gray]

Yeah, or that.

[REDBUCK]

Pivot Table

[Bewster]

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.

[Ardiles]

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.

[Bewster]

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.
 

[Ardiles]

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.

[Bewster]

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/

[Ardiles]

That's handy.  Thanks.

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

[Bewster]

Living the dream 

[sonicyouth]

That's handy.  Thanks.

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

I'd burn the server before that day.

[Ardiles]

I'd burn the server before that day.

 

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