google sheets
Here's a copy of the Google Sheets I'm working on and here's what I'm trying to do:
I need a COUNTIF formula that would count as 1 if the sum from columns R to AD in the Failed sheet is less than 8. There are five potential responses to columns R to AD and each is assigned a weight:
0 - Not at all
1 - To a slight degree
2 - To a moderate degree
3 - To a great degree
4 - All the time
Based on the current data, row 30 in the Reports sheet should count as 2.
But how do I do that?
The following formula will display the corresponding teachers.
=ARRAYFORMULA(array_expression)
=VLOOKUP(search_criterion, array, index, sort_order)
=IF(test, then_value, otherwise_value)
=SIGN(number)
=ROW(reference)
=ARRAYFORMULA( // ARRAYFORMULA start
VLOOKUP( // VLOOKUP start
B2:B, // search_criterion
DATA!A2:G, // array
// index start
IF( // IF start
C2:C="1st", // test
{4,5}, // then_value
{6,7} // otherwise_value
) * // IF end
SIGN( // SIGN start
ROW( // ROW start
A2:A // reference
) // ROW end
), // SIGN end
// index end
0 // sort_order
) // VLOOKUP end
) // ARRAYFORMULA end
// to copy / paste
=ARRAYFORMULA(VLOOKUP(B2:B,DATA!A2:G,IF(C2:C="1st",{4,5},{6,7})*SIGN(ROW(A2:A)),0))
The SIGN and the ROW function are there to meet up with the criteria, set with using an ARRAYFORMULA. It will return an equally long array as setout in the search_condition. The IF function sets the condition for the index, by using the test.
I've copied your file and added my solution to it: teacher references
Use the formula below, in stead.
=QUERY('A'!A1:I200 , "SELECT * WHERE I = TRUE")
Best Answer
It looks like the correct countif formula is in your reports sheet right now - but in answer to your comment - if you want to simplify that huge if statement, you can use arrayformula, and vlookup or substitute to trim the formula to this:
for my example i gave it a short lookup table, but i included it as a literal array so that you dont have to create a helper column
or