Forum Discussion

RLCornish00's avatar
RLCornish00
Copper Contributor
May 29, 2020
Solved

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 tha...
Excel Desktop
Need Help
  • SergeiBaklan's avatar
    SergeiBaklan
    May 29, 2020

    RLCornish00 

    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)

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 29, 2020

RLCornish00 

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)

RLCornish00's avatar
RLCornish00
Copper Contributor
Jun 02, 2020

SergeiBaklan, 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.

Resources