Previous Topic Next Topic

Macro to count the answers in a table

Closed
theinvictus
Posts
2
Registration date
Sunday February 3, 2013
Status
Member
Last seen
February 4, 2013
 - Feb 3, 2013 at 02:11 PM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 - Feb 5, 2013 at 10:59 AM
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

Answer 1 / 3
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 770
Feb 3, 2013 at 06:46 PM
Is this output on a new sheet. are all groups to be on same sheet ?
0
Answer 2 / 3
theinvictus
Posts
2
Registration date
Sunday February 3, 2013
Status
Member
Last seen
February 4, 2013
Feb 4, 2013 at 05:48 PM
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! :)
0
Answer 3 / 3
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 514
Feb 5, 2013 at 10:59 AM
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
0