SOLVED

Highlighted
New Contributor

# Excel 365 - Count visible rows with multiple criteria

Hello,

I have racked my brain trying to find the solution to this but no dice so I'm coming to you! Here are the details:

* Count how many WIDGETS (simple enough)

* Two potential names that are similar; 'WIDGETS' and 'WIDGETS - WITH CHEESE'

* But only for the rows displayed, not those rows that are filtered out or hidden

I found this formula (see below) and it was working until I realized 'WIDGETS' & WIDGETS - WITH CHEESE' should be counted together because at the end of the day both products fall under the same parent category of 'WIDGETS'.

{=SUMPRODUCT((G8:G5000='WIDGETS')*(SUBTOTAL(103,OFFSET(G8,ROW(G8:G5000)-MIN(ROW(G8:G5000)),0))))}

I have tried a number of options from OR to COUNTIFS to SUM but nothing that I'm coming up with is working, and I would greatly appreciate any help you can offer.

While we're talking about this formula, specifically, can I not use Table references in an array formula, or even a Named Range? I was trying to replace 'G8:G5000' with Named Range 'PROD', where I sent the range as G8:G5000. Only taking this route because I've seen where array formulas have limits in terms of references to whole columns.

** If there is a better / simpler / different formula you would recommend I am completely open to that too! **

2 Replies
Highlighted
Solution

# Re: Excel 365 - Count visible rows with multiple criteria

Here we need to implement OR condition to calculate both options. For such sample

we may use

``=SUMPRODUCT( ( (G8:G5000="WIDGETS")+(G8:G5000="WIDGETS - WITH CHEESE"))*(SUBTOTAL(103,OFFSET(G8,ROW(G8:G5000)-MIN(ROW(G8:G5000)),0))))``

filtered result will be

Another variant could be with helper column C, where we add the formula

``=AGGREGATE(3,5,F8)``

which returns zero for hided rows and 1 for visible. Formula to sum will be

``=SUMPRODUCT(( (G8:G5000="WIDGETS")+(G8:G5000="WIDGETS - WITH CHEESE"))*H8:H5000)``

Above is regular one (non-array)

Highlighted

# Re: Excel 365 - Count visible rows with multiple criteria

@Sergei Baklan, this is fantastic! I tried and tried to figure out the OR option and after exhausting my brain figured there was someone smarter that might be willing to help, and here you did! Thank you, I'm most appreciative.