Macro to count the answers in a table

[Closed]
Report
Posts
2
Registration date
Sunday February 3, 2013
Status
Member
Last seen
February 4, 2013
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
Hello,

I have a problem in macro in Excel. I am working on a file that counts the number of responses (0 or 1) of a certain type under certain questions (Qs). Below is my raw data.

      
Type:	       Q1	Q2	Q3	Q4
Type A	       T	F	T	T
Type B	       F	F	F	F
Type C	       T	T	T	F
Type A	       T	T	F	F
Type D	       F	T	F	T
Type B	       T	T	F	F
Type B	       F	T	T	F
Type D	       T	T	T	T
Type B	       F	F	F	F
Type A	       T	F	T	F
Type D	       T	T	T	F



I need to count the number of responses, T or F, of a certain type.

My expected output should be:

***TYPE A	
	T	F
Q1	3	0
Q2	1	2
Q3	2	1
Q4	1	2




***TYPE B	
	T	F
Q1	1	3
Q2	2	2
Q3	1	3
Q4	0	3


***TYPE C		
	T	F
Q1	1	0
Q2	1	0
Q3	1	0
Q4	0	1


***TYPE D
	T	F
Q1	2	1
Q2	3	0
Q3	2	1
Q4	2	1



I hope someone could help.

Actually I can "force" do it by having a lot of concatenations. But any better and cleaner formula and/or macros are greatly appreciated.


Thanks! :)

3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Is this output on a new sheet. are all groups to be on same sheet ?
Posts
2
Registration date
Sunday February 3, 2013
Status
Member
Last seen
February 4, 2013

Hi rizvisa1, it will be good if the output will be on a new sheet. Is it possible to name the sheet as Type A/B/C/D respectively? Thank you very much! :)
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Theinvictus,

This can also be done using array formula's.

Range A1:E12 is used for your example data.

To get Type A Q1 number of T's:
=SUM(($A$2:$A$12="Type A")*($B$2:$B$12="T"))
To get Type A Q1 number of F's:
=SUM(($A$2:$A$12="Type A")*($B$2:$B$12="F"))
To get Type A Q2 number of T's:
=SUM(($A$2:$A$12="Type A")*($C$2:$C$12="T"))
To get Type A Q2 number of F's:
=SOM(($A$2:$A$12="Type A")*($C$2:$C$12="F"))
etc...

Keep in mind that array formula's needs to be confirmed by hitting Ctrl+Shift+Enter.

Best regards,
Trowa