Pivot Table- Sumif

[aromaveda]

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.

[DonkeyOte]

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.

[aromaveda]

DonkeyOte,

Yes QuickBooks Horifications!!

You are a GENIUS!!!! Now can you also let me know the steps for this? AND... If I download data for 5 months and want to write the Table on 5 different tabs for each month is it possible? If yes, then please also let me know the steps

SD

[DonkeyOte]

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!

[aromaveda]

One more question!

If i have to show data in sub columns is it possible? For example under the Company name if i want to show 3 months separate by each column, then same thing for the next company. Can the PT do it automatically?

SD

[DonkeyOte]

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)

[aromaveda]

Donkey,

Considering my basic data (as per attached) is for 4 months (May to Aug), i want to show the PT as per on the second tab. Is it possible?

Thanks
Sanjay