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.

2 Answers 2

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:

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. Mar 16 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 at 21:19
  • 1
    Thank you for taking the time to accept as well as sharing the alternatives. Mar 16 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)
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 at 22:26
  • give edit access please.
    – ztiaa
    Mar 16 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 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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