1

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))).

Google Sheets screenshot

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.

Improve this question

2 Answers 2

Reset to default
1

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)))

enter image description here

Functions used:

Improve this answer
3
  • 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.
    – marikamitsos
    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
  • 1
    Thank you for taking the time to accept as well as sharing the alternatives.
    – marikamitsos
    Mar 16, 2022 at 22:54
0

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)
Improve this answer
3
  • 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
  • give edit access please.
    – user290033
    Mar 16, 2022 at 6:02
  • If it's easiest to demonstrate within an actual sheet, you can create a copy of my Google Sheet, edit your copy, and change its permissions so that it's publicly-viewable, and then you can share its URL here. Thanks!
    – Ryan
    Mar 16, 2022 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.