Sum Unique Values (Diff Row & Column ) from Different Sh

[Kamal Subhani]

Book1
	A	B	C	D
1	Product	Quality1	Quality3	Quality4
2	A	10	10	10
3	B	20	20	10
4	F	30	10	10
5	E	10	20	30
Sheet1

Book1
	A	B	C	D
1	Product	Quality3	Quality2	Quality5
2	C	10	10	10
3	D	20	20	10
4	A	30	10	10
Sheet10

Book1
	A	B	C	D	E	F
1	Product	Quality1	Quality2	Quality3	Quality4	Quality5
2	A	10	40	10	10	10
3	B	20	20	10	0	0
4	C	0	10	0	10	10
5	D	0	20	0	20	10
6	E	10	20	30	0	0
7	F	30	10	10	0	0
Main

In actual I have more than 10 sheets.

In the main sheet the Unique Products and the Unique quality will be displayed with the help of the formula

In the main sheet the total of each product according to qualties should be dispalyed


Plz help.

I think u undertand my problem.

 

[Kamal Subhani]

Re: Sum Unique Values (Diff Row & Column ) from Differen

As I am not expert in the Pivot Table I have to do a lot of calculation work on the data ain the Main sheet (Now in which Pivot Table is residing)

My problem has been to some extent solved But till I am not able to do the Calculation so Plz I am requesting all the Excel MVPs here to Plz tell me the formula to do this without using the Pivot Table approach

for summarizing the unique Values in Column A I have used the following Array Formula by Aladin and Yogi Anand
=UNIQUEVALUES(THREED(Sheet1:Sheet2!A2:A20),1)

for summarizing Unique values In the Range B1:J1 in The main sheet I ahev used the array formula by again Aladin

=INDEX(UNIQUEVALUES(THREED(Sheet1:Sheet2!$1:$1),1),COLUMN()-COLUMN($B$1)+1)


Now in the last Part I want to get the total for each Qualitry according to the Product in the Main Sheet


Hope that U people understanfd my problem and will help me out of this trouble.

Regards


Kamal

 

[Yogi Anand]

Re: Sum Unique Values (Diff Row & Column ) from Differen

As I am not expert in the Pivot Table I have to do a lot of calculation work on the data ain the Main sheet (Now in which Pivot Table is residing)

My problem has been to some extent solved But till I am not able to do the Calculation so Plz I am requesting all the Excel MVPs here to Plz tell me the formula to do this without using the Pivot Table approach

for summarizing the unique Values in Column A I have used the following Array Formula by Aladin and Yogi Anand
=UNIQUEVALUES(THREED(Sheet1:Sheet2!A2:A20),1)

for summarizing Unique values In the Range B1:J1 in The main sheet I ahev used the array formula by again Aladin

=INDEX(UNIQUEVALUES(THREED(Sheet1:Sheet2!$1:$1),1),COLUMN()-COLUMN($B$1)+1)


Now in the last Part I want to get the total for each Qualitry according to the Product in the Main Sheet


Hope that U people understanfd my problem and will help me out of this trouble.

Regards


Kamal

Hi Kamal:

Let us see if this is what you are looking for ...

y040203h1a.xls
	B	C	D	E	F	G	H	I	J	K
1	Aladin	Jack	Jill	Marry	Richie	Smith	Tom
2	1	1	1	1	1	2	2
Main

The formula in cell B2 is ... =COUNTIF.3D(Sheet1:Sheet2!$B$1:$Z$1,B1)

and this is then copied to cells B3 to Z3

so for reference sheet1 looks like ...

y040203h1a.xls
	B	C	D	E	F	G	H	I	J	K
1	Tom	Jill	Marry	Smith
Sheet1

and sheet2 looks like ...

y040203h1a.xls
	B	C	D	E	F	G	H	I	J	K
1	Jack	Aladin	Richie	Tom	Smith
Sheet2

I hope this helps!

 

[santeria]

Re: Sum Unique Values (Diff Row & Column ) from Differen

Just a minor query.

Formula Looks Excellent.

When trying to Filter Unique Phrases, do the Phrases need to be within Quotes ( "Dropped Call" as an example ) so that the number of unique Phrase, and also how many times the Phrase occurs, can be counted?

The Phrases will be in Columns, and basically I want to produce a summary of the unique Phrases.

Some Cells will be empty. Some Will have one word, some will have a word string, or maybe better described as a series of Phrases, separated by a comma (,).




Ta

 

[santeria]

Re: Sum Unique Values (Diff Row & Column ) from Differen

Mmm, well I was hoping to capialise on the basis of this thread and extend it a little.

Summing up Unique names is not as simple as I thought, especially in columns, anyway... if you know of a basic formula, or a thread that covers this, would be much appreciated.

Ta

 

[Yogi Anand]

Re: Sum Unique Values (Diff Row & Column ) from Differen

Hi santeria:

How about posting a sample of ...

1. data you are working with
2. what formulation have you tried
3. what result did you get
4. what do you think is the correct result with an explanation of why you think that is the correct result

and then let us take it from there!

NB: so as not to muddy the water with the matter of the original thread -- you might even consider starting a new thread.

 

[Aladin Akyurek]

Re: Sum Unique Values (Diff Row & Column ) from Differen

As I am not expert in the Pivot Table I have to do a lot of calculation work on the data ain the Main sheet (Now in which Pivot Table is residing)

My problem has been to some extent solved But till I am not able to do the Calculation so Plz I am requesting all the Excel MVPs here to Plz tell me the formula to do this without using the Pivot Table approach

for summarizing the unique Values in Column A I have used the following Array Formula by Aladin and Yogi Anand
=UNIQUEVALUES(THREED(Sheet1:Sheet2!A2:A20),1)

for summarizing Unique values In the Range B1:J1 in The main sheet I ahev used the array formula by again Aladin

=INDEX(UNIQUEVALUES(THREED(Sheet1:Sheet2!$1:$1),1),COLUMN()-COLUMN($B$1)+1)


Now in the last Part I want to get the total for each Qualitry according to the Product in the Main Sheet


Hope that U people understanfd my problem and will help me out of this trouble.

Regards


Kamal

Hi Kamal:

Let us see if this is what you are looking for ...

....

{=TRANSPOSE(UNIQUEVALUES(THREED(Sheet1:Sheet2!B1:Z1),1))}


The formula in cell B2 is ... =COUNTIF.3D(Sheet1:Sheet2!$B$1:$Z$1,B1)

and this is then copied to cells B3 to Z3

...

It's not counting but summing he looks for.

The thing is that a conditional 3d summing is not a problem. The OP has been told how many times. Apart from being a costly business, there is a bigger problem. The 3d formulas require identical layout in every sheet of interest. The OP's sheets are not identical in layout (surfaced up in this thread). So, a SumProduct formula with THREED (or the alternative that I posted in one of these interminable threads regarding OP's question) will not work at all for the fields of interest, say Quality7, do not occupy the same range in every sheet (Quality7 may even be absent altogether in some sheets).

I still maintain that Andrew's suggestion is the only game in town, regarding OP's question.

 

[santeria]

Re: Sum Unique Values (Diff Row & Column ) from Differen

Okay.
Probably should have a new thread.
I guess after seeing this one I saw some parallels.

The file I am using is at work( I'm home right now )

It's sort of trend Analysis using text in the file I am trying to sort out.
No messy Numerical data with odd formatting

Just reports with comments, and I have to provide Trainers and Managers with a look at the monitoring analysis.

So the powers that be want to know how many times a problem arises in monitoring, and then management talks to trainers and etc... and they do whatever it is that they do to get the Phone Operators back into the land of the righteous, and out of the wilderness ...

For ever and Ever Amen...

 

[PaddyD]

Re: Sum Unique Values (Diff Row & Column ) from Differen

"Probably should have a new thread. "

Yep A specific question also wouldn't go amiss!

 

[santeria]

Re: Sum Unique Values (Diff Row & Column ) from Differen

Granted I was maybe vague.
But since this thread is about "Sum Unique Value" and I was talking about Text Values, and sorting that for trend analysis, I thought that was sufficient ... Evidently not.

"Liberate tutume ex infernis"

"Probably should have a new thread. "

Yep A specific question also wouldn't go amiss!

 

[Kamal Subhani]

Re: Sum Unique Values (Diff Row & Column ) from Differen

Thanks all for replying

But still My problem not solved.Plz tell me what to do

Plz favour all the experts Gurus and MVPs in this forum.


For Gods sake


Kamal