count filtered rows based on condition greater than forty

G

Guest

i need to count the number of filtered rows greater than 40 for a particular
column of data. i know we hav subtotal function, but donno how to giv a
condion?
 
T

T. Valko

Try this:

Row 1 are column headers with filter
Data in the range A2:C10
You want to count based on column C

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C2:C10,ROW(C2:C10)-MIN(ROW(C2:C10)),,1)),--(C2:C10>40))

Assume column C contains *only* numeric values, no text entries.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count the number of rows with entries greater than zero 1
need help on either doing a loop and counting specific values orusing subtotal function 2
count number of columns based on values across a row 1
Counting the number of cells meeting conditional formating criteria 4
COUNTIF for 2 columns 6
SUBTOTAL COUNTIF formula in Excel. 2
subtotal function won't work 1
NEED FORMULA TO POINT TO TOP OF COLUMN IF NUMBER GREATER THAN ANOT 9

Top