trying to count a range with criterion in different columns

  • Thread starter Thread starter skweetis
  • Start date Start date
S

skweetis

I'm trying to count the number of fields in a column if another colum
is between a range.

for example

I want to count cells in column J if data in column A falls between
and z. the count must fall between the amount in the row.

I've done a similar SUMPRODUCT for adding the data in column J, but no
I want to count those same cells for a calculation I'm doing. I'
stumped...please advis
 
skweetis,
=SUMPRODUCT((A1:A25>5)*(A1:A25<30))
count the instances where columnA value is >5 and <30

=SUMPRODUCT((A1:A25>5)*(A1:A25<30)*(LEN(J1:J25)>0))
counts above if there is something in columnJ too.

=SUMPRODUCT((A1:A25>5)*(A1:A25<30)*(ISNUMBER(J1:J25)))
counts above if there is a number in columnJ.

HTH
Cecil
 
Cecilkumara,

Your formula is working, but, the data I am trying to count is times.
There is always data in every row of at least 0:00:00. If i take ou
the data and leave the cell blank, it works the way I want it.
However, since it takes too much time to go thru and delete the 0:00:0
to leave it blank.

Any sugestions
 
=SUMPRODUCT(--(Arange>=X),--(Arange<=Z),--(Jrange<>""),--(Jrange<>"0:00"+0))

Arange means your range in column A, Jrange your range in column J. X and Z
stand for cells housing the conditions you mentioned in your earlier post.
 
Back
Top