Summarize Table By Category

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

The sooner you read the links on Pivot Tables the sooner you can help yourself.

Re: Summarize Table of Data By Category

Quote from phiprbh

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

Display More

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

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

Quote from phiprbh

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

Display More

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

I can't tell without seeing the dataset.

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

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.