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

orapun phuthomdi

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!

James Keuning

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

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.

Instructions.pdf

Thanks,

Jessica

Jessica Schuler

@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

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

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