# Thread: Formula to count by a set criteria

1. ## Formula to count by a set criteria

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

2. 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")

3. Originally Posted by NBVC
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•