< Go Back

Count visible rows only with criteria

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}

Finally, this is what we get:

=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

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar