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