Macro to count the answers in a table Closed

Question

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! :)

rizvisa1 · Answer

Is this output on a new sheet. are all groups to be on same sheet ?

theinvictus · Answer

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! :)

TrowaD · Answer

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

Macro to count the answers in a table

3 responses

Similar discussions

Subscribe To Our Newsletter!