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)

Row\Col	A​	B​	C​
1​
2​	NY	NY	NY
3​	DC	DC	NY
4​	NN	PT	NN
5​	DC	NY	DC
6​

summary (processing)

Row\Col	A​	B​	C​	D​	E​
1​
2​	NY	1​	2​	2​	3​
3​	DC	2​	1​	1​	2​
4​	NN	1​	0​	1​	1​
5​	PT	0​	1​	0​	1​
6​

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:

Row/Col	A	B	C	D	E	F	G	H	I	J	K
1						Attending	Not Attending	No Information	Mail Sent	Mail Read	No Information
2	NY	1	2	2	3	2	1	0	1	1	1
3	DC	2	1	1	2	1	1	0	0	1	1
4	NN	1	0	1	1	0	0	1	1	0	0
5	PT	0	1	0	1	0	0	1	1	0	0
6

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.