• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to organize data for report

Kimber

Kimber

Member
I have financial data from a manually entered sheet provided by another department. I need to subtotal expenses by category for all products as well as by product for all categories. From the example you will see that a category was not provided for every product. This is an urgent need for my boss, and I'm stumped as to how to accomplish this most efficiently and expediently.


I have created another spreadsheet listing an entry for each category for each product and listed "0". Beyond that, I'm stalled. The link is for the "raw" data I received.


https://www.dropbox.com/s/12b7ss2kh7i677o/recap.xlsx


This is my first time uploading a file. Please let me know if i have made an error. Thank you for your help!
 
Hi, Kimber!

About your 1st upload, the file is perfectly downloadable, no error at all.

Regards!
 
say you make a table of Expense Categories in A61:A63


Customer Credits

Customer Payment

Legal/Expert


then in B61 you can put

=SUMIFS(B$5:B$58,$A$5:$A$58,$A61)

Copy across and down to produce:

[pre]
Code: 
Customer Credits	0	0	0	0	0
Customer Payment	19637	0	0	19637	26182
Legal/Expert		6656	25127	50039	81823	109097
[/pre]
Is that what your after?
 
Thank you, SirJB7 and Hui. Hui, your solution is great. I was stuck thinking I needed a pivot table and couldn't step back and see any other solution. I have one further question. I'm thinking about making a sheet for only when an entry appears (sometimes there is no data for a product in any quarter.) Can you suggest a formula to include the product, category and amount in the proper quarter column only if there is an entry on that row (or that meets that criteria?) Otherwise, I will use your very effective solution. Thank you again, Hui, for your prompt and very well written response.
 
Can you post a file highlighting what you require

I'm struggling to visualise it
 
Hi Kimber,


Please see the attached file, you need to be consistent in your layout, you might find some figures missing that is due to in-consitent layout:


https://dl.dropboxusercontent.com/u/60644346/recap.xlsx


Regards,
 
Faseeh, I've created a new worksheet using your formulas, but I couldn't get the second and third boxes to work. Would you please take a look at what I have and tell me what I'm doing wrong? I haven't used indirect before and I guessed at the target cells when I copied to my worksheet. Thank you so much.


https://www.dropbox.com/s/89ppdl37139omin/20130520Recap.xlsx
 
Kimber


J65: =SUMPRODUCT(($C$2:$G$2=$I65)*($B$3:$B$74=J$64)*($C$3:$G$74))

Copy down and across


You were looking up Column A instead of B for the category


J33: =SUMPRODUCT(($A$3:$A$74=$I33)*($B$3:$B$74=J$32)*($C$3:$G$74))

Copy down and across


These technique are described in detail here: http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/


Be careful using the range: $C$3:$G$74

as that includes Q1..Q3, FY and Projected

If you only want this year use $F$3:$F$74 instead

If you only want Projected use $G$3:$G$74 instead
 
Also J3: Could be:

=SUMPRODUCT(($A$3:$A$75=$I3)*($C$2:$G$2=J$2)*($C$3:$G$75))

Copy down and across
 
You must log in or register to reply here.
Back
Top