Formula to count by a set criteria

Muke

New member
Joined
Aug 12, 2012
Messages
28
Reaction score
0
Points
0
Hi there,

Please see the attached spreadsheet that I am trying to create. From the information (lines 2 to 13) in the table I want to create four small tables, one for each building (a, b, c & d), with numeric totals to show the amount of Yes in each column (labelled 1 to 10). The idea being that from the overall data collected, I can then break it down by building also. I have looked through the help files and I just can't seem to find anything that quite fits the bill.
Basically I want to total up if, column N equals A then how many 1's are Yes, how many 2's etc. and then repeat for each building.

Any help appreciated.

Cheers,

Muke
 

Attachments

  • PledgeTest.xlsx
    13.3 KB · Views: 12

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Try:

=COUNTIFS($O$2:$O$13,$A27,B$2:B$13,"yes")

copied down and across

if the column headers are not necessarily in same order, then you can use:

=COUNTIFS($O$2:$O$13,$A27,INDEX($B$2:$K$13,0,MATCH(B$26,$B$1:$K$1,0)),"yes")
 

Muke

New member
Joined
Aug 12, 2012
Messages
28
Reaction score
0
Points
0
Try:

=COUNTIFS($O$2:$O$13,$A27,B$2:B$13,"yes")

copied down and across

if the column headers are not necessarily in same order, then you can use:

=COUNTIFS($O$2:$O$13,$A27,INDEX($B$2:$K$13,0,MATCH(B$26,$B$1:$K$1,0)),"yes")


That has worked a treat, thank you so much for your help.
 
Top