If you want to get a count of visible rows only with criteria, you just need to apply a formula that is a little bit complex including three functions which are SUMPRODUCT/SUBTOTAL/OFFSET

**General Formula**

**=****SUMPRODUCT****((****rng=crta****)*(****SUBTOTAL****(3,****OFFSET****(****rng,rows,0,1****))))**

**…..where “crta” is the criteria and “rng” is the range.**

**Application**

The application of this formula in count visible rows only with criteria is quite simple. Imagine you are managing an order for T-shirt supply and you hidden row! Check out this example.

*Figure 1. Example 1 of **SUMPRODUCT ** Function*

From the example, the formula in D14 is

**=****SUMPRODUCT****((****C6:C9=D12****)*(****SUBTOTAL****(103,****OFFSET****(****C6,ROW,C6:C9****)-****MIN****(****ROW****(****C6:C9****)),0)))**

**…..**note that “**103**” has been used in this formula and not **“3” **since the input is done manually

**Here’s How the formula Works**

First, it is important to understand that what we need is the calculation inside the SUMPRODUCT. From the left, the first array checks criteria while the 2nd one deals with the ‘visibility’ of the rows. Here’s the simplified form:

**=SUMPRODUCT(****crta*visibility****)****……where “crta” is the criteria**

Where the criteria in the formula is ** =(C6:C9=D12) **and generate.

`{FALSE;TRUE;FALSE;TRUE}`

TRUE means “meet criteria. That is converted to:

** {0;1;0;1}**.

Now, SUBTOTAL will handle the visibility. The function can exclude hidden row inside **SUMPRODUCT. ****But** there is one a problem. The function can only return a single number. That’s why we need OFFSET so that it can feed SUBTOTAL with one reference for every row.

But to do that, you must give OFFSET an array with one number for every row, starting from zero. This is how we do it:

`= ROW(C6:C9) –MIN(ROW(C6:C9)`

The generated array will look like this {0;1;2;3}. Therefore, the second generated array (handing visibility using SUBTOTAL) is::

`=`

** SUBTOTAL****(3,****OFFSET****(****C6,ROW,C6:C9****)-****MIN****(****ROW****(C6:C9)),0)))**

`=SUBTOTAL(3,OFFSET(C6, {0;1;2;3},0)))`

**=**** SUBTOTAL****(3, {“Blue”; “Red”;”Green”;”Red”})**

**={1;0;1;1}**

**Fi****nally, this is what we ge**t:

**=SUMPRODUCT**({0,1,0,1,}*{1;0;1;1})

**Which return**** 1****.**

**How to count Multiple Criteria**

The formula can be extended if you would like to solve multiple criteria problem. This is how you extend the formula::

**=SUMPRODUCT****((****range1=crta1****)*(****rng2=crta2****)*(****SUBTOTAL****(3,****OFFSET****(****range,rows,0,1****))))**

…..where “crta” is criteria

## Leave a Comment