SUMPRODUCT to count BETWEEN numbers and only VISIBLE cells

[Gilberto Fernandez]

Hello,

Im currently using this formula to count cells that match the contents of another cell. It works perfectly only counting visible cells.

=SUMPRODUCT(--($A$12:$A$50000=A50003),(SUBTOTAL(103,OFFSET($A$12,ROW($A$12:$A$50000)-MIN(ROW($A$12:$A$50000)),,))))

I need another formula to count cells with the condition that its value lies between the values of two reference cells. I tried to modify the above formula but its not working.

=SUMPRODUCT(--($A$12:$A$50000>G50003),($A$12:$A$50000<=H50003),(SUBTOTAL(103,OFFSET($A$12,ROW($A$12:$A$50000)-MIN(ROW($A$12:$A$50000)),,))))

Any ideas?
Thanks,
Gilberto

 

[Domenic]

Try...

=SUMPRODUCT(--($A$12:$A$50000>G50003),--($A$12:$A$50000<=H50003),SUBTOTAL(103,OFFSET($A$12:$A$50000,ROW($A$12:$A$50000)-ROW($A$12),0,1)))

 

[Gilberto Fernandez]

That was a quick reply thank you very much.

The formula returns the TOTAL count of all the visible cells (its NOT counting ONLY the the cells which value is BETWEEN the two reference cells).

 

[Gilberto Fernandez]

SORRY!!! i mistyped your formula.

IT WORKS PERFECTLY, thanks.