Search code examples
excelfilterexcel-formulacriteria

excel, how to combine subtotal function with numeric criteria on filtered results

enter image description here

I would like to use a subtotal function with numeric criteria for filtered data. As you can see in the image i have a dashboard that will calculate the filtered results based on static criteria.

the criteria is >1,>2,>3... and so forth.

So when i filter the data, i would like to see how many visible rows meet each criteria listed within the dashboard.

Anyone know how to do this? Any help is much appreciated.

Thanks, Furla

Solution

  • Use this in E2 (per the supplied image),

    =SUMPRODUCT(--(E$11:E$21>1)*SUBTOTAL(102, OFFSET(E$11:E$21, ROW($1:$11)-1, 0, 1, 1)))

    enter image description here

    Functions like SUBTOTAL (or SUM, AVERAGE, etc) use their range all-at-once so cyclic processing is not available. You need to cycle through each cell in the range, hence the range for SUBTOTAL is redefined with OFFSET.

    You might be better putting 1, 2, 3, 4, 10 in D2:D6 and using a custom number format of \>0\% (or convert to true percentages).