# How Google Sheets ArrayFormula consider other column

In Row 1 of this sheet I have formulas like `=ArrayFormula(sum(if(and(not(ISBLANK(C\$4:C\$49)),\$A\$4:\$A\$49>=\$A\$1),1,0)))`. What am I doing wrong?

How can I change the formula such that the values in Row 1 from `C` to `F` show the counts of non-empty cells in the main part of the table but only when the Importance value of that row is `>=3`?

P.S. If it's easier to demonstrate within an actual sheet, you can create a copy of my Google Sheet, edit your copy, change its permissions so that it's publicly-viewable and then share its URL here.

Please try either of the following (simpler) formulas

``````=COUNTIFS(\$A\$4:\$A\$49,">="&\$A\$1,C4:C49,"<>")
``````

OR (if you really want an array)

``````=SUM(INDEX(IF((\$A\$4:\$A\$49>=\$A\$1)*(C4:C49<>""),1)))
`````` Functions used:

• Please remember that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. Mar 16, 2022 at 14:25
• Thank you!! Wow, this is so cool. And someone else also privately sent me 2 other solutions that work. It's so interesting how 4+ approaches work, and I hadn't been able to come up with even one of them. `=ArrayFormula(MMULT(SEQUENCE(1,ROWS(A4:A),1,0),IF(C4:F<>"",1,0)*(A4:A>=A1)))` and `=SUM(FILTER(IF(C\$4:C<>"",1,0),\$A\$4:\$A>=\$A\$1))`
– Ryan
Mar 16, 2022 at 21:19
• Thank you for taking the time to accept as well as sharing the alternatives. Mar 16, 2022 at 22:54

You can try the following in C1 and drag it to F1:

``````=sumif(\$A4:\$A,">="&A1,C4:C)
``````

Or with a single formula in C1:

``````=index(query((A4:A>=A1)*(C4:F),"select sum(Col1), sum(Col2), sum(Col3), sum(Col4)"),2)
``````
• I appreciate your effort, but these don't work. The first one doesn't consider whether cells are empty. So its resulting values are wrong. And the second approach causes a #REF error as shown here: docs.google.com/spreadsheets/d/…
– Ryan
Mar 15, 2022 at 22:26