# Formula to count by a set criteria

#### Muke

##### New member
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
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
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.