How to Count the Number of Cells Greater than Zero in a Filtered List?

**denise001** · 09-01-2011, 11:00 AM

Hello Excel Forum.

Can anyone tell me the formula to put in Cell D1 that will state how many cells,when I apply my filters, are greater than zero?

Attached example enclosed - with the desired filters applied already.

The count in the filtered list is 10. But I want the count only where the value in the cell is greater than zero. In this example, the correct answer is 9, because there is a cell that is zero and I want to ignore the zero cells.

Have tried every possible combination of functions (sumproduct/countif) but this is really beating me hands down.

Many thanks
denise
London

**NBVC** · 09-01-2011, 11:20 AM

Try:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($D$3:$D$50,ROW($D$3:$D$50)-MIN(ROW($D$3:$D$50)),,1)),--($D$3:$D$50>0))

**Marcol** · 09-01-2011, 11:22 AM

Assuming you are using 2007 or later this might do what you need witout filtering.

Select from the drop-downs in B1 & C1

In D1

=COUNTIFS(B3:B51,B1,C3:C51,C1,D3:D51,">0")

**denise001** · 09-01-2011, 11:27 AM

Perfect NBVC! Works perfectly. Thank you very much.

With a formula that long, I would have never got it - but I am pleased to say I did get close, as one of my attempts did include MIN(ROW). So thank you for your help. Appreciate your time.

Quite a tricky one I thought!

Thread set to SOLVED.

Warm regards
denise

**denise001** · 09-01-2011, 11:33 AM

Hello Marcol

Thank you so much for your solution. Appreciate your time on my behalf. Like your solution. I have saved this and NBVC solution. I will be using NBVC solution

this time around.

Quite a tricky problem initially I thought!
best regards
denise