 # Summarize Table By Category

• 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

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

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

## Files

• 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.

## Participate now!

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