Re: Summarize Table of Data By Category
Thanks Greg You were RIGHT I had not defined the Ranges Properly. Thanks very Very much for the Help.
Re: Summarize Table of Data By Category
Thanks Greg You were RIGHT I had not defined the Ranges Properly. Thanks very Very much for the Help.
Re: Summarize Table of Data By Category
Glad to be of help. Wish they were as appreciative where I work !!!
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
The sooner you read the links on Pivot Tables the sooner you can help yourself.
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
Quote from phiprbhDisplay MoreHi 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
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 phiprbhThanks 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
Quote from phiprbhDisplay MoreHello 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
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
I can't tell without seeing the dataset.
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
Sorry I forgot attach the excel sheet here it is
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...!!
Re: Summarize Table By Category
Hello someone Kindly reply.
Don’t have an account yet? Register yourself now and be a part of our community!