Results 1 to 3 of 3

Thread: Formula to count by a set criteria

  1. #1

    Formula to count by a set criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    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")


  3. #3

    Quote Originally Posted by NBVC View Post
    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
  •