A normal IF formula works on one row, so you copy it down the column. ARRAYFORMULA lets a single formula in the top cell fill the whole column, and it keeps working when rows are added. It’s simple until you need two conditions: the usual AND and OR don’t work inside it, and the formula doesn’t tell you. This guide shows what does work, tested on a list of ten students.
Scores are in B2:B11 and attendance in C2:C11. Row 7 has no score, and row 8 has a score typed as text, “90”. The full table is in IF with multiple conditions.
Tested in Google Sheets. Every formula on this page was run on November 13, 2026, and the results below are the ones Google Sheets returned. How we test
The basic formula
=ARRAYFORMULA(IF(B2:B11>=80, "Pass", "Fail"))
Typed once, in the first row, it returned ten results, one per row: the same as copying =IF(B2>=80, "Pass", "Fail") down. The changes are the range in place of the single cell, and ARRAYFORMULA around everything.
Leave the cells below the formula empty. The results spill into them, and if one of them already holds something, the formula shows #REF! instead.
Without ARRAYFORMULA
Where we typed =IF(B2:B11>=80, "Pass", "Fail") |
Result |
|---|---|
| Row 2, next to the data | A single “Pass”: row 2’s result only |
| Row 13, below the data | #VALUE! The default output of this reference is a single cell in the same row but a matching value could not be found. To get the values for the entire range use the ARRAYFORMULA function. |
In a row inside the data, the formula quietly used that row’s cell and returned one value, which can look like a working formula.
Two conditions: AND returns a single cell
Found in testing: =ARRAYFORMULA(IF(AND(B2:B11>=80, C2:C11>=0.8), "Pass", "Fail")) returned one “Fail” in the first cell and nothing below it. AND doesn’t work row by row: it checks every value at once and returns a single answer. On its own, =ARRAYFORMULA(AND(B2:B11>=80, C2:C11>=0.8)) returned one FALSE, because not every student met both conditions. OR did the same: ARRAYFORMULA(IF(OR(…), "Merit", "")) returned a single “Merit”, since at least one student qualified.
Inside ARRAYFORMULA, write the conditions in brackets and combine them with arithmetic:
| Logic | Formula | Result |
|---|---|---|
| AND: multiply | =ARRAYFORMULA(IF((B2:B11>=80)*(C2:C11>=0.8), "Pass", "Fail")) |
Pass for Ava, Ivy and Uma; Fail for the rest |
| OR: add | =ARRAYFORMULA(IF((B2:B11>=90)+(C2:C11>=0.95), "Merit", "")) |
Merit for Ava, Ivy and Max |
These were the same results as the row-by-row formulas with AND and OR. Each bracket gives TRUE or FALSE per row. Multiplied, they give 1 only when both are true. Added, they give 1 or 2 when at least one is true, and IF treats any number other than 0 as true.
Stop the column where the data ends
An open range such as B2:B includes new rows automatically, but it also includes every empty row below your data:
=ARRAYFORMULA(IF(B2:B>=80, "Pass", "Fail"))
In our 300-row sheet, this wrote 299 results. 294 of them were “Fail”, because an empty score counts as 0. Check another column for a blank first:
=ARRAYFORMULA(IF(A2:A="", "", IF(B2:B>=80, "Pass", "Fail")))
This left the rows without a name blank. =COUNTIF(range, "?*") on the output counted 10 results, one per student. COUNTA counted 299, because it counts the "" cells too; COUNTIF not blank explains the difference.
Several bands with IFS
IFS also works inside ARRAYFORMULA:
=ARRAYFORMULA(IFS(B2:B11>=90, "A", B2:B11>=80, "B", TRUE, "F"))
It returned a grade for every row. Keep the final TRUE pair, or rows that match no band show #N/A.
Empty cells and text numbers
=ARRAYFORMULA(IF(ISBLANK(B2:B11), "No score", IF(B2:B11>=80, "Pass", "Fail")))returned “No score” for Eli’s empty cell.=ARRAYFORMULA(IF(B2:B11="", "", B2:B11*1.1))left Eli blank and multiplied the rest. Ivy’s text “90” became 99: the multiplication turned it into a number.- Comparisons don’t convert text the same way. The text “90” passed
>=80, but only because text counts as greater than any number. See IF with multiple conditions.
When you need AND or OR as written: MAP
MAP with LAMBDA runs a formula once per row, so ordinary AND and OR work again:
=MAP(B2:B11, C2:C11, LAMBDA(score, att, IF(AND(score>=80, att>=0.8), "Pass", "Fail")))
This returned the same ten results as the multiplied version. It’s longer, but easier to read when there are many conditions. MAP doesn’t need ARRAYFORMULA around it.