COUNTIF criteria with sum of row

Question

I am attempting to set up a formula that will allow me to count the number of items in column A which have a corresponding SUM(Bn:En)>0 (where n is the row for that item). Conceptually (for the exampled data shown below) I would like to count the number of colors that make up my articles of clothing; the formula for this example data would return 5, since Green would not satisfy the criteria.

I have previously accomplished something like this by adding a separate column at the end of the data which checked if SUM > 0, then counted from there. However, I am wondering if there is a more straightforward way to accomplish this and have personally failed to get a formula to work here. The only thing I can think of to try is =COUNTIF(A2:A7,"SUM(B2:E2)>0"), or other varitaions on it, none of which have worked. I have also tried out using COUNTIFS (e.g. =COUNTIFS(B2:B7,">0",C2:C7,">0",...)), but this results in criteria working more like an AND statement, rather than an OR which is essentially what I'm trying to do here.

Answer 1

You will need to enter this formula with 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's important to first understand how the FREQUENCY function works.

---

Example 1

See the table below to see some of the similarities FREQUENCY and COUNTIF share when passing a range of values into both of the function's parameters:

The array returned by the COUNTIF function should be no surprise. For each 1 that appears in the table, the number 11 (The total count of 1s) appears in it's position in the array. Note also that 0s appear in the array in place of all the blanks.

The array returned by the FREQUENCY function is a little more tricky. The number 11 (Once again, the total count of 1s) appears ONLY in the position of the array where the first number 1 lies. FREQUENCY avoids all blank values and the 0s found here in the array actually represent all other instances of the number one. See the table below for a more clear picture of what is happening 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

Lets take a look at one more 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 is different from the one returned by COUNTIF. Once again refer to the table below to see what is happening inside FREQUENCY:

Often times, the FREQUENCY function is used to find the number of unique values inside a data set. In order to do this here, you can just 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).

---

The Original Problem

We can now use what we have learned to attack the original problem. In order to count the number of colors that make up the articles of clothing, we are going to need to assign each row a unique number where there is a number greater than zero. We can do this with the row function.

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

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

Next, we are going to put this formula into both parameters of the FREQUENCY functon:

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

See table below:

Finally, we can wrap the array with the SUM and IF function to get the answer of 5:

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

Answer 2

This will work Don't forget CTRL+SHIFT+ENTER B1:E1 - your product name range B2:E2 =- first row data B7:E7 - last row data

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

Answer 3

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