Pivot Table- Sumif

Hi All,

I was wondering if any one could guide me how to use SumIf or any other functions while creating a pivot table from excel data in a very presentable manner.
The data shows - sales in qty to various customers for the month of may. Important columns need to be reported are Item, Qty.
On the second tab (in the attached sheet) i am showing the format how i want. I need to sumif by item code and put the total in the respective company column. I could manually do this by SumIf but if I want to do this for month by month for 4 months then how would i do it quickly.
Is there an easy way report for 4 month's comparative figures to make an analysis to see whihc company orders whihc product and the so on...

Some one please help.

Ah, the wonders of QuickBooks... that brings back some horrific memories...

See attached - you need to have a header value for every column that get's included in the PT Range (ie your blank columns need a dummy header value)

Not sure re: your 4 month comments given only one month incl. in this dummy data.

In terms of creation... pretty straight forward, lets work through from your file... note: below are for XL2007

Step 1 - make sure your header cells don't contain blanks
(see F1,H1 etc on BasicData sheet).

Step 2 - highlight your data range, in this instance E1:P216

Step 3 - with data highlighted go to Insert Tab on Ribbon and click Pivot Table -> Click OK

Step 4 - drag Item from the Field list into Row Label area

Step 5 - drag Name from the Field list into the Column Label area

Step 6 - drag Quantity from the Field list into the Values area
double click on "Count of Qty" in Values section & select "Value Field Settings" - choose SUM (and set Number Format to meet your requirements)

Step 7 - click OK

Done.

Re: 5 months... IMO you should run a QB report to export all 5 months on one report such that you have one download / data source ... working with data across tabs severely limits the PT functionality. Once you have the data in one contiguous set you can add the Date field as a criteria and subsequently Group the Date by Month & Year to get Monthly Analysis etc...

You will find lots of good info. on creating PTs on the web - including video tutorials on YouTube etc... if in doubt Google!

Post a sample so I can be sure but if I understand you correctly, yes.

I think you're saying you have transactions for company X, Y & Z spanning many months, you want to have Company as Column Field and you want to have Date as Column Field also but Date set to Group by Month & Year ?
(Date would in this instance be the 2nd Column Field rather than the 1st so as to get Company -> Month display as opposed to Month -> Company display)

Assuming all transactions are stored in one table (BasicData) then yes, follow my instructions in the prior post and you should get there... remember that you can Group on field Date by both Month & Year (presently you only have May data present).

If you're active on the PT you will see a Design bar in the Ribbon - you can use this to alter the PT Report Layout - it sounds as though you perhaps want to use Tabular layout ?