Count all instances across a few columns if there are at least 4 instances of a number below 4

%3CLINGO-SUB%20id%3D%22lingo-sub-2167035%22%20slang%3D%22en-US%22%3ECount%20all%20instances%20across%20a%20few%20columns%20if%20there%20are%20at%20least%204%20instances%20of%20a%20number%20below%204%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2167035%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20with%20two%20sheets.%20This%20spreadsheet%20collects%20customer%20survey%20satisfaction%20scores.%3C%2FP%3E%3CP%3ESheet%201%20has%20a%20mix%20of%20variables%20in%20column%20A%20that%20are%20IT%2C%20HR%2C%20Payroll%2C%20and%20Travel.%20There%20are%207%20questions%20scored%200-10%20across%207%20columns.%20There%20are%20thousands%20of%20rows%20and%20a%20huge%20amount%20of%20columns%2C%20I%20only%20need%20to%20calculate%207%20columns.%20I%20created%20a%20sample%20attachment.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%202%20has%202%20columns.%26nbsp%3BColumn%20A%20has%204%20cells%3A%20IT%2C%20HR%2C%20Payroll%2C%20and%20Travel.%26nbsp%3BI%20need%20column%20B%20to%20show%20HOW%20MANY%20TIMES%20each%20of%20those%20resulted%20in%20at%20least%204%20of%20the%207%20questions%20scored%20below%204.%20Please%20see%20my%20attached%20example.%20I%20have%20no%20idea%20how%20to%20use%20macros%20and%20want%20to%20use%20formulas%20for%20this.%3C%2FP%3E%3CP%3EPlease%20help%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2167035%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2167317%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20all%20instances%20across%20a%20few%20columns%20if%20there%20are%20at%20least%204%20instances%20of%20a%20number%20below%204%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2167317%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F979217%22%20target%3D%22_blank%22%3E%40Fiosguy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20use%20formulas%20for%20a%20helper%20column%20such%20as%20the%20one%20you%20used%20on%20the%20first%20sheet.%3C%2FP%3E%0A%3CP%3EYou%20can%20then%20use%20COUNTIFS%20to%20get%20the%20counts%20you%20need.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20workbook.%20You'll%20have%20to%20adjust%20the%20ranges%20for%20your%20real%20workbook%20of%20course.%3C%2FP%3E%0A%3CP%3E(Your%20example%20counts%20a%20score%20of%204%20and%20below%2C%20instead%20of%20below%204)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Thanks in advance!

I have a spreadsheet with two sheets. This spreadsheet collects customer survey satisfaction scores.

Sheet 1 has a mix of variables in column A that are IT, HR, Payroll, and Travel. There are 7 questions scored 0-10 across 7 columns. There are thousands of rows and a huge amount of columns, I only need to calculate 7 columns. I created a sample attachment. 

 

Sheet 2 has 2 columns. Column A has 4 cells: IT, HR, Payroll, and Travel. I need column B to show HOW MANY TIMES each of those resulted in at least 4 of the 7 questions scored below 4. Please see my attached example. I have no idea how to use macros and want to use formulas for this.

Please help

2 Replies

@Fiosguy 

You can use formulas for a helper column such as the one you used on the first sheet.

You can then use COUNTIFS to get the counts you need.

 

See the attached workbook. You'll have to adjust the ranges for your real workbook of course.

(Your example counts a score of 4 and below, instead of below 4)