Summarize Table By Category

  • Re: Summarize Table of Data By Category


    Hi Greg..


    I had to revisit this page again this morning since I have a requirement on the same issue. On Analysis Sheet(Example2.xls) we have an table


    Customer DP MP VP FP Total
    Tom 7 8 2 11 28
    Jerry 10 5 5 3 23
    Bunny 5 11 9 2 27


    And the formula for this is computation is =SUM(IF(Customers=$D2,IF(Category=F$1,Qty)))


    It is working fine, But I need to update the same table from another worksheet, I mean to say I need to add up values for the existing data for Tom , Jerry Bunny in their respective Category. How can I do this?


    regards

  • Re: Summarize Table of Data By Category


    Thanks Dave I shall consider Pivot Tables as per you suggestion, since I have already started an excel sheet with the following formula i would like to get some feed back on the same.

  • Re: Summarize Table of Data By Category



    You need to define the ranges in the other worksheet with different names (i.e. Customers2) and then change the formula to reflect the new range names (remember to enter array formulas with Ctrl + Shift + Enter).

  • Re: Summarize Table By Category


    Thanks Greg. I don't know whether I conveyed the point what I wanted to convy. For E.g if I place the Cursor on E2 it gives an formula like {=SUM(IF(Customers=$D2,IF(Category=E$1,Qty)))}. and the value in the cell shows 10, Now from another Sheet I need to update another 10 to the existing value using similar formula. How do I go about it


    regards

  • Re: Summarize Table By Category


    Quote from phiprbh

    Thanks Greg. I don't know whether I conveyed the point what I wanted to convy. For E.g if I place the Cursor on E2 it gives an formula like {=SUM(IF(Customers=$D2,IF(Category=E$1,Qty)))}. and the value in the cell shows 10, Now from another Sheet I need to update another 10 to the existing value using similar formula. How do I go about it


    regards


    I think this is what I'm trying to explain in my previous post. In the formula you provided 'Customers' & 'Category' are defined ranges in the 'Data' sheet. If you have another data sheet you are trying to analyse you'll need to define the ranges as different names to 'Customers' & 'Category' otherwise you will get conflicting range names. You then need to recreate the sumif array formulas replacing 'Customers' & 'Category' with the newly defined range names.


    It would probably be a lot easier to use pivot tables if you're not comfortable with the sumif array formulas, as Dave has suggested. I would advise you to investigate this method as it is quite a powerful and useful tool and it's simple to use.


    Greg.

  • Re: Summarize Table By Category


    Hello All


    Hi Greg..Kindly Excuse me that I have not still gone through Pivot Table, but I have tried to Define Range Vert1 for Wrksht1 , Vert2 for WorkSht2..etc.. Below is the formula but it shows Error #Value.


    =SUM(IF((Vert1+Vert2+Vert3) =$D2,IF((Cat1+Cat2+Cat3)=E$1,NoHrs1+NoHrs2+NoHrs3)))


    regards

  • Re: Summarize Table By Category



    Have you entered the formula with Ctrl+Shift+Enter (the formula will then show as being wrapped in curly brackets '{}') ? If you have then post the workbook so I can have a look. In the meantime LOOK AT PIVOT TABLES !

  • Re: Summarize Table By Category


    Yes, Greg I have
    {=SUM(IF((Vert1+Vert2+Vert3) =$D2,IF((Cat1+Cat2+Cat3)=E$1,(NoHrs1+NoHrs2+NoHrs3)))}


    Well just Let me know whether, what I am doing is right??? or show I go this way


    {=SUM(IF(Vert1 =$D2,IF(Cat1)=E$1,NoHrs1+))) + (IF(Vert2 =$D2,IF(Cat2)=E$1,NoHrs2))) +(IF(Vert3 =$D2,IF(Cat3)=E$1,NoHrs3+))) }


    regards

  • Re: Summarize Table By Category


    hi Greg


    Attached is the File Example2.xls. I have added Data2 workbook, on the Analysis Sheet I have colured the table with Yello and Red. I need to compute the data from Two Worksheet into one table.


    regards.

  • Re: Summarize Table By Category


    Let's give Greg a short break.


    Is there any reason why you can't just paste sheet 2 data below sheet 1 data so you have one table? You can even use dynamic named ranges which will vary according to the number of rows (or columns) of data you have.

  • Re: Summarize Table By Category


    Well Stephen There are Many Many Reason Why I should not paste SheetData. The example file which I have uploaded is a fictious one which does not give you a real picture or reason to WHY? What IF you consider Data from Dataone Belongs to one Deparment? and Data2 to an another ? They Don't Want to Share Data of Each other is very obvious.

  • Re: Summarize Table By Category


    Hi Greg


    Sorry to Bother U. since u were well assured of the issue I just included your name. Kindly excuse me for this.


    regards

  • Re: Summarize Table By Category


    Don't worry I was only joking.


    Not sure how you can do this atm, I think you'll have to combine the data at some point - you've certainly got to access both datasets so the data must be shared anyway.

  • Re: Summarize Table By Category


    Hello Stephen thank for your joke:) but now Greg has stopped responding?????? I feel he has taken real Break....from answering my posts...kindly help Greg...!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!