How do I count the amount of non-blank cells in a row, including only cells that are in a column that contains a specific value?

Question

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

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.

Answer 1

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

Answer 2

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

---

Test sheet