Google Sheet: Get Data from 3 columns?

[gameover]

Dear All,

I am working on google sheet with loads of rows and columns. I have to summarize the results of main sheet into summary sheet.

Main sheet contains many columns like Name, email, phone, city 1, city 2, city 3. In summary sheet I have to give total numbers of city 1, city 2 and city 3. let me give you example here:

Row 1:

City 1: NY
City 2: NY
City 3: NY

Row 2:

City 1: DC
City 2: DC
City 3: NY

Row 3:

City 1: NY
City 2: DC
City 3: NY

Row 4:

City 1: DC
City 2: NY
City 3: DC

In summary Sheet I want following result:

Row 1 NY:

Column 1 (City 1 (total count)): 2
Column 2 (City 2 (total count)): 2
Column 3 (City 3 (total count)): 3
Column 4 (Unique Count of rows where NY is present ): 4 (As NY is present in every row and despite of it repeating in columns, it will be counted only once)

Row 2 DC:

Column 1 (City 1 (total count)): 2
Column 2 (City 2 (total count)): 2
Column 3 (City 3 (total count)): 1
Column 4 (Unique Count of rows where DC is present ): 3 (As DC is present in 3 rows)

Now in summary sheet I can easily get Column 1, 2 and 3 with the help of Pivot table but how to get column 4 is where I got lost.

Please let me know if my question is not clear or any more information is required.

Any help is appreciated.

Thanks a lot in advance for your time.

 

[Aladin Akyurek]

The lay-out is not clear. What does the following from your post really mean?

Row 1:

City 1: NY
City 2: NY
City 3: NY

 

[gameover]

The lay-out is not clear. What does the following from your post really mean?

Row 1:

City 1: NY
City 2: NY
City 3: NY

Thanks for the reply. Let me try to explain it little better.

I will try with cell numbers.

Cell A2 contains NY, B2 contains NY, C2 contains NY
Cell A3 contains DC, B3 contains DC, C3 contains NY

and so on.

I want following results in summary sheet

A1 should contain NY
B1 should have count of All NY present in mainsheet column 1
C1 should have count of all NY present in mainsheet column 2
D1 should have count of all NY present in mainsheet column 3
E1 should contain unique rows of NY in mainsheet. So even if NY is repeated in column 1 and 2 and 3, the count will show only 1 result of NY for that specific row

Please let me know if I have explained it in better form or else I will try once again.

Thanks a lot for your time.

 

[Aladin Akyurek]

main (data)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]NY[/td][td]NY[/td][td]NY[/td][/tr]
[tr][td]

3​

[/td][td]DC[/td][td]DC[/td][td]NY[/td][/tr]
[tr][td]

4​

[/td][td]NN[/td][td]PT[/td][td]NN[/td][/tr]
[tr][td]

5​

[/td][td]DC[/td][td]NY[/td][td]DC[/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

summary (processing)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]NY[/td][td]

1​

[/td][td]

2​

[/td][td]

2​

[/td][td]

3​

[/td][/tr]
[tr][td]

3​

[/td][td]DC[/td][td]

2​

[/td][td]

1​

[/td][td]

1​

[/td][td]

2​

[/td][/tr]
[tr][td]

4​

[/td][td]NN[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]
[tr][td]

5​

[/td][td]PT[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In B2 enter, copy across to D2, and down:

=COUNTIFS(main!A$2:A$5,$A2)

In E2 control+shift+enter, not just enter, and copy down:

=SUM(IF(MMULT((main!$A$2:$C$5=$A2)+0,TRANSPOSE(COLUMN(main!$A$2:$C$5)^0)),1))

 

[gameover]

Thanks Aladin. That worked perfectly.

Thanks for your time and help.

 

[Aladin Akyurek]

Thanks Aladin. That worked perfectly.

Thanks for your time and help.

You are welcome. Glad to help.

 

[gameover]

Hi,

I have further queries to my initial question. My Raw Main file looks like below:

Main Data:

Row/Col	A	B	C	D	E
1
2	NY	NY	NY	Attending	Mail Sent
3	DC	DC	NY	Attending	Mail Read
4	NN	PT	NN	No Information	Mail Sent
5	DC	NY	DC	Not Attending	No Information
6

What I need is following:

Summary:

[TABLE="width: 0"]
[TR]
[TD]Row/Col[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Attending[/TD]
[TD]Not Attending[/TD]
[TD]No Information[/TD]
[TD]Mail Sent[/TD]
[TD]Mail Read[/TD]
[TD]No Information[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]NY[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]DC[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]NN[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]PT[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

Thanks to Aladin, I have got my answer till column E. But need help in getting the count from Column F to Column K.

Please note that the Column E is always = Sum of Column F, G and H and also Column E is always = Sum of Column I, J and K

Thanks for your time and help. Please let me know if any further explanation is required.

Thanks again.

 

[Aladin Akyurek]

The question of post #7 ... Hope this is what you require:

In F2 control+shift+enter, not just enter, copy across to H2, and down:

=SUM(IF(IF(MMULT((main!$A$2:$C$5=$A2)+0,TRANSPOSE(COLUMN(main!$A$2:$C$5)^0)),1),(main!$D$2:$D$5=F$1)+0))

In I2 control+shift+enter, not just enter, copy across to K2, and down:

=SUM(IF(IF(MMULT((main!$A$2:$C$5=$A2)+0,TRANSPOSE(COLUMN(main!$A$2:$C$5)^0)),1),(main!$E$2:$E$5=I$1)+0))

 

[gameover]

Hi Aladin,

I don't have enough words to thank you. You are really a life saver.

Thanks a lot for your time and answering my question.

Regards.

 

[Aladin Akyurek]

Hi Aladin,

I don't have enough words to thank you. You are really a life saver.

Thanks a lot for your time and answering my question.

Regards.

Happy to help. Thanks for the kind words.