Count all values in cell from selected column where name on that row is matched

Options

Hi Team,

I am looking for support in formula to do a special count. I have been trying COUNTIF, COUNTIFS, SUMIFS, INDEX, MATCH,...but have been unsuccessful.

I am trying to create a summary sheet referenced from main sheet where I can plug in Auto Name from main sheet and it will count how many "Yes" within that specific row (Or how many "No"...How many "N/A", etc)

The count in red is where i am looking for the formula to do the calculation.


Any suggestions or feedback will be very helpful.

Thank you!

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    edited 11/07/23
    Options


    I would add the total column to your data sheet, and then reference them in the rollup sheet using INDEX and MATCH:

    SUMIF in the data sheet. Obviously you will need to create another column for N/A:

    INDEX and MATCH in the rollup sheet. The way this works is you use MATCH to find out what row the name is on, let's say it's row 1. And then you use INDEX to find the value for the column on the row that matches, in the case of this example the Yes column for row 1. So the No column is very similar, you use the same MATCH formula because you are looking for the row with the name, and then the INDEX is looking at a different column. You will need to make another column for the N/A values.


  • Jessica Schuler
    Options

    Hi,

    I duplicated your sheets and found some formulas that work. I set up a summary columns in the "Survey Main Sheet" and then a VLOOKUP formula in the "Summary Sheet with Formula." See the attached document with step-by-step instructions, and let me know if have any additional questions.

    Thanks,

    Jessica

  • Jessica Schuler
    Options

    @James Keuning I really like how you used the video clips in your instructions. Is there a program you use to create them?

  • James Keuning
    James Keuning ✭✭✭✭✭
    Options

    I used Snagit. Its GIF feature is pretty basic, you can cut out content and that's about it. Camtasia, by the same company, is a better screen recorder with a ton of video feaures.

  • orapun phuthomdi
    Options

    Thank you @James Keuning and @Jessica Schuler !! This is super helpful.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!