Google Sheets COUNTIF

0
votes

I have a Google Sheet with a column of Names and some columns of Values I also have a column of total available Names

This Sheet is connected to a Google Form and i need to check if users have filled any value in Values Columns

I need a formula that indicates if there are any missing names (of total available Names) from the NAMES Column and also if a Name appears give a warn that this name has no values in its row

Here is a screenshot of what i mean enter image description here

Here is a shared form to give it a try https://docs.google.com/spreadsheets/d/1N2CCD7MdKBO0faj8akfdtX-K_JZ6o7hD0tZSVfnbrAs/edit?usp=sharing

2
google-sheetscountif

2 Answers

1
votes

I have added a new sheet ("Erik Help") with this formula:

=ArrayFormula(IFERROR(VLOOKUP(FILTER(J3:J,J3:J<>""),{C3:C,IF(D3:D&E3:E&F3:F&G3:G<>"","OK","Missing Values")},2,FALSE),"Missing Name"))

This looks up every value in the full names list (FILTERed to remove blanks) within a virtual array with names from form submissions in column 1 and a concatenation of all form VALUES per row in column 2.

If a name from the full list is not found within the form submissions, VLOOKUP throws an error, which is replaced with "Missing Name" by the IFERROR outer wrap.

If a name is found and the concatenation of all values fields is not blank (i.e., if there is even one value present in that row), "OK" is returned. Otherwise, "Missing Values" is returned.

0
votes

I think you need to use COUNTA. It worked for me in your testfile.
COUNTIF counts numbers, COUNTA also counts text.

=IF(COUNTA(D3:G3)<>0,"OK", "MISSING NAME")