COUNTIF Criteria with Sum of Rows

Question

I am trying to set up a formula that will allow me to count the number of elements in column A that have the corresponding SUM(Bn:En)>0 (where n is the row for this element). Conceptually (for the data below) I would like to count the number of colors that make up my garments; the formula for this sample data will return 5 since Green will not meet the criteria.

Earlier, I did something like this, adding a separate column at the end of the data, which checked if SUM> 0, and then it is counted from there. However, I am wondering if there is an easier way to accomplish this and personally failed to get the formula for working here. The only thing I can think of is =COUNTIF(A2:A7,"SUM(B2:E2)>0") or other variables on it, none of which worked. I also tried using COUNTIFS (e.g. =COUNTIFS(B2:B7,">0",C2:C7,">0",...) ), but this leads to the criteria working more like an AND operator, and not OR , which is essentially what I'm trying to do here.

Answer 1

You will need to enter this formula using CTRL + SHIFT + ENTER :

 =SUM(IF(FREQUENCY(IF(B2:E7>0,ROW(B2:B7)),IF(B2:E7>0,ROW(B2:B7)))>0,1)) 

To understand how this formula works, it is important to first understand how the FREQUENCY function works.

---

Example 1

See the table below to see some common features of FREQUENCY and COUNTIF, passing a range of values ​​to both parameters of the function:

The array returned by the COUNTIF function should not be surprising. For each 1 that appears in the table, the number 11 appears in its position in the array (the total number of units is 1). Note also that 0s appear in the array instead of all spaces.

The array returned by the FREQUENCY function is a bit more complicated. The number 11 (again, the total number 1s) appears ONLY at the position of the array, where the first number is 1. FREQUENCY avoids all empty values, and 0 found here in the array actually represents all the other instances of number one. See the table below for a clearer picture of what is going on inside FREQUENCY:

Where does the last 0 (pink) come from? The FREQUENCY function automatically adds an extra zero at the end of the array.

---

Example 2

Let's look at another example. Suppose our original table had not only 1s, but also 2s. See the table below:

You can see how the array returned by FREQUENCY differs from the array returned by the COUNTIF function. Refer to the table below again to find out what is going on inside FREQUENCY:

Often, the FREQUENCY function is used to find the number of unique values ​​within a dataset. To do this here, you can simply wrap the array with this SUM and IF function:

 =SUM(IF({8;0;0;3;0;0;0;0;0;0;0;0}>0,1)) 

This formula returns 2 (2 unique values).

---

Original problem

Now we can use what we learned to attack the original problem. To calculate the number of colors that make up clothing, we will need to assign each line a unique number, where the number is greater than zero. We can do this with the row function.

 =IF(B2:E7,ROW(B2:B7)) 

See below to see how this formula "affects our table":

Then we are going to put this formula in both parameters of the FREQUENCY function:

 =FREQUENCY(IF(B2:E7>0,ROW(B2:B7)),IF(B2:E7>0,ROW(B2:B7))) 

See table below:

Finally, we can wrap the array using the SUM and IF functions to get the answer from 5:

 =SUM(IF({3;0;0;2;0;3;0;0;1;2;0;0}>0,1)) 

Answer 2

This will work. Do not forget CTRL + SHIFT + ENTER B1: E1 - range of names of your products B2: E2 = - data of the first line B7: E7 - data of the last line

 =SUMPRODUCT(IF(MMULT(IF(B2:E2:B7:E7 > 0,1,0),ROW(INDIRECT("1:"&COUNTA(B1:E1)))) > 0,1,0)) 

Answer 3

Would it be something like =MAX(COUNTIF(B2:B7,">0"),COUNTIF(C2:C7,">0"),COUNTIF(D2:D7,">0"),COUNTIF(E2:E7,">0")) do? Or I do not understand? This counts the number of times a value appears in each column and finds the highest COUNT.