0

Here is my example sheet: https://docs.google.com/spreadsheets/d/1xxmTnRTaA0DPAyibbiXK00YPqhwT5NXH8kf8PtBh0jg/edit?usp=sharing

enter image description here

I am trying to create a formula for the column "Total" that would contain the total amount of non-blank cells for that row, counting only cells that are in a column that contains the value "B". (0 counts as non-blank)

Finding the sum of these columns is actually pretty easy, using this formula (for row 2 as an example): =SUMIF($1:$1,"B",2:2)

For counting non-blank cells however, I couldn't find a similarly simple way to do it. I tried approaching this by using FILTER and COUNTIF, but I couldn't figure it out.

0

This formula can do the job :

=count(ArrayFormula(IF($C$1:$J$1="B",C2:J2,)))
  1. IF + arrayformula to catch cells within B columns
  2. Count() to count numeric values

enter image description here


Test sheet

0

To include zeros you could use this simple formula and drag it down to as many rows as you like.

=COUNTIFS(C2:2,"<>",$C$1:$1,"=B")

On the other hand, if you want to exclude zeros try:

=COUNTIFS(C2:2,">0",$C$1:$1,"=B")

Function used: COUNTIFS

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.