Countif for multiple columns

Options

I am trying to count a score for multiple columns in a smartsheet summary. For some reason, it is only counting the first column if I use this:

=COUNTIF([Educational Background]:[Educational Background], COUNTIF([Prior Work Experience]:[Prior Work Experience]) = 5)

If I put the extra ) at the end, it makes it unparseable.

I'm not sure what I am doing wrong.

Tags:

Answers

  • Austin Smith
    Austin Smith ✭✭✭✭✭
    Options

    @Chris Phillippe

    Are you trying to use two criteria to filter your count so that it only counts the number of instances that there are 5s in each column? If so, that formula would be:

    =countifs([Educational Background]:[Educational Background], =5, [Prior Work Experience]:[Prior Work Experience], =5)

    If you're just wanting to add up the total score for the two columns:

    =sum([educational background]@row, [prior work experience]@row)

    If you only want to sum scores where PWE=5, try

    =if([Prior Work Experience]@row=5, sum([educational background]@row, [prior work experience]@row),"")

    If I missed the mark, let me know.

  • Chris Phillippe
    Options

    Thank you for your help!

    I tried your first solution, and it does count them, but the number is wrong. It was saying 17 and there are more than that in just one column.

    I did find something online and it seems to be working well. (I added another column) =COUNTIF([Educational Background]:[Overall Impression and Recommendation], =5)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!